Export (0) Print
Expand All
Expand Minimize

UPDATE() (Transact-SQL)

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.

Topic link icon Transact-SQL Syntax Conventions


UPDATE ( column ) 

column

Is the name of the column to test for either an INSERT or UPDATE action. Because the table name is specified in the ON clause of the trigger, do not include the table name before the column name. The column can be of any data type supported by SQL Server 2005. However, computed columns cannot be used in this context.

Boolean

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.

ms187326.note(en-US,SQL.90).gifNote:
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.

ms187326.note(en-US,SQL.90).gifNote:
In SQL Server 2000, UPDATE() does not detect changes to timestamp columns. For these columns, an IF UPDATE() clause inside a trigger body returns FALSE, regardless of whether the columns are updated. In SQL Server 2005, UPDATE() detects changes to timestamp columns. An IF UPDATE() clause inside a DML trigger body returns TRUE if these columns are updated.

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 AdventureWorks;
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

Community Additions

ADD
Show:
© 2014 Microsoft