Applies To: SQL Server 2014, SQL Server 2016 Preview
Topic Status: Some information in this topic is preview and subject to change in future releases. Preview information describes new features or changes to existing features in Microsoft SQL Server 2016 Community Technology Preview 2 (CTP2).
Returns a Boolean value that indicates whether an INSERT or UPDATE attempt was made on a specified column of a table or view. UPDATE() is used anywhere inside the body of a Transact-SQL INSERT or UPDATE trigger to test whether the trigger should execute certain actions.
Applies to: SQL Server (SQL Server 2008 through current version), Azure SQL Database.
UPDATE() returns TRUE regardless of whether an INSERT or UPDATE attempt is successful.
To test for an INSERT or UPDATE action for more than one column, specify a separate UPDATE(column) clause following the first one. Multiple columns can also be tested for INSERT or UPDATE actions by using COLUMNS_UPDATED. This returns a bit pattern that indicates which columns were inserted or updated.
IF UPDATE returns the TRUE value in INSERT actions because the columns have either explicit values or implicit (NULL) values inserted.
The IF UPDATE(column) clause functions the same as an IF, IF...ELSE, or WHILE clause and can use the BEGIN...END block. For more information, see Control-of-Flow Language (Transact-SQL).
UPDATE(column) can be used anywhere inside the body of a Transact-SQL trigger.
The following example creates a trigger that prints a message to the client when anyone tries to update the StateProvinceID or PostalCode columns of the Address table.
USE AdventureWorks2012; GO IF EXISTS (SELECT name FROM sys.objects WHERE name = 'reminder' AND type = 'TR') DROP TRIGGER Person.reminder; GO CREATE TRIGGER reminder ON Person.Address AFTER UPDATE AS IF ( UPDATE (StateProvinceID) OR UPDATE (PostalCode) ) BEGIN RAISERROR (50009, 16, 10) END; GO -- Test the trigger. UPDATE Person.Address SET PostalCode = 99999 WHERE PostalCode = '12345'; GO