UPDATE - 觸發程序函式 (Transact-SQL)UPDATE - Trigger Functions (Transact-SQL)

適用於: 是SQL Server 是Azure SQL Database 否Azure Synapse Analytics (SQL DW) 否平行處理資料倉儲 APPLIES TO: yesSQL Server yesAzure SQL Database noAzure Synapse Analytics (SQL DW) 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

布林Boolean

RemarksRemarks

不論 INSERT 或 UPDATE 嘗試成功與否,UPDATE() 都會傳回 TRUE。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.

IF UPDATE 會在 INSERT 動作中傳回 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)