It is possible to receive an error when you execute a trigger. The error might be due to execution of SQL statements or business logic. If a trigger returns an error, it causes the initiating DML statement to fail. 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.
For any errors in user-written business logic that is part of the trigger body, users can receive errors in a procedure variable by using the RETURN SQLERROR error_string or RETURN SQLERROR char_variable statements.
The error is returned in the following format:
User error: error_string
If a user does not specify the RETURN SQLERROR statement in the trigger body, all trapped SQL errors are raised with a default error string that is determined by the system.
Note Triggered SQL statements are a part of the initiating transaction. If the initiating 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 statement.
The following example uses WHENEVER SQLERROR ABORT to make sure that the trigger catches an error in a stored procedure that it calls.
-- If you return an SQLERROR from a stored procedure, the error is -- displayed. However, if the stored procedure is called from inside -- a trigger, then the error is not displayed unless you use the -- SQL statement WHENEVER SQLERROR ABORT. CREATE TABLE table1 (x INT); CREATE TABLE table2 (x INT);
"CREATE PROCEDURE stproc1 BEGIN RETURN SQLERROR 'Here is an error!'; END"; COMMIT WORK;
"CREATE TRIGGER displays_error ON table1 BEFORE INSERT BEGIN EXEC SQL WHENEVER SQLERROR ABORT; EXEC SQL EXECDIRECT CALL stproc1(); END"; COMMIT WORK; "CREATE TRIGGER does_not_display_error ON table2 BEFORE INSERT BEGIN EXEC SQL EXECDIRECT CALL stproc1(); END"; COMMIT WORK;
-- CALL stproc1() displays an error. -- INSERT INTO table1 (x) values (1) displays an error because the trigger includes WHENEVER SQL ERROR ABORT. -- INSERT INTO table2 (x) values (1) does not display an error.