SQL Server/T-SQL Tutorial/Trigger/Trigger order

Setting Trigger Firing Order

   <source lang="sql">

7> CREATE TABLE dbo.MyTable 8> (TestID int NOT NULL) 9> GO 1> 2> CREATE TRIGGER dbo.trg_i_MyTable 3> ON dbo.MyTable 4> AFTER INSERT 5> AS 6> PRINT "I will be fired first." 7> GO 1> 2> CREATE TRIGGER dbo.trg_i_MyTable2 3> ON dbo.MyTable 4> AFTER INSERT 5> AS 6> PRINT "I will be fired last." 7> GO 1> 2> CREATE TRIGGER dbo.trg_i_MyTable3 3> ON dbo.MyTable 4> AFTER INSERT 5> AS 6> PRINT "3" 7> GO 1> 2> CREATE TRIGGER dbo.trg_i_MyTable3 3> ON dbo.MyTable 4> AFTER INSERT 5> AS 6> PRINT "I 3" 7> GO 1> 2> EXEC sp_settriggerorder "trg_i_MyTable", "First", "INSERT" 3> EXEC sp_settriggerorder "trg_i_MyTable2", "Last", "INSERT" 4> 5> INSERT dbo.MyTable 6> (TestID) 7> VALUES (1) 8> 9> drop table dbo.MyTable 10> GO (1 rows affected) I will be fired first. 3 I will be fired last. 1> 2></source>

Specifying Trigger Firing Order

   <source lang="sql">

4> 5> 6> CREATE TABLE test_trigger 7> (col1 int, 8> col2 char(6) ) 9> GO 1> INSERT INTO test_trigger VALUES (1, "First") 2> INSERT INTO test_trigger VALUES (2, "Second") 3> INSERT INTO test_trigger VALUES (3, "Third") 4> INSERT INTO test_trigger VALUES (4, "Fourth") 5> INSERT INTO test_trigger VALUES (5, "Fifth") 6> GO 1> 2> CREATE TRIGGER delete_test 3> ON test_trigger AFTER DELETE 4> AS 5> PRINT "You just deleted a row!" 6> GO 1> 2> 3> CREATE TRIGGER delete_update_test 4> ON test_trigger AFTER DELETE, UPDATE 5> AS 6> PRINT "You just deleted or updated a row!" 7> GO 1> EXEC sp_settriggerorder delete_update_test, first, "delete" 2> EXEC sp_settriggerorder delete_update_test, last, "update" 3> 4> DELETE test_trigger 5> WHERE col1 = 0 6> GO You just deleted or updated a row! You just deleted a row! 1></source>