15.10 - Example: Cascaded Triggers - Teradata Database

Teradata Database SQL Data Definition Language Syntax and Examples

prodname
Teradata Database
vrm_release
15.10
created_date
December 2015
category
Programming Reference
featnum
B035-1144-151K

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);