The following guidelines for developing triggers are intended to minimize their performance impact:
- 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 more advantageous.
- 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.