Triggers require no commit from the invoking transaction in order to fire; DML statements alone cause triggers to fire. COMMIT WORK is also disallowed in a trigger body.

In a procedure definition, you can use COMMIT and ROLLBACK statements. But in a trigger body, you cannot use COMMIT and ROLLBACK statements. You can use only the WHENEVER SQLERROR ABORT statement. Note that if autocommit is on, then each statement inside the trigger is not treated as a separate statement and is not committed when it is executed; instead, the entire trigger body is executed as part of the INSERT, UPDATE, or DELETE statement that fired the trigger. Either the entire trigger (and the statement that fired it) is committed, or else the entire trigger (and the statement that fired it) is rolled back.

If a DML statement updates/deletes a row that causes a trigger to be fired, you cannot update/delete the same row again within that trigger. In such cases an AFTER trigger event can cause a recursion error and a BEFORE trigger event can cause a concurrency conflict error.

The following sections explain these terms, provide some examples of triggers that create these problems, and provide a table (shown in Summary of trigger cases), that indicates the trigger situations that will and will not cause recursion errors or concurrency conflict errors.

A piece of code is “recursive” if the code causes itself to execute again. For example, a stored procedure that calls itself is recursive. Recursion in stored procedures is occasionally useful. On the other hand, triggers can create a slightly more subtle type of recursion, which is invalid and prohibited by the solidDB^® server. A trigger that contains a statement that causes the same trigger to execute again on the same record is recursive. For example, a delete trigger would be recursive if it tries to delete the same record whose deletion fired the trigger.

If the database server were to allow recursion in triggers, then the server might go into an “infinite loop” and never finish executing the statement that fired the trigger. A concurrency conflict error occurs when a trigger executes an operation that “competes with” the statement that fired the trigger by trying to do the same type of action (for example, delete) within the same SQL statement. For example, if you create a trigger that is supposed to be fired when a record is deleted, and if that trigger tries to delete the same record whose deletion fired the trigger, then there are in essence two different “simultaneous” delete statements “competing” to delete the record; this results in a concurrency conflict. The following section provides an example of a defective delete trigger.

Examples of Defective Triggers Causing Recursion

The examples in this section explain just a few of the many restrictions and rules involving triggers.

In this scenario, an employee has resigned from a job and his or her medical coverage requires cancellation. The medical coverage also requires cancellation for the employee’s dependents. A business rule for this situation is implemented by creating a trigger; the trigger is executed when an employee’s record is deleted and the statements inside the trigger then delete the employee’s dependents. (This example assumes that the employees and their dependents are stored in the same table; in the real world, dependents are normally kept in a separate table. This example also assumes that each family has a unique last name.)

Assume that an employee “John Smith” resigns and his medical coverage is deleted. When you delete “John Smith”, the trigger is invoked immediately after John Smith is deleted and the trigger will try to delete ALL people named “John Smith”, including not only the employee’s dependents, but also the employee himself, since his name meets the criteria in the WHERE clause.

Every time an attempt is made to delete the employee’s record, this action fires the trigger again. The code then recursively keeps trying to delete the employee by again firing the trigger, and again trying to delete. If the database server did not prohibit this or detect the situation, the server could go into an infinite loop. If the server detects this situation, it will give you an appropriate error, such as “Too many nested triggers.”

A similar situation can happen with UPDATE. Assume that a trigger adds sales tax every time that a record is updated. Here is an example that causes a recursion error:

In this scenario, customer Ann Jones calls up to change her order; the new price (with sales tax) is calculated by multiplying the new subtotal by 1.08. The record is updated with the new total price; each time the record is updated, the trigger is fired, so updating the record once, causes the trigger to update it again and updates are repeated in an infinite loop.

If AFTER triggers can cause recursion or looping, what happens with BEFORE triggers? The answer is that, in some cases, BEFORE triggers can cause concurrency problems. Let us return to the first example of the trigger that deleted medical coverage for employees and their dependents. If the trigger were a BEFORE trigger (rather than an AFTER trigger), then just before the employee is deleted, we would execute the trigger, which in this case deletes everyone named John Smith. After the trigger is executed, the engine resumes its original task of dropping employee John Smith himself, but the server finds either he is not there or that his record cannot be deleted because it has already been marked for deletion — in other words, there is a concurrency conflict because there are two separate efforts to delete the same record.

In addition to the examples described in the previous section, the following table summarizes a number of additional cases, including those involving INSERTs, as well as UPDATEs and DELETEs.

The table is divided into the following five columns:

For details on interpreting a trigger entry in this table, see Using events: Example 1.

Here is an example entry from the table and an explanation of that entry:

In this situation, we have a trigger that fires AFTER an INSERT operation is done. The body of the trigger contains statements that update the same row as was inserted (that is, the same row as the one that fired the trigger). If the lock type is “optimistic”, then the result will be that the record gets updated. (Because there is no conflict, the locking [optimistic versus pessimistic] does not make a difference).

Note that in this case there is no recursion issue, even though we update the same row that we just inserted. The action that “fires” the trigger is not the same as the action taken inside the trigger, and so we do not create a recursive/looping situation.

Here is another example from the table:

In this case, we try to insert a record, but before the insertion takes place the trigger is run. In this case, the trigger tries to update the record (for example, to add sales tax to it). Since the record is not yet inserted, however, the UPDATE command inside the trigger does not find the record, and never adds the sales tax. Thus the result is the same as if the trigger had never fired. There is no error message, so you may not realize immediately that your trigger does not do what you intended.

Flawed trigger logic occurs in the following example in which the same row is deleted in a BEFORE UPDATE trigger; this causes solidDB^® to generate a concurrency conflict error.

To avoid recursion and concurrency conflict errors, be sure to check the application logic and take precautions to ensure the application does not cause two transactions to update or delete the same row.

If a procedure returns an error to a trigger, the trigger causes its invoking DML command to fail with an error. To automatically return errors during the execution of a DML statement, you must use WHENEVER SQLERROR ABORT statement in the trigger body. Otherwise, errors must be checked explicitly within the trigger body after each procedure call or SQL statement.

For any errors in the user written business logic as part of the trigger body, users must use the RETURN SQLERROR statement. For details, see Raising errors from inside triggers.

If RETURN SQLERROR is not specified, then the system returns a default error message when the SQL statement execution fails. Any changes to the database due to the current DML statement are undone and the transaction is still active. In effect, transactions are not rolled back if a trigger execution fails, but the current executing statement is rolled back.

It is the responsibility of the invoking transaction to commit or rollback any DML statements executed within the trigger’s procedure. However, this rule does not apply if the DML command invoking the trigger fails as a result of the associated trigger. In this case, any DML statements executed within that trigger’s procedure are automatically rolled back.

The COMMIT and ROLLBACK statements must be executed outside the trigger body and cannot be executed within the trigger body. If one executes COMMIT or ROLLBACK within the trigger body or within a procedure called from the trigger body or another trigger, the user will get a run-time error.

If a trigger gets into an infinite loop, a solidDB^® server detects this recursive action when the 16-level nesting (or MaxNestedTriggers system parameter maximum is reached). For example, an insert attempt on table T1 activates a trigger and the trigger could call a stored procedure which also attempts to insert into Table T1, recursively activating the trigger. A solidDB^® server returns an error on a user’s insert attempt.

If a set of nested triggers fails at any time, a solidDB^® server rolls back the command which originally activated the triggers.