Triggers and transactions

Triggers do not require a commit from the invoking transaction in order to execute; DML statements alone cause triggers to execute. 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, even if autocommit is on, each statement inside the trigger is not committed when it is executed; instead, the entire trigger body is executed as part of the INSERT, UPDATE, or DELETE statement that initiated the trigger. Either the entire trigger (and the statement that initiated it) is committed, or else the entire trigger (and the statement that initiated it) is rolled back.

If a DML statement updates or deletes a row that causes a trigger to be initiated, you cannot update or 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 (see Summary of trigger cases), that indicates the trigger situations that do and do 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.

A trigger that contains a statement that causes the same trigger to execute again on the same record is recursive. For example, a trigger is recursive if the trigger is initiated by the update of a record and the trigger then attempts to update that same record.

If a database server allowed unlimited recursion in triggers, the server processing might go into an infinite loop and never finish executing the statement that initiated the trigger. However, a solidDB server detects recursive action when the default 16-level nesting (or level set by the SQL.MaxNestedTriggers system parameter) is reached, (see SQL section), and returns an appropriate error, such as Too many nested triggers.

A concurrency conflict error occurs when a trigger executes an operation that competes with the statement that initiated 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 initiated when a record is deleted, and that trigger tries to delete the record that initiated 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.

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

Imagine a scenario where an employee has resigned from a job and their medical coverage requires cancellation. The medical coverage also requires cancellation for the employee dependents. A business rule for this situation is implemented by creating a trigger; the trigger is executed when an employee record is deleted and statements inside the trigger then delete the employee 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 in the company has a unique last name and that this name is the same for all the dependents.

Assume that an employee, John Smith, resigns and their medical coverage is deleted. When you delete the record for John Smith, the trigger is invoked immediately and tries to delete ALL people named with a last name of Smith. This includes not only the employee dependents, but also the employee themself, since their name meets the criteria in the WHERE clause.

Every time an attempt is made to delete the employee record, the trigger is initiated and another attempt is made to delete the record.

A similar situation can happen with UPDATE. In this scenario, a trigger adds sales tax every time that a record is updated:

In this scenario, a customer calls up to change their 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 initiated, so updating the record one time, causes the trigger to update it again and updates are repeated in an infinite loop.

In some cases, BEFORE triggers can also cause concurrency problems. For example, in the first scenario, if the trigger is a BEFORE trigger (rather than an AFTER trigger), then just before the employee is deleted, the trigger deletes everyone with the last name of Smith. After the trigger is executed, the engine resumes its original task of dropping employee John Smith, but the server finds either the employee is not there or that the 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 INSERT actions, as well as UPDATE and DELETE actions.

The table is divided into the following columns:

For details on interpreting a trigger entry in this table, see Example: Waiting on a single event.

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

In this situation, you have a trigger that is initiated AFTER an INSERT operation is done. The body of the trigger contains statements that update the same row that was inserted (that is, the same row as the one that initiated the trigger). If the lock type is optimistic, then the result is that the record gets updated.

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

Here is another example from the table:

In this case, you try to insert a record but, before the insertion takes place, the trigger is executed. In this case, the trigger tries to update the record (for example, to add sales tax to it). However, since the record is not yet inserted, the UPDATE statement 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 executed. There is no error message, so you might 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, 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 the initiating DML statement to fail with an error. To automatically return errors during the execution of a DML statement, you must use the 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.

