Update function in TSQL trigger

Question

I have a question about TSQL function Update. For example, I have a table with a field Name. If I check if the field Name is changed or not in a After Update trigger likes this:

  if Update(Name)   Begin     -- process   End 

Will the Update still return TRUE even if Name is not changed? The following update statement will update it with the same value:

  SELECT @v_Name = Name From MyTable Where Id = 1;   Update MyTable Set Name = @v_Name where Id = 1; 

If the Update() returns TRUE even the value of Name is not changed, do I have to compare the value in the inserted and deleted virtual tables to find out if the value is really changed?

By the way, the inserted and deleted are virtual tables and they may contain more than one rows of data if more than one rows of data are changed by one TSQL INSERT or UPDATE statement. In case of more than one records, are the count numbers of rows in inserted and deleted virtual tables the same and what is the real meaning of Update(Name) as TRUE? Does it mean that at least one is changed? Or does Update(Name) mean that the field of Name has been set by Update statement regardless if the value is changed?

The SQL server I use is Microsoft SQL 2005.

Answer 1

Triggers are tricky and you need to think in bulk when you're creating one. A trigger fires once for each UPDATE statement. If that UPDATE statement updates multiple rows, the trigger will still only fire once. The UPDATE() function returns true for a column when that column is included in the UPDATE statement. That function helps to improve the efficiency of triggers by allowing you to sidestep SQL logic when that column isn't even included in the update statement. It doesn't tell you if the value changed for a column in a given row.

Here's a sample table...

CREATE TABLE tblSample (     SampleID INT PRIMARY KEY,     SampleName VARCHAR(10),     SampleNameLastChangedDateTime DATETIME,     Parent_SampleID INT ) 

If the following SQL was used against this table:

UPDATE tblSample SET SampleName = 'hello' 

..and an AFTER INSERT, UPDATE trigger was in effect, this particular SQL statement would always evaluate the UPDATE function as follows...

IF UPDATE(SampleName) --aways evaluates to TRUE IF UPDATE(SampleID)  --aways evaluates to FALSE IF UPDATE(Parent_SampleID) --aways evaluates to FALSE 

Note that UPDATE(SampleName) would always be true for this SQL statement, regardless of what the SampleName values were before. It returns true because the UPDATE statement includes the column SampleName in the SET section of that clause and not based on what the values were before or afterward. The UPDATE() function will not determine if the values changed. If you want to do actions based on whether the values are changed you're going to need to use SQL and compare the inserted and deleted rows.

Here's an approach to keeping a last updated column in sync:

--/* IF OBJECT_ID('dbo.tgr_tblSample_InsertUpdate', 'TR') IS NOT NULL    DROP TRIGGER dbo.tgr_tblSample_InsertUpdate GO --*/  CREATE TRIGGER dbo.tgr_tblSample_InsertUpdate ON dbo.tblSample   AFTER INSERT, UPDATE  AS BEGIN --Trigger    IF UPDATE(SampleName)       BEGIN       UPDATE tblSample SET       SampleNameLastChangedDateTime = CURRENT_TIMESTAMP       WHERE         SampleID IN (SELECT Inserted.SampleID                 FROM Inserted LEFT JOIN Deleted ON Inserted.SampleID = Deleted.SampleID                WHERE COALESCE(Inserted.SampleName, '') <> COALESCE(Deleted.SampleName, ''))     END  END --Trigger 

The logic to determine if the row was updated is in the WHERE clause above. That's the real check you need to do. My logic is using COALESCE to handle NULL values and INSERTS.

... WHERE   SampleID IN (SELECT Inserted.SampleID                 FROM Inserted LEFT JOIN Deleted ON Inserted.SampleID = Deleted.SampleID                WHERE COALESCE(Inserted.SampleName, '') <> COALESCE(Deleted.SampleName, '')) 

