views:

1689

answers:

3

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.

+3  A: 

Update() can be true, even if it's the same value. I would not rely on it personally and compare values.

Second, DELETED and INSERTED have the same number of rows.

The Update() function is not per row, but across all rows. Another reason not to use it.

More here in MSDN, however it's a but sparse, really.

After comment:

IF EXISTS (
    SELECT
        *
    FROM
        INSERTED I
        JOIN
        DELETED D ON I.key = D.key
    WHERE
        D.valuecol <> I.valuecol --watch for NULLs!
    )
   blah
gbn
thank you. Any other options available from SQL server to find out the change? Or I have to loop through each row to find out changes?
David.Chu.ca
Just join the 2 tables on the key, no loosp needed
gbn
That would have to be the `deleted` and `**inserted**` virtual tables, right? I've never heard of an "updated" virtual table
marc_s
@marc_s: doh! corrected...
gbn
A: 

The update trigger will fire on all update statements. the impacted rows are available within the trigger in the "inserted" and "deleted" tables. You can compare the old and new values by comparing the PK columns in the two tables (if you have a PK). The actual table remains unchanged till the trigger finishes execution.

nagul
+1  A: 

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, you're 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.

-Eric Isaacs, J Street Technology, Inc.