Trigger support has been enhanced to permit remote 3-part names to be used within Native and SQL triggers.
A remote 3-part name can be explicitly coded within the trigger program (e.g. <RDB-name>.<schema-name>.<table-name>) or abstracted from the trigger programs via the CREATE ALIAS (SQL) statement.
Prior to this support, any attempt to use a remote 3-part name within an trigger program would fail with a SQL0751. Explicit CONNECT (SQL) statement remain restricted and will continue to fail with SQL0751.
- The library that the trigger is defined in must exist on the target system, so that a *SQLPKG can be automatically created when the remote 3-part name statement is executed.
- Native triggers are fully supported, meaning any kind of SQL statement using a 3-part name can be compiled in the program and executed when the trigger fires. Native triggers are added to a file using the ADDPFTRG command.
- SQL triggers can utilize remote 3-part names within dynamic SQL statement (i.e. PREPARE and EXECUTE, EXECUTE IMMEDIATE).
- Static SQL statements within an SQL trigger cannot use remote 3-part names and will fail to compile. SQL triggers are created using the CREATE TRIGGER (SQL) statement.
- The remote 3-part name statement execution will use the same isolation level as the triggering action.
- When constructing your trigger program, it is recommended that the trigger program detect the Commitment_Control_Level of the triggering action and use the SET TRANSACTION ISOLATION LEVEL statement as shown here: http://www.ibm.com/support/knowledgecenter/en/ssw_ibm_i_74/dbp/rbafosystrigexample.htm.
13 January 2020