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

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