15.10 - Example: Ensuring That Parent Table Updates Propagate to its Child - 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

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