Triggers and Tactical Queries - Advanced SQL Engine - Teradata Database

SQL Data Definition Language Detailed Topics

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-24
dita:mapPath
jpx1556733107962.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1184
lifecycle
previous
Product Category
Teradata Vantage™

Teradata Database triggers are parallelized. That is, they are implemented as multistatement requests along with the statement that caused them to fire. Being part of a multistatement request means that the triggers and the triggering statement become a single optimizing unit and a single recovery unit.

Performing a trigger can involve few or all AMPs. The presence of triggers can result in a query becoming an all-AMP operation, even if the base query is highly tuned for few AMPs, because both query and trigger share the same optimized plan.

A trigger execution can be a few AMPs operation or it can be an all-AMPs operation. The presence of triggers can cause a query to become an all-AMP operation even if it is highly tuned for few AMPs because the query and the trigger are both bundled into the same request.

Triggered insert, update, and delete statements are single-AMP operations when the qualifying condition helps derive the primary index value of the triggered table. In other cases, the Optimizer might choose Group AMP operations instead of all-AMP triggered statements.

To gain a better understanding of the impact executing the trigger has on system performance, EXPLAIN the triggering statement. In the following example, a trigger has been specified to perform each time a new supplier is added. To get a feeling for the performance impact of the trigger, you should EXPLAIN the INSERT request.

     CREATE TRIGGER supp_insert AFTER INSERT ON supplier
     REFERENCING NEW AS n
     FOR EACH ROW
      (INSERT INTO supplog VALUES (n.s_suppkey, n.s_acctbal,   
       n.s_nationkey);
     );

     EXPLAIN
     INSERT INTO CAB.supplier
     VALUES (353,'Fosters','133 Meadow',13,'3108437272',0.00,'new')

Teradata Database returns the following EXPLAIN report, with the relevant text highlighted in bold:

     Explanation
     ------------------------------------------------------------
       1) First, we execute the following steps in parallel.
            1) We do an INSERT into CAB.supplier.
            2)  We do an INSERT into CAB.supplog.
       2) Finally, we send out an END TRANSACTION step to all AMPs involved
          in processing the request.

The relevant line of the report is 1.2, We do an INSERT into CAB.supplog.

This trigger is designed to insert a row into a Supplier Log table each time a new Supplier row is added to the database. Both inserts are parallel steps in the query plan, which is highly efficient for tactical queries.

You should always EXPLAIN any single-row update statement that is part of a tactical query, especially if triggers could be involved.