First and Last Triggers

If you deploy a large collection of AFTER triggers on a specific table, you can specify which of the triggers is the first AFTER trigger and which is last AFTER trigger. This essentially allows you to create a “list” of triggers with a start and an end, and all other AFTER triggers will fall in between.

You should clearly understand, however, that the feature allows you to define a first or last attribute on a trigger after each DML event that fires triggers. In other words, you can specify for TableX that trigger triggerA is first after the FOR INSERT event, that triggerB is first after the FOR UPDATE event, and that triggerC is last after the FOR DELETE event.

Here are some nuances to the application of the first/last attributes you should be aware of:

• The first AFTER trigger cannot be the last AFTER trigger as well.

• The triggers between first and last are not executed according to any order.

• The first and last triggers must be fired by DML statements (INSERT, UPDATE, and DELETE).

• INSTEAD OF triggers are not supported by the first/last feature.

• If you alter a first or last trigger, its status as first or last is dropped.

• Replicated tables will define a first trigger automatically for any table that is an immediate or queued update subscriber. In other words, the replication trigger will be positioned as the first trigger, regardless of any other trigger attributed as a first trigger, and if you try to reassign the first manually, after configuring for update subscription, SQL Server will generate an error.

• If you use an INSTEAD OF trigger on a table, it will fire before any updates on the base table fire the AFTER triggers.

• Also, if you define an INSTEAD OF trigger on a view, and that trigger updates a base table that has AFTER triggers defined, these triggers will fire before any manipulation on the base table takes place as a result of the INSTEAD OF trigger fired at the view level.

You use the sp_settriggerorder stored procedure to specify the first and last attributes for an AFTER trigger. The options that are available are as follows:

• First   This makes an AFTER trigger the first trigger fired after a fire event.

• Last   This makes an AFTER trigger the last trigger fired after a fire event.

• None   This cancels the first or last attribute on a trigger. Use None to reset the trigger to fire in any order.

The following example demonstrates the use of the trigger positioning stored proc:

 sp_settriggerorder @triggername = 'myTrigger', @order = 'first'