How to Fire a Succession of SQL Triggers - dummies

How to Fire a Succession of SQL Triggers

By Allen G. Taylor

You can probably see a complication in the way SQL triggers operate. Suppose you create a trigger that causes an SQL statement to be executed on a table upon the execution of some preceding SQL statement. What if that triggered statement itself causes a second trigger to fire?

That second trigger causes a third SQL statement to be executed on a second table, which may itself cause yet another trigger to fire, affecting yet another table. How is it possible to keep everything straight? SQL handles this machine-gun-style trigger firing with something called trigger execution contexts.

A succession of INSERT, DELETE, and UPDATE operations can be performed by nesting the contexts in which they occur. When a trigger fires, an execution context is created. Only one execution context can be active at a time. Within that context, an SQL statement may be executed that fires a second trigger.

At that point, the existing execution context is suspended in an operation analogous to pushing a value onto a stack. A new execution context, corresponding to the second trigger, is created, and its operation is performed.

There is no arbitrary limit to the depth of nesting possible. When an operation is complete, its execution context is destroyed, and the next higher execution context is “popped off the stack” and reactivated. This process continues until all actions are complete and all execution contexts have been destroyed.