Triggers and Transactions
A trigger executes trigger code within a transaction. It sets the transaction level, then executes the trigger code. Upon successful completion of trigger code, the trigger commits the transaction.
Note:
A consequence of triggers using transactions is that if a trigger invokes code that commits a transaction, completion of the trigger fails because the transaction level has already been decremented to 0. This situation can occur when invoking a Business Service of a Production.
With an AFTER INSERT statement-level ObjectScript trigger, if the trigger sets %ok=0, the insert of the row fails with an SQLCODE -131 error. Depending on your automatic transaction setting, transaction rollback might also occur. In SQL, the SET TRANSACTION %COMMITMODE commitmode option controls this setting. In ObjectScript, the "AutoCommit" option of the SetOption()Opens in a new tab method controls this setting, using this syntax: SET status=$SYSTEM.SQL.Util.SetOption("AutoCommit",intval,.oldval). This table shows the effect the automatic transaction setting has on rollback. It also shows the corresponding commitmode option to set for SQL and "AutoCommit" integer value to set for ObjectScript.
| Automatic Transaction Setting |
%COMMITMODE Option (SQL) |
"AutoCommit" Value (ObjectScript) |
Transaction Rollback Result |
| No automatic transaction processing |
NONE |
0 |
No transaction was started, so the INSERT cannot be rolled back. |
| Automatic transaction commitment is on (default) |
IMPLICIT |
1 |
The transaction for the INSERT is rolled back. |
| Automatic transaction commitment is off |
EXPLICIT |
2 |
It is up to the application to either rollback or commit the transaction for the INSERT. |
The trigger can set an error message in the %msg variable in the trigger. This message will be returned to the caller, giving information why the trigger failed.
The %ok and %msg system variables are described in System Variables.
