Triggers and Tactical Queries - 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™

Vantage 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')

Vantage 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.