A trigger is a database object that is attached to a table. In many aspects it is similar to a stored procedure. As a matter of fact, triggers are often referred to as a "special kind of stored procedure." The main difference between a trigger and a stored procedure is that the former is attached to a table and is only fired when an INSERT, UPDATE or DELETE occurs. You specify the modification action(s) that fire the trigger when it is created.

The following shows how to create a trigger that displays the current system time when a row is inserted into the table to which it is attached:

SET NOCOUNT ON

CREATE TABLE Source (Sou_ID int IDENTITY, Sou_Desc varchar(10))
go
CREATE TRIGGER tr_Source_INSERT
ON Source
FOR INSERT
AS
PRINT GETDATE()
go
INSERT Source (Sou_Desc) VALUES ('Test 1')

-- Results --

Apr 28 2001  9:56AM

In some situations, you may want create more than one trigger on table. By default, all triggers execute at the same time and if there was a dependency between your triggers, you’ll get an exception. In this situation you have to execute triggers one by one and in an order.

In SQL Server 2005, Microsoft introduced a system stored procedure name “SP_SETTRIGGERORDER” that do whatever you want.

Note: In “SP_SETTRIGGERODER”, you can set the first and last trigger to execute; so if you have more than tree triggers in a table, you won’t be able to set all triggers’ orders.

The structure of “SP_SETTRIGGERODER” system stored procedure is like this:

USE AdventureWorks;
GO
sp_settriggerorder @triggername= 'Sales.uSalesOrderHeader',
 @order='First', @stmttype = 'UPDATE';

Note: You CANNOT use SP_SETTRIGGERODER for INSTEAD OF triggers.

Hope it helps.