News
Abstract
SIGNAL support for native triggers
Content
You are in: IBM i Technology Updates > Db2 for i - Technology Updates > Db2 for i Functional Enhancements > SIGNAL support for native triggers
INSERT/UPDATE/DELETE SQL statements have been changed to recognize when system triggers have used the SIGNAL SQL statement to communicate failure detail with the application.
If the system trigger executes the SIGNAL statement and sends an escape message to its caller, the SQL INSERT/UPDATE/DELETE statement will fail with MSGSQL0438 (SQLCODE=-438) instead of MSGSQL0443.
The SQLSTATE, MSG, and other values within the SQL diagnostics area or SQLCA will contain the values passed into the SIGNAL statement.
The Database Programming book (Recommendations for trigger programs) contains recommendations for native trigger programs, which includes the following:
Signal an exception if an error occurs or is detected in the trigger program. If an error message is not signalled from the trigger program, the database assumes that the trigger ran successfully. This might cause the user data to end up in an inconsistent state.
The SIGNAL SQL statement provides the SQL linkage between the native trigger and the application which caused the trigger to be fired through the use of SQL.
Signal an exception if an error occurs or is detected in the trigger program. If an error message is not signalled from the trigger program, the database assumes that the trigger ran successfully. This might cause the user data to end up in an inconsistent state.
The SIGNAL SQL statement provides the SQL linkage between the native trigger and the application which caused the trigger to be fired through the use of SQL.
The SIGNAL SQL statement does not signal an exception, so be sure to use QMHSNDPM() to send an escape message, after executing the SIGNAL statement.
Example JDBC failure:
*** SQLException caught ***
Statement was insert into mylib.mytable values(1)
SQLState: IWF99
Message: [SQL0438] DOCUMENT NOT FOUND
Vendor: -438
java.sql.SQLException: [SQL0438] DOCUMENT NOT FOUND
at com.ibm.as400.access.JDError.throwSQLException(JDError.java:650)
at com.ibm.as400.access.JDError.throwSQLException(JDEr
[{"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SWG60","label":"IBM i"},"Component":"","Platform":[{"code":"PF012","label":"IBM i"}],"Version":"All Versions","Edition":"","Line of Business":{"code":"LOB57","label":"Power"}}]
Was this topic helpful?
Document Information
Modified date:
14 January 2020
UID
ibm11167754