CREATE/REPLACE TRIGGER Examples | Teradata Vantage - Example: Ensuring That Parent Table Updates Propagate to its Child - Advanced SQL Engine - Teradata Database

SQL Data Definition Language Syntax and Examples

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

Suppose you want to ensure that all UPDATEs and DELETEs to a parent table are propagated to a child table. This example, which is designed to enforce referential integrity procedurally, defines two AFTER statement triggers on the parent table to ensure that primary key updates to the parent table propagate to the appropriate foreign key column in a child table.

These are the table definitions:

      CREATE TABLE parent_tab (
       prime_key INTEGER, 
       column_2  INTEGER, 
      column_3  INTEGER)
     UNIQUE PRIMARY INDEX (prime_key);
     CREATE TABLE child_tab (
       prime_key INTEGER, 
       for_key   INTEGER, 
       column_3 INTEGER
     FOREIGN KEY (for_key) REFERENCES WITH NO CHECK OPTION
                           parent_tab(prime_key));

Now define the triggers on Parent_Tab, the subject table:

      CREATE TRIGGER UpdateForKey
       AFTER UPDATE OF prime_key ON parent_tab
       REFERENCING OLD TABLE AS OldTable NEW TABLE AS NewTable
     FOR EACH STATEMENT (
       UPDATE child_tab
       SET for_key=NewTable.prime_key
       WHERE child_tab.for_key=OldTable.prime_key;);
     CREATE TRIGGER DelForKey
       AFTER DELETE ON parent_tab
       REFERENCING OLD TABLE AS OldTable
     FOR EACH STATEMENT (
       UPDATE child_tab
       SET for_key=NULL
       WHERE for_key=OldTable.prime_key;);