SQL SERVER – Difference Between UPDATE and UPDATE() in Triggers

What is the difference between UPDATE and UPDATE()? The answer comes down to how UPDATE() in triggers works.

UPDATE is syntax used to update the database tables or database views.
USE AdventureWorks ;
GO
UPDATE Production.Product
SET ListPrice = ListPrice * 2;
GO

UPDATE() is used in triggers to check update/insert to the database tables or database views.
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.
USE AdventureWorks ;
GO
CREATE TRIGGER reminder
ON Person.Address
AFTER
UPDATE
AS
IF (
UPDATE (StateProvinceID)
OR
UPDATE (PostalCode) )
BEGIN
RAISERROR (50009, 16, 10)
END;
GO

Please read additional details on BOL – UPDATE, BOL – UPDATE()

Common Mistakes With UPDATE and UPDATE() in Triggers

I see one mix-up again and again in code reviews. A developer writes a trigger, uses UPDATE(), and believes it tells whether the value in the column changed. It does not. UPDATE() only tells you that the column was part of the statement. If someone runs a SET that writes the same value back into the column, UPDATE() still returns TRUE.

Here are a few points I keep in mind when I write a trigger:

• UPDATE() checks the column list of the statement, not the data. To find the rows where the value really changed, join the inserted and deleted tables on the key and compare the old and the new value.
• In an INSERT trigger, UPDATE() returns TRUE for every column, because each column gets a value, even if it is only a default or NULL.
• A trigger fires once per statement, not once per row. An UPDATE that touches zero rows still fires the trigger, so check @@ROWCOUNT at the very top, or look at the inserted table, before you do any work.
• When you need to test many columns at once, COLUMNS_UPDATED() returns a bit pattern of all the updated columns. It is harder to read, so I use it only when the list is long.

A simple way to remember it: UPDATE is the statement that changes rows, and UPDATE() is the question a trigger asks about that statement. Test every trigger with one row, many rows and zero rows before it goes to production. That small habit catches most trigger bugs early.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.