Bookmark and Share
RSS

Recent Posts

DB2 12 Debuts Advanced Triggers

May 09, 2017

A trigger defines a set of actions that represent the business logic that should occur when INSERT, UPDATE or DELETE operations are performed on a given table or view. Triggers can be used to update tables or generate or transform values to be used to insert or update rows. They can also invoke functions that operate within and outside of DB2.

A trigger is created using the data definition language (DDL) statement, CREATE TRIGGER. As noted, triggers enforce business logic. Because this logic is stored in the database and executed on the server, they're a better-performing alternative to sending the SQL over the network from a client application. However, the CREATE TRIGGER statement is incapable of supporting many features application developers needed to enforce complex business rules. When complex business rules are needed, developers must write a stored procedure that would activate specific triggers.  

Triggers created prior to DB2 12--specifically,  the activation of function level M500 or higher (V12R1M500)--are considered basic triggers. Those created after the activation of M500 are advanced triggers. Advanced triggers can contain the same traditional SQL used in basic triggers, as well as SQL Procedure Language (SQL PL).

DB2 12 advanced triggers offer these benefits:
  • It's easier to create highly functional triggers without having to call stored procedures.
  • They're compatible with DB2 for Linux/UNIX/Windows (LUW).
  • Multiple versions can be defined and maintained (via the ALTER TRIGGER, Add Version or Activate Version statements).
Another very significant improvement comes with the capability to maintain a trigger activation order. Prior to DB2 12, if multiple triggers are activated and they need to continue to execute in a specific order, you'd have to drop and recreate all the triggers in that same order. However, DB2 12 allows you to maintain a sequence of triggers
 
The order of activation is based on the timestamp of when the trigger was created. DB2 12 provides three ways to modify a trigger and maintain the activation order:
  • Issue a CREATE or REPLACE with the same version:
    CREATE TRIGGER trigger-name BEFORE INSERT ON EMP VERSION V1 …;
  • Issue an ALTER TRIGGER REPLACE VERSION with the same version:
    ALTER TRIGGER trigger-name REPLACE VERSION V1 …;
  • Issue an ALTER TRIGGER ADD VERSION with a different version followed by an
    ALTER TRIGGER ACTIVATE VERSION:

    ALTER TRIGGER trigger-name ADD VERSION V2…;
        ALTER TRIGGER trigger-name ACTIVATE VERSION V2 …;

 The DB2 Knowledge Center has much more on advanced triggers. I'll include this quick list of additional capabilities:

 DB2 12, DB2 for z/OS, an advanced trigger definition can:
  • Define and reference SQL variables
  • Include more types of traditional SQL statements than in previous releases, including dynamic SQL statements
  • Include SQL PL control statements
  • Reference global variables, or assign values to global variables
  • Explicitly specify options, including bind options
  • Include SQL comments
  • Include definitions for multiple versions of the trigger
  • In addition, the following changes are introduced to advanced triggers:
    • All transition variables are nullable
    • An ALTER TRIGGER statement can be used to change trigger options or regenerate the trigger body
    • An advanced trigger can be debugged.

Posted May 09, 2017 | Permalink

comments powered by Disqus