Back to Basics – Triggers and Advanced Triggers

Frank Rhodes, BMC Software

Download PDF

Triggers move the business rule application logic into the database, which results in faster application development and easier maintenance. The business rule is centralized to the triggers and is no longer repeated in several applications.

In Db2 12 for z/OS with new function mode activated, IBM enhanced support for triggers and introduced the object as an Advanced Trigger. IBM continues to support existing triggers from previous releases and refers to them as Basic Triggers.

Prior to Db2 version 12, Basic Triggers supported a limited set of SQL statements because of which the trigger text had to call a stored procedure to provide this additional capability. As a result, it took increased time to develop and deploy applications. Calling a stored procedure in the trigger text can be inefficient. This increases the cost of using Db2 for z/OS, degrades the application performance for CPU and elapsed time, and increases the maintenance cost to manage more objects and corresponding code.

Restrictions of Basic Trigger has been overcome by Advanced Trigger which provides support for complex and logical SQL/PLs such that, with Advanced Triggers, users can now directly use SQL/PL statements in the text instead of calling native SQL stored procedures. Advanced Triggers also provides enhancements like – ability to define and maintain multiple “Versions”, allowing users with debugging capabilities and to better align itself with the DB2 family and SQL standards.

We will also cover in this presentation the following topics:

  • Introduction to Triggers and Event Driven Triggers
  • Trigger Types and Differences (Basic and Advanced Trigger)
  • Why Should you Care About Advanced Triggers?
  • Advanced Trigger Operations, Syntax and Trigger Options
  • Advanced Trigger Ordering and Versioning
  • Trigger Restrictions, Considerations and Cascading Concepts
  • Advanced Trigger Example