16.20 - Triggers and Referential Integrity - Teradata Database - Teradata Vantage NewSQL Engine

Teradata Vantage™ SQL Data Definition Language Detailed Topics

Product
Teradata Database
Teradata Vantage NewSQL Engine
Release Number
16.20
Release Date
March 2019
Content Type
Programming Reference
Publication ID
B035-1184-162K
Language
English (United States)
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):
  • 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