Advanced trigger support

Db2 12 introduces support for advanced triggers, while continuing to support the existing triggers created in previous releases, which are now considered basic triggers.

Any trigger created before activation of function level 500 or higher in Db2 12, or in earlier Db2 releases, is a basic trigger. Advanced triggers are supported at application compatibility level V12R1M500 or higher.

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 about the differences between basic and advanced triggers, see Triggers.