Errors encountered in a trigger

If any SQL statement in the trigger body fails during trigger execution, DB2® rolls back all changes that are made by the triggering SQL statement and the triggered SQL statements. However, if the trigger body executes actions that are outside of the control of DB2, or are not under the same commit coordination as the DB2 subsystem in which the trigger executes, DB2 cannot undo those actions. Examples of external actions that are not under the control of DB2 are:

• Performing updates that are not under RRS commit control
• Sending an electronic mail message

If the trigger executes external actions that are under the same commit coordination as the DB2 subsystem under which the trigger executes, and an error occurs during trigger execution, DB2 places the application process that issued the triggering statement in a must-rollback state. The application must then execute a rollback operation to roll back those external actions. Examples of external actions that are under the same commit coordination as the triggering SQL operation are:

• Executing a distributed update operation
• From a user-defined function or stored procedure, executing an external action that affects an external resource manager that is under RRS commit control.