INF: Behavior of Cascading Triggers with Recursive Calls

ID: Q89655

The information in this article applies to:

Microsoft SQL Server for OS/2, version 4.2

SUMMARY

In the Microsoft SQL Server for OS/2 version 4.2 "Language Reference" manual under CREATE TRIGGER, two statements refer to recursive behavior of cascading triggers. These statements are not complete in their description of cascading triggers with recursive calls and can be misinterpreted. The purpose of this article defines expected behavior of cascading triggers and the possible recursive elements.

MORE INFORMATION

There are two statements that appear on page 90 of the "Language Reference" manual for SQL Server that describe behavior of cascading triggers and possible recursion. The two statements are as follows:

A trigger does not call itself in response to a second update to the same table within the trigger. For example, if an update trigger on one column of a table results in an update to another column, the update trigger activates only once.

A trigger activates only once per query. Query, in this case, means a single data modification statement. A complex query containing a WHILE loop can repeat an UPDATE or INSERT many times, an the trigger activates each time.

First, a trigger cannot cause itself to be fired. However, if a table has separate triggers for UPDATE, INSERT, and/or DELETE, one of these triggers can cause one of the remaining two to be activated.

If it is desired that a trigger cannot fire any other trigger on the same table, the triggers should be combined into one single trigger, or the database option for cascading triggers should be turned off.

Statement number two says that a trigger activates only once per single data modification statement. This is correct, but it is important to understand that any INSERT, UPDATE, or DELETE statement fired by a trigger is its own single data modification statement.

With this in mind, it is possible to establish a recursive loop between two or more triggers where the triggers can be on one or more tables. If an uninterrupted recursive loop is encountered, all statements will be rolled back after the maximum depth of 16 cascading triggers is reached.

To clarify how a recursive trigger can occur, use the following example:

Table A has an INSERT trigger that issues an UPDATE statement to the row just inserted.

Table A has an UPDATE trigger that inserts another row into table A.

If a row is INSERTED into table A, the INSERT trigger would fire the UPDATE trigger by issuing an UPDATE statement to table A. The UPDATE trigger then issues an INSERT statement into table A that causes the UPDATE trigger to fire. If this recursive loop is left uninterrupted, it will eventually reach the maximum depth of 16 and would roll back.

With understanding of the above information and careful design of the database, cascading triggers can be a valuable piece of the database design.

Additional query words: 4.20 Transact-SQL Trigger

Keywords : kbusage
Version : 4.2
Platform : OS/2
Issue type :