June 1, 2009 at 2:07 pm
I've recently noticed an issue with getting my triggers to fire on some SQL Server 2005 installations. They fire whenever a true insert, update, or delete occurs, but if I run something like:
UPDATE myTable SET Id=Id
the trigger does not fire. However, it always has fired on most databases I've installed it on in the past. I'm just wondering if there is an optimization setting that I'm unaware of that determines that none of the data is changing, and therefore doesn't fire the trigger. Any help would be appreciated.
June 1, 2009 at 2:39 pm
Is there code in the trigger to check that a row has actually changed?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 2, 2009 at 2:21 am
Sample Trigger i use
IF(UPDATE(Authorised))
BEGIN
UPDATE
tbl
SET
IsAuthorised = (CASE WHEN inst.Authorised = 'Yes' THEN 1 ELSE 0 END)
FROM
TABLE tbl
INNER JOIN INSERTED inst ON tbl.TaskNumber = inst.TaskNumber
END
June 2, 2009 at 3:38 am
sql_lock (6/2/2009)
IF(UPDATE(Authorised))
That trigger is not going to do anything for the update you posted. It will only execute if the Authorised column is updated, that's what the IF UPDATED line is there for.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply