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.