IBM Support

Ability to use 3-part names from within Trigger programs

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 UpdatesDb2 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:

  1. 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.
  2. 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.
  3. SQL triggers can utilize remote 3-part names within dynamic SQL statement (i.e. PREPARE and EXECUTE, EXECUTE IMMEDIATE).
  4. 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.
  5. The remote 3-part name statement execution will use the same isolation level as the triggering action.
  6. 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":"BU054","label":"Systems w\/TPS"},"Product":{"code":"SWG60","label":"IBM i"},"Component":"","Platform":[{"code":"PF012","label":"IBM i"}],"Version":"All Versions","Edition":"","Line of Business":{"code":"LOB08","label":"Cognitive Systems"}}]

Document Information

Modified date:
13 January 2020

UID

ibm11142644