Triggered Action Timing

The actions triggered can then take place BEFORE, AFTER, or INSTEAD OF the triggering action.

BEFORE simply means that the trigger’s actions will take place before the triggering action. Uses for this include calculating some value that is needed for the triggering action or checking constraints before taking an action. If you’re going to change the value of of some field being updated or inserted as a part of the triggering action, this is usually the way to do it.

AFTER means that the trigger’s actions will take place after the triggering action. This is usually used to populate other tables or to note in other tables that changes were made. In a WebSphere Commerce environment, both staging triggers and triggers for selective cache invalidation are AFTER triggers.

INSTEAD OF triggers, I’ve never actually used. My understanding is that their main application is in dealing with inserts to otherwise non-insertable views.

Trigger Granularity

Triggers can either fire with every row (FOR EACH ROW) or just once for a statement including a triggering action (FOR EACH STATEMENT). In practice, I’ve only actually used FOR EACH ROW. I think these are pretty self-explanatory. FOR EACH ROW means that the triggered action takes place once for each row affected by the triggering action. FOR EACH STATEMENT means that the triggered action takes place only once for each statement that includes a triggering action, reguardless of how many rows are affected.

Trigger Issues

Triggers are meant to be small pieces of SQL that run fast. Their execution must be complete for any transaction with the triggering action to complete. The time it takes a trigger to complete affects the total length of a transaction, so it is important to be aware of that for performance. Sometimes people try to put too much into a trigger. If you find yourself struggling with a trigger too much, it is reasonable to ask if the actions it is taking shouldn’t be coded into an application.

Triggers can also get into circular references, more than many areas of DB2. For example if you have a trigger that fires on update of table ‘SALES’, but then in turn updates the ‘SALES’ table, you may get an error about circular references.

It is important to test performance after adding triggers – in a few cases, I’ve seen poorly written triggers increase response time drastically.

Since triggers can contain multiple SQL statements which are themselves terminated with a semicolon, usually files of create trigger statements use an alternate terminatior. I’ve most frequently seen @ or # used. Because of this, they are usually executed with slightly different syntax. My normal go-to syntax for executing db2 SQL files is:

db2 -tvf filename >filename.out

For triggers (or stored procedures or other compound SQL), I use syntax more like this:

Ember is always curious and thrives on change. Working in IT provides a lot of that change, but after 17 years developing a top-level expertise on Db2 for mid-range servers and more than 7 years blogging about it, Ember is hungry for new challenges and looks to expand her skill set to the Data Engineering role for Data Science. With in-depth SQL and RDBMS knowledge, Ember shares both posts about her core skill set and her journey into Data Science.
Ember lives in Denver and work from home for XTIVIA, leading a team of Db2 DBAs.