Trigger mode	Operation	Trigger action	Lock type	Result
AFTER	INSERT	UPDATE the same row by adding a number to the value	Optimistic	Record is updated.
AFTER	INSERT	UPDATE the same row by adding a number to the value	Pessimistic	Record is updated.
BEFORE	INSERT	UPDATE the same row by adding a number to the value	Optimistic	Record is not updated since the WHERE condition of the UPDATE within the trigger body returns a NULL result set (as the desired row is not yet inserted in the table).
BEFORE	INSERT	UPDATE the same row by adding a number to the value	Pessimistic	Record is not updated since the WHERE condition of the UPDATE within the trigger body returns a NULL result set (as the desired row is not yet inserted in the table).
AFTER	INSERT	DELETE the same row that is being inserted	Optimistic	Record is deleted.
AFTER	INSERT	DELETE the same row that is being inserted	Pessimistic	Record is deleted.
BEFORE	INSERT	DELETE the same row that is being inserted	Optimistic	Record is not deleted since the WHERE condition of the DELETE within the trigger body returns a NULL result set (as the desired row is not yet inserted in the table).
BEFORE	INSERT	DELETE the same row that is being inserted	Pessimistic	Record is not updated since the WHERE condition of the UPDATE within the trigger body returns a NULL result set (as the desired row is not yet inserted in the table).
AFTER	INSERT	INSERT a row	Optimistic	Too many nested triggers.
AFTER	INSERT	INSERT a row	Pessimistic	Too many nested triggers.
BEFORE	INSERT	INSERT a row	Optimistic	Too many nested triggers.
BEFORE	INSERT	INSERT a row	Pessimistic	Too many nested triggers.
AFTER	UPDATE	UPDATE the same row by adding a number to the value	Optimistic	Generates Solid Table Error: Too many nested triggers.
AFTER	UPDATE	UPDATE the same row by adding a number to the value	Pessimistic	Generates Solid Table Error: Too many nested triggers.
BEFORE	UPDATE	UPDATE the same row by adding a number to the value.	Optimistic	Record is updated, but does not get into a nested loop because the WHERE condition in the trigger body returns a NULL result set and no rows are updated to initiate the trigger recursively.
BEFORE	UPDATE	UPDATE the same row by adding a number to the value.	Pessimistic	Record is updated, but does not get into a nested loop because the WHERE condition in the trigger body returns a NULL result set and no rows are updated to initiate the trigger recursively.
AFTER	UPDATE	DELETE the same row that is being updated.	Optimistic	Record is deleted.
AFTER	UPDATE	DELETE the same row that is being updated.	Pessimistic	Record is deleted.
BEFORE	UPDATE	DELETE the same row that is being updated.	Optimistic	Concurrency conflict error.
BEFORE	UPDATE	DELETE the same row that is being updated.	Pessimistic	Concurrency conflict error.
AFTER	DELETE	INSERT a row with the same value.	Optimistic	Same record is inserted after deleting.
AFTER	DELETE	INSERT a row with the same value.	Pessimistic	Hangs at the time of initiating the trigger.
BEFORE	DELETE	INSERT a row with the same value.	Optimistic	Same record is inserted after deleting
BEFORE	DELETE	INSERT a row with the same value.	Pessimistic	Hangs at the time of initiating the trigger.
AFTER	DELETE	INSERT a row with the same value.	Optimistic	Record is deleted.
AFTER	DELETE	UPDATE the same row by adding a number to the value.	Pessimistic	Record is deleted.
BEFORE	DELETE	UPDATE the same row by adding a number to the value.	Optimistic	Record is deleted.
BEFORE	DELETE	UPDATE the same row by adding a number to the value	Pessimistic	Record is deleted.
AFTER	DELETE	DELETE same row	Optimistic	Too many nested triggers.
AFTER	DELETE	DELETE same record	Pessimistic	Too many nested triggers
BEFORE	DELETE	DELETE same record	Optimistic	Concurrency conflict error.
BEFORE	DELETE	DELETE same record	Pessimistic	Concurrency conflict error.

Trigger	Operation	Trigger action	Lock type	Result
AFTER	INSERT	UPDATE the same row by adding a number to the value	Optimistic	Record is updated.

Trigger	Operation	Trigger action	Lock type	Result
BEFORE	INSERT	UPDATE the same row by adding a number to the value	Optimistic	Record is not updated since the WHERE condition of the UPDATE within the trigger body returns a NULL result set (as the desired row is not yet inserted in the table).

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

If a RETURN SQLERROR statement 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 initiating transaction to commit or rollback any DML statements that are executed within the trigger procedure. However, this rule does not apply if the DML statement that initiates the trigger fails as a result of the associated trigger. In this case, any DML statements executed within that trigger 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 a COMMIT or ROLLBACK statement is executed within the trigger body or within a procedure called from the trigger body, the user receives a run-time error.

If a trigger gets into an infinite loop, a solidDB server detects this recursive action when the default 16-level nesting (or level set by the SQL.MaxNestedTriggers system parameter) is reached, see SQL section.

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

Triggers