Guidelines for Creating Triggers
The following guidelines for developing triggers are intended to minimize their performance
Design the triggering statement so that in normal situations it changes as few rows
as possible, preferably a single row or a small number of rows
Avoid complex cascading actions by row triggers.
For instance, you can specify an appropriate condition in the WHEN clause to limit
the number of times the clause is tested.
Use triggers only where complex processing and validation of data are required.
Use other alternatives, such as macros and declarative constraints, when they are
Avoid writing applications that reference tables associated with triggers that change
a large number of rows because such applications can impact throughput significantly.
Avoid writing triggers that add workload to the data-changing statements that are
triggered. For example:
For statements that change a single row per request, additional action is needed for
each trigger fired.
For statements that might change a large number of rows per request, a row trigger
has to make a number of secondary changes for each affected row.
Avoid writing row triggers that access the triggering table directly or the transition
tables through OLD TABLE or NEW TABLE correlation names that require join or aggregation
operations because of their potentially high performance impact.
Compare the respective effects of row and statement triggers for an application before
deciding on which to implement.