15.00 - Triggers and Referential Integrity - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

Product
Teradata Database
Release Number
15.00
Content Type
Programming Reference
Publication ID
B035-1184-015K
Language
English (United States)

Triggers and Referential Integrity

The following trigger cases return a referential integrity violation error to the requestor for all forms of referential integrity:

  • The triggering statement is an UPDATE on the child table in the relationship.
  • The triggering statement is a DELETE on child table in the relationship.
  • The triggers are cascaded.
  • The following trigger cases for tables with referential integrity relationships are valid if and only if the relationship is defined using batch referential integrity (see “Batch Referential Integrity Constraints” on page 562):

  • The triggering statement is a DELETE operation on the parent table in the relationship.
  • The triggering statement is an UPDATE operation on the parent table in the relationship.
  • The triggering statement is an INSERT operation on the child table in the relationship.
  • The triggering statement is an INSERT operation on the child table in the relationship and defines an INSERT … SELECT operation as the triggered action statement.
  • For cascaded triggers, the system only handles INSERT operations as the triggering statement. For example, an INSERT operation on the parent table and an INSERT on the child table in the relationship.
  • No other cascaded triggers are valid.

    Consider the following table definitions. The tables they define make up a referential integrity checking hierarchy. Batch RI is defined on tables child_tab (referring to parent_tab) and grandchild_tab (referring to child_tab).

    Note that the foreign keys for these tables are defined implicitly using REFERENCES clauses without specifying FOREIGN KEY, but the referential integrity checking they define is the same as it would be if the keywords FOREIGN KEY had been specified.

    Triggers are defined on these tables.

         CREATE TABLE parent_tab ( 
           prime_key INTEGER NOT NULL, 
           column_2  INTEGER, 
           null_col  CHARACTER DEFAULT NULL)
         UNIQUE PRIMARY INDEX (prime_key);
     
         CREATE TABLE child_tab ( 
           prime_key INTEGER NOT NULL,  
           null_col  CHARACTER DEFAULT NULL, 
           for_key   INTEGER REFERENCES WITH CHECK OPTION parent_tab
                     (prime_key))
         UNIQUE PRIMARY INDEX (prime_key);
     
         CREATE TABLE grandchild_tab ( 
           prime_key INTEGER NOT NULL,  
           column_3  INTEGER,
           grc_key   INTEGER REFERENCES WITH CHECK OPTION child_tab
                     (prime_key))
         UNIQUE PRIMARY INDEX (prime_key); 

    Consider the following definitions of the referential levels among these tables and the respective triggering statements that are supported:

     

    At this level …

    The triggering statement is …

    On this table in the RI relationship …

    1

  • DELETE
  • or

  • UPDATE
  • parent

    2

  • DELETE
  • or

  • UPDATE
  • child

    There are batch referential integrity relationships between the table pairs as listed in the following table:

     

    At this level …

    There is a batch referential integrity relationship between these tables …

    1

  • parent_tab
  • child_tab
  • 2

  • child_tab
  • grandchild_tab