Converting existing triggers to support advanced capabilities

You can convert existing basic triggers to take advantage of advanced capabilities, including support for more SQL statements, including SQL PL in the trigger body, support for more variable types, and other advantages.

Before you begin

Advanced triggers are supported at application compatibility level V12R1M500 or higher.

You can identify basic triggers by querying the SYSIBM.SYSTRIGGERS catalog table. Blank values in the SQLPL column identify basic triggers.

About this task

Advanced triggers offer the following advantages over basic triggers:

  • In the trigger definition, an advanced trigger can:
    • Include more types of SQL statements, including SQL PL control statements, dynamic SQL statements, and SQL comments.
    • Define and reference more types of variables, including SQL variables and global variables.
    • Explicitly specify bind options.
    • Define multiple versions of the trigger.
  • All transition variables are nullable.
  • ALTER TRIGGER statements can change options, and change or regenerate the trigger body.
  • The OR REPLACE clause can be used in CREATE TRIGGER (advanced) statements. It enables the use of a single CREATE statement to either define a new trigger or trigger version, or update an existing trigger or trigger version if it already exists.

For more information about the differences between basic and advanced triggers, see Triggers.

Procedure

To change an existing basic trigger into an advanced trigger, complete the following steps:

  1. Modify the original CREATE TRIGGER statement into a CREATE TRIGGER (advanced) statement by removing any of the following items:
    • The MODE DB2SQL clause. Db2 attempts to creates a basic trigger if that clause is included.
    • Stand-alone fullselect or VALUES statements. You can use SELECT INTO statement or VALUES INTO statements instead.
  2. Use one of the following approaches to convert to the new advanced trigger definition:
    • Issue the modified CREATE TRIGGER (advanced) statement with the OR REPLACE clause.
    • Issue a DROP statement for the original trigger and then issue the new CREATE TRIGGER statement.
    The existing trigger is effectively dropped, and an advanced trigger is defined. If multiple triggers are defined on the associated table, the trigger activation order changes.

What to do next

If multiple triggers are defined on the associated table, you might need to restore the original the activation order of the triggers. To do that, you must drop and re-create any triggers that were created after the converted trigger was originally created, in the same order that they were originally created. For more information about the activation order of multiple triggers, see Activation order of multiple triggers.