SQL Guide : SQL extensions : Triggers : Triggers and transactions
  
Triggers and transactions
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.
Recursion and concurrency conflict errors
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.
Triggers and recursion
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.)
CREATE TRIGGER do_not_try_this ON employees_and_dependents
  AFTER DELETE
  REFERENCING OLD last_name AS old_last_name
  BEGIN
    EXEC SQL PREPARE del_cursor
    DELETE FROM employees_and_dependents
    WHERE last_name = ?;
    EXEC SQL EXECUTE del_cursor USING (old_last_name);
    -- ... close and drop the cursor.
  END;
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's an example that causes a recursion error:
CREATE TRIGGER do_not_do_this_either ON invoice
  AFTER UPDATE
  REFERENCING NEW total_price AS new_total_price
  BEGIN
    -- Add 8% sales tax.
    EXEC SQL PREPARE upd_curs1
      UPDATE invoice
      SET total_price = 1.08 * total_price
      WHERE ...;
      -- ... execute, close, and drop the cursor...
  END;
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's 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 isn't 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.
Summary of trigger cases
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:
▪Trigger Mode (that is, BEFORE or AFTER)
▪Operation (INSERT, DELETE, or UPDATE)
▪Trigger Action (what the trigger itself attempts to do, such as update the record that was just inserted)
▪Lock Type ("optimistic" or "pessimistic")
▪Result that you will see (for example, that the trigger action was successful, or that the trigger failed for a reason such as a recursion error like the one discussed in the previous section).
For details on interpreting a trigger entry in this table, see Using events - Example 1.
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 resultset (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 resultset (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 resultset (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 resultset (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 resultset and no rows are updated to fire 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 resultset and no rows are updated to fire 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 firing 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 firing 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.
Here's an example entry from the table and an explanation of that entry:
Trigger
Operation
Trigger Action
Lock Type
Result
AFTER
INSERT
UPDATE the same row by adding a number to the value
Optimistic
Record is updated.
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's another example from the table:
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 resultset (as the desired row is not yet inserted in 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: 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.
Flawed Trigger
DROP EMP;
COMMIT WORK;

CREATE TABLE EMP(C1 INTEGER);
INSERT INTO EMP VALUES (1);
COMMIT WORK;
"CREATE TRIGGER TRIG1 ON EMP
  BEFORE UPDATE
  REFERENCING OLD C1 AS OLD_C1
BEGIN
  EXEC SQL WHENEVER SQLERROR ABORT;
  EXEC SQL PREPARE CUR1 DELETE FROM EMP WHERE C1 = ?;
  EXEC SQL EXECUTE CUR1 USING (OLD_C1);
END";

UPDATE EMP SET C1=200 WHERE C1 = 1;
SELECT * FROM EMP;

ROLLBACK WORK;
Note If the row that is updated/deleted were based on a unique key, instead of an ordinary column (as in the example above), solidDB® generates the following error message: 1001: key value not found.
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.
Error handling: 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.
Note Triggered SQL statements are a part of the invoking transaction. If the invoking DML statement fails due to either the trigger or another error that is generated outside the trigger, all SQL statements within the trigger are rolled back along with the failed invoking DML command.
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.
Nested and recursive triggers: 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.
See also
Triggers