define trigger execution order

sp_settriggerorder use for defining the After trigger execution order. You can specify only the first trigger to be executed and the last trigger to be executed.

the syntax of the command is

sp_settriggerorder [ @triggername = ] '[ triggerschema. ] triggername'
       , [ @order = ] 'value(First or Last)'
       , [ @stmttype = ] 'statement_type ( Insert or update or delete)'
       [ , [ @namespace = ] { 'DATABASE' | 'SERVER' | NULL } ]

Note that after executing the sp_settriggerorder if you edit your trigger you will notice the call to the sp_settriggerorder
store procedure at the bottom of the trigger. This is because the trigger execution sequence can change any time a trigger is Alter  
For http://msdn2.microsoft.com/en-us/library/ms186762.aspx

Note

If an ALTER TRIGGER statement changes a first or last trigger, the First or Last attribute originally set on the trigger is dropped, and the value is replaced by None. The order value must be reset by using sp_settriggerorder.

If the same trigger must be designated as the first or last order for more than one statement type, sp_settriggerorder must be executed for each statement type. Also, the trigger must be first defined for a statement type before it can be designated as the First or Last trigger to fire for that statement type.