UPDATE - 触发器函数 (Transact-SQL)UPDATE - Trigger Functions (Transact-SQL)

适用于: 是SQL Server是Azure SQL 数据库否Azure SQL 数据仓库否并行数据仓库APPLIES TO: yesSQL Server yesAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

返回一个布尔值,指示是否尝试对表或视图的指定列执行 INSERT 或 UPDATE 操作。Returns a Boolean value that indicates whether an INSERT or UPDATE attempt was made on a specified column of a table or view. 可以在 Transact-SQLTransact-SQL INSERT 或 UPDATE 触发器主体中的任意位置使用 UPDATE(),以测试触发器是否应执行某些操作。UPDATE() is used anywhere inside the body of a Transact-SQLTransact-SQL INSERT or UPDATE trigger to test whether the trigger should execute certain actions.

主题链接图标 TRANSACT-SQL 语法约定Topic link icon Transact-SQL Syntax Conventions

语法Syntax

  
UPDATE ( column )   

参数Arguments

columncolumn
要为 INSERT 或 UPDATE 操作测试的列的名称。Is the name of the column to test for either an INSERT or UPDATE action. 由于表名是在触发器的 ON 子句中指定的,因此不要在列名前包含表名。Because the table name is specified in the ON clause of the trigger, do not include the table name before the column name. 列可以是 SQL ServerSQL Server 支持的任何数据类型The column can be of any data type supported by SQL ServerSQL Server. 但是,计算列不能用于此上下文。However, computed columns cannot be used in this context.

返回类型Return Types

BooleanBoolean

RemarksRemarks

UPDATE() 返回 TRUE,不考虑 INSERT 或 UPDATE 尝试是否成功。UPDATE() returns TRUE regardless of whether an INSERT or UPDATE attempt is successful.

若要测试对多个列执行的 INSERT 或 UPDATE 操作,请在第一个操作后指定单独的 UPDATE(column) 子句 。To test for an INSERT or UPDATE action for more than one column, specify a separate UPDATE(column) clause following the first one. 通过使用 COLUMNS_UPDATED,也可以为 INSERT 或 UPDATE 操作测试多个列。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.

在 INSERT 操作中,IF UPDATE 将返回 TRUE 值,因为这些列插入了显式值或隐式 (NULL) 值。IF UPDATE returns the TRUE value in INSERT actions because the columns have either explicit values or implicit (NULL) values inserted.

备注

IF UPDATE(column) 子句的功能等同于 IF、IF...ELSE 或 WHILE 子句,并且可以使用 BEGIN...END 语句块 。The IF UPDATE(column) clause functions the same as an IF, IF...ELSE, or WHILE clause and can use the BEGIN...END block. 有关详细信息,请参阅控制流语言 (Transact-SQL)For more information, see Control-of-Flow Language (Transact-SQL).

可以在 Transact-SQLTransact-SQL 触发器主体中的任意位置使用 UPDATE(column) 。UPDATE(column) can be used anywhere inside the body of a Transact-SQLTransact-SQL trigger.

如果将触发器应用于列,UPDATED 值将返回为 true1,即使列值保持不变也是如此。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

以下示例创建一个触发器;当有人尝试更新 StateProvinceID 表的 PostalCodeAddress 列时,该触发器将向客户端输出一条消息。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  

另请参阅See Also

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