Chapter 8. Triggers

for RuBoard

I'd rather be a "Has Been" than a "Never Was" any day of the week.

H. W. Kenton

A trigger is a special type of stored procedure that executes when a specified DML operation (an INSERT, DELETE, or UPDATE, or any combination thereof) occurs. They're typically used to ensure that business rules are followed or to do other work when a data modification occurs. A trigger is the SQL equivalent of a 3GL callback function or a hooked interrupt vector.

You construct and attach triggers to tables using the CREATE TRIGGER command. When a table is dropped, so are its triggers.

Most of the details of stored procedure programming apply equally well to triggers. In fact, because you can call a stored procedure from a trigger, you can effectively do anything in a trigger that a stored procedure can do. One thing that triggers don't normally do is return result sets. Most front ends have no way of handling trigger-generated result sets, so you don't see this done very often. SQL Server doesn't permit triggers to return result codes.

Triggers fire once per statement, not per row, regardless of the number of rows changed by a given DML statement. You can set up triggers that fire after a DML operation (known as AFTER triggers, the default and most common type), and you can set up triggers that fire instead of a DML operation (INSTEAD OF triggers). We'll discuss each of these separately.

You can set up as many AFTER triggers as you want for a given table. Using sp_settriggerorder, you can designate a first and a last AFTER trigger on each table; the rest fire in no particular order. You're allowed just one INSTEAD OF trigger per DML operation on each table or view, although you can easily circumvent this by creating multiple views over the table or view in question and attaching INSTEAD OF triggers to each of them.

Declarative referential integrity (DRI) constraints have precedence over triggers. This means that a violation of a DRI constraint by a DML command will prevent triggers from executing.

for RuBoard