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

Setting Trigger Firing Order

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>

Specifying Trigger Firing Order

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>