All About Triggers in DB2 V10

I am writing what DB2 did more in V10. The developer has to focus more on performance, and logic for any type of business scenario.

IBM did some changes in Create Trigger functionality

More flexible and function-rich CREATE TRIGGER support. The trigger-event clause can now contain more than one INSERT, UPDATE, or DELETE operation, which means that a trigger can be activated by any combination of specified events.

Although you can specify one, two, or all three trigger events in a single CREATE TRIGGER statement, a trigger event cannot be specified more than once.

You can also use the new trigger event predicates (INSERTING, UPDATING, and DELETING) in a triggered action to test the event that activated a trigger.

A trigger event predicate is only valid in the triggered action of a compiled trigger definition. And finally, the FOR EACH STATEMENT option is now supported in the CREATE TRIGGER statement for PL/SQL triggers. You can create triggers that fire only once per statement.(Source: IBM)

Types of Triggers

  1. DML Triggers
  2. DDL Triggers
  3. Database Event Triggers
  4. INSTEAD OF Triggers
  5. AFTER SUSPEND Triggers
  6. Maintaining Triggers

Added new scalar functions

A scalar function is a suction that returns a single value. All built-in functions are Scalar Functions.

INSTRB – Returns starting position in bytes.

TO_SINGLE_BYTE – Returns a string in which multi-byte chars are converted into single-byte chars.


Author: Srini

Experienced software developer. Skills in Development, Coding, Testing and Debugging. Good Data analytic skills (Data Warehousing and BI). Also skills in Mainframe.