At times, it is possible to receive an error in executing a trigger. The error may be due to execution of SQL statements or business logic. If a trigger returns an error, it causes its invoking DML command 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 the user-written business logic as part of the trigger body, users can receive errors in a procedure variable 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 determined by the system.
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.
Below is an example of using 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;
-- This shows that the error is returned if you execute the stored procedure. CALL stproc1(); -- Displays an error because the trigger had WHENEVER SQL ERROR ABORT. INSERT INTO table1 (x) values (1); -- Does not display an error. INSERT INTO table2 (x) values (1);