Example: Ensuring that Parent Table Updates Propagate to the Child - Teradata Vantage

Suppose you want to make sure 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 make sure 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;);