17.00 - Example: Cascaded Triggers - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
Release Date
September 2020
Content Type
Programming Reference
Publication ID
B035-1144-170K
Language
English (United States)

This example demonstrates how one triggered action statement can cause another trigger to fire.

These are the table definitions.

     CREATE TABLE tab1 (
       a INTEGER, 
       b INTEGER, 
       c INTEGER);
     CREATE TABLE tab2 (
       d INTEGER, 
       e INTEGER, 
       f INTEGER);
     CREATE TABLE tab3 (
       g INTEGER, 
       h INTEGER, 
       i INTEGER);

These are the trigger definitions.

     CREATE TRIGGER trig1
       AFTER INSERT ON tab1
       REFERENCING NEW AS NewRow
     FOR EACH ROW (
       INSERT INTO tab2
       VALUES (NewRow.a + 10, NewRow.b + 10, NewRow.c););
     CREATE TRIGGER trig2
       AFTER INSERT ON tab2
       REFERENCING NEW AS NewRow
     FOR EACH ROW (
       INSERT INTO tab3
       VALUES (NewRow.d + 100, NewRow.e + 100, NewRow.f););

Now, suppose the following INSERT request is submitted:

     INSERT INTO tab1 
     VALUES (1,2,3);

This triggering event fires a trigger to insert into tab2. This operation is equivalent to the following INSERT request:

     INSERT INTO tab2 
     VALUES (11,12,3);

This triggering event fires a trigger to insert into tab3. This operation is equivalent to the following INSERT request:

     INSERT INTO tab3 
     VALUES (111,112,3);