Restrictions on Creating and Using Triggers | Teradata Vantage - Restrictions on Creating and Using Triggers - Analytics Database - Teradata Vantage

SQL Data Definition Language Detailed Topics

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2023-07-11
dita:mapPath
vuk1628111288877.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
B035-1184
lifecycle
latest
Product Category
Teradata Vantage™
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.