Restrictions on Creating and Using Triggers | Teradata Vantage - 17.10 - Restrictions on Creating and Using Triggers - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Detailed Topics

Advanced SQL Engine
Teradata Database
Release Number
July 2021
English (United States)
Last Update
The following restrictions apply to the creation and use of triggers:
  • You can define triggers only for persistent base tables.
    You cannot define triggers for any of the following database objects:
    • Error tables
    • Global temporary tables, including global temporary trace tables
    • Views
    • Volatile tables
  • BEFORE statement triggers are not valid.
  • BEFORE triggers cannot have data-changing statements as their triggered action (triggered SQL statements).
  • You cannot define triggers and hash indexes on the same table.
  • You can specify UDT comparisons in the WHEN clause as long as the UDTs have defined orderings.
  • If you reference a NEW_TABLE, OLD_TABLE, or OLD_NEW_TABLE transition table in a WHEN condition, the reference must be made:
    • From a subquery
    • Using correlation names for the referenced transition tables

      The typical subquery used for this purpose is an aggregate that returns a scalar value.

  • Aggregates cannot appear on the left hand side of the search condition specified for a WHEN clause.

    Aggregates can appear on the right hand side of the search condition.

  • Positioned (updatable cursor) update and delete operations cannot fire a trigger. An attempt to do so generates an error.

    You must disable all triggers defined on a subject table prior to positioning cursors for update or delete operations on it.

  • You cannot use an INSERT … SELECT AND CONSUME statement as either of the following:
    • Triggered action statement.
    • Triggering statement.
  • You cannot refer to a recursive view, a WITH clause, or a WITH RECURSIVE clause in the definition of a trigger.