Note that the IF UPDATE() check is used to help improve the efficiency of the trigger for when the SampleName column is NOT being updated. If a SQL statement updated the Parent_SampleID column for instance then that IF UPDATE(SampleName) check would help sidestep around the more complex logic in that IF statement when it doesn't need to run. Consider using UPDATE() when it's appropriate but not for the wrong reason.

Also realize that depending on your architecture, the UPDATE function may have no use to you. If your code architecture uses a middle-tier that always updates all columns in a row of a table with the values in the business object when the object is saved, the UPDATE() function in a trigger becomes useless. In that case, your code is likely always updating all the columns with every UPDATE statement issued from the middle-tier. That being the case, the UPDATE(columnname) function would always evaluate to true when your business objects are saved because all the column names are always included in the update statements. In that case, it would not be helpful to use UPDATE() in the trigger and would just be extra overhead in that trigger for a majority of the time.

Here's some SQL to play with the trigger above:

INSERT INTO tblSample (   SampleID,   SampleName ) SELECT 1, 'One' UNION SELECT 2, 'Two' UNION SELECT 3, 'Three'  GO SELECT SampleID, SampleName, SampleNameLastChangedDateTime FROM tblSample  /* SampleID  SampleName SampleNameLastChangedDateTime ----------- ---------- ----------------------------- 1       One    2010-10-27 14:52:42.567 2       Two    2010-10-27 14:52:42.567 3       Three  2010-10-27 14:52:42.567 */  GO  INSERT INTO tblSample (   SampleID,   SampleName ) SELECT 4, 'Foo' UNION SELECT 5, 'Five'  GO SELECT SampleID, SampleName, SampleNameLastChangedDateTime FROM tblSample /* SampleID  SampleName SampleNameLastChangedDateTime ----------- ---------- ----------------------------- 1       One    2010-10-27 14:52:42.567 2       Two    2010-10-27 14:52:42.567 3       Three  2010-10-27 14:52:42.567 4       Foo    2010-10-27 14:52:42.587 5       Five   2010-10-27 14:52:42.587 */  GO  UPDATE tblSample SET SampleName = 'Foo'   SELECT SampleID, SampleName, SampleNameLastChangedDateTime FROM tblSample  /* SampleID  SampleName SampleNameLastChangedDateTime ----------- ---------- ----------------------------- 1       Foo    2010-10-27 14:52:42.657 2       Foo    2010-10-27 14:52:42.657 3       Foo    2010-10-27 14:52:42.657 4       Foo    2010-10-27 14:52:42.587 5       Foo    2010-10-27 14:52:42.657 */ GO  UPDATE tblSample SET SampleName = 'Not Prime' WHERE SampleID IN (1,4)  SELECT SampleID, SampleName, SampleNameLastChangedDateTime FROM tblSample /* SampleID  SampleName SampleNameLastChangedDateTime ----------- ---------- ----------------------------- 1       Not Prime  2010-10-27 14:52:42.680 2       Foo        2010-10-27 14:52:42.657 3       Foo        2010-10-27 14:52:42.657 4       Not Prime  2010-10-27 14:52:42.680 5       Foo        2010-10-27 14:52:42.657 */  --Clean up... DROP TRIGGER dbo.tgr_tblSample_InsertUpdate DROP TABLE tblSample 

User GBN had suggested the following:

IF EXISTS (     SELECT         *     FROM         INSERTED I         JOIN         DELETED D ON I.key = D.key     WHERE         D.valuecol <> I.valuecol --watch for NULLs!     )    blah 

GBN's suggestion of using an IF (EXISTS( ...clause and putting the logic in that IF statement if rows exist that were changed could work. That approach will fire for ALL rows included in the trigger even if only some of the rows were actually changed (which may be appropriate for your solution, but also may not be appropriate if you only want to do something to rows where the values changed.) If you need to do something to rows where an actual change has occurred, you need different logic in your SQL that he provided.

In my examples above, when the UPDATE tblSample SET SampleName = 'Foo' statement is issued and the fourth row is already 'foo', using GBN's approach to update a "last changed datetime" column would also update the fourth row, which would not be appropriate in this case.