Skip to content

Latest commit

 

History

History
90 lines (73 loc) · 3.84 KB

update-trigger-functions-transact-sql.md

File metadata and controls

90 lines (73 loc) · 3.84 KB
title description author ms.author ms.date ms.service ms.subservice ms.topic f1_keywords helpviewer_keywords dev_langs
UPDATE() (Transact-SQL)
UPDATE - Trigger Functions (Transact-SQL)
MikeRayMSFT
mikeray
03/15/2017
sql
t-sql
reference
UPDATE()_TSQL
UPDATE()
INSERT statement [SQL Server], UPDATE function
testing column updates
UPDATE function
UPDATE() function
detecting changes
columns [SQL Server], change detection
UPDATE statement [SQL Server], UPDATE function
verifying column updates
checking column updates
TSQL

UPDATE - Trigger Functions (Transact-SQL)

[!INCLUDE SQL Server Azure SQL Database Azure SQL Managed Instance]

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 [!INCLUDEtsql] INSERT or UPDATE trigger to test whether the trigger should execute certain actions.

:::image type="icon" source="../../includes/media/topic-link-icon.svg" border="false"::: Transact-SQL syntax conventions

Syntax

UPDATE ( column )

Arguments

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 [!INCLUDEssNoVersion]. However, computed columns cannot be used in this context.

Return Types

Boolean

Remarks

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.

Note

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 [!INCLUDEtsql] trigger.

If a trigger applies to a column, the UPDATED value will return as true or 1, even if the column value remains unchanged. This is by-design, and the trigger should implement business logic that determines if the insert/update/delete operation is permissible or not.

Examples

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

See Also

COLUMNS_UPDATED (Transact-SQL)
CREATE TRIGGER (Transact-SQL)