Leah
4Penny.net
Points: 7116

11/12/2020 7:44:01 AM

SQL Triggers fire even when no rows are updated

It's true. They do. 

Try this:

CREATE TABLE TrigTest (RowID INT)
INSERT INTO TrigTest (RowID) VALUES (1)
 
 
 
 
IF EXISTS (SELECT name
       FROM   sysobjects
       WHERE  name = N't_TrigTest'
       AND    type = 'TR')
    DROP TRIGGER t_TrigTest
GO
 
CREATE TRIGGER t_TrigTest ON TrigTest FOR update
 
AS
 
BEGIN
    PRINT 'trigger executing'
end
 
UPDATE TrigTest SET rowid = 2 WHERE rowid = 3

When you run the update statement at the bottom of the script, the trigger will fire. 

Want to know an easy way to stop this behavior?

 

 

4Penny.net
Version: Unknown or N/A
Section: SQL Scripts
Table Definition Quick Links
All Tables
SOP Tables
RM Tables
GL Tables
POP Tables
HR Tables
PM Tables
UPR Tables
IV Tables
Olympic Tables
3