Guidelines for Creating Triggers - Analytics Database - Teradata Vantage

SQL Data Definition Language Detailed Topics

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2023-07-11
dita:mapPath
vuk1628111288877.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
B035-1184
lifecycle
latest
Product Category
Teradata Vantage™
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.