Triggers - Pros and Cons

[Joey]

We have a very timid I.S. Development Director who doesn't want to put
triggers on any tables. She is willing to think about it if we can
come up with pros & cons for using them. The triggers will let us
perform a guaranteed ETL. Can you all give me some thoughts on this
subject?

(BTW, if we don't use triggers we will have to create monitors &
crawlers to do the work anyway so system overhead is probably not a
good *con* but if you think it is worth mentioning or have some
insight into the area, let me know)

[Thomas Raddatz]

Joey,

We use triggers for all new tables that need to perform related updates in other tables or that even
need to perform any other things that must happen on particular updates.

So far the advantages truly exceed the disadvantages. Triggers have made our application more stable
and reliable. You can not forget to update something if it is done by a trigger. The only disadvantage
I have in mind is that triggers impact performance a bit. Some guys say that it is a disadvantage that
you can not easily fix a problem just by starting DFU/SQL because the triggers also fire for DFU/SQL
updates. In fact actually this behavior is the best advantage triggers have. Regardless of what
utility a user takes - triggers are always in place.

We also use triggers to ensure that some updates are rejected dependent on data constellations.

If you really need to fix something without a trigger, you can disable it using CHGPFTRG. CHGPFTRG
will also re-enable the trigger after the fix is done. So there is nothing you lose.

For each file with one or more triggers you should write a CL that contains the commands to add the
triggers. Otherwise you will not be able to properly remember which triggers must be added to which
files. Of course you can also use a REXX procedure to add the triggers. This way you do not need to
compile a CL program. Last but not least you can also put the parameters used to add the triggers into
a table and use a little program to read the table and add the triggers.

I do not want to miss triggers again. As well as I do not want to miss constraints, journaling and
commitment control.

Thomas Raddatz.

Joey schrieb:

[Elvis]

Triggers will impact performance but benefits may very well outgain
the drawbacks. If performance becomes a serious issue, you can always
offload trigger workload to a separate job by utilizing a data queue
as interprocess synchronization mechanism. Advantage of this approach
is that trigger work is minimal then (just send a message to a data
queue) so inline performance impact is minimal.

[Bradley V. Stone]

Joey wrote:

Triggers do the work you want done, without crawlers and monitors.
Performance impact vs. them will be the same in the long run. You're
doing X amount of work, right? Triggers won't make that any worse.

The only cons I can think of is not fully thinking through the trigger
implementation. For example, adding a trigger that when fired, updates
a record in the same file and causes an infinate loop (I've seen it
happen a few times!)

Bottom line, Triggers rock when applied correctly. Just don't overdo it
or try to use triggers to make up for a bad design that is pre-existing.
Kinda like LFs and stored procedures. :)

Brad
www.bvstools.com