17.10 - Guidelines for Creating Triggers - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Detailed Topics

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1184-171K
Language
English (United States)

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.