News
Abstract
Trigger support has been enhanced to permit remote 3-part names to be used within Native and SQL triggers.
Content
You are in: IBM i Technology Updates > Db2 for i - Technology Updates > Db2 for i Functional Enhancements > Ability to use 3-part names from within Trigger programs
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.
Usage detail:
- 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.
[{"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:
13 January 2020
UID
ibm11142644