17.10 - Triggered Action Statements - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Detailed Topics

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1184-171K
Language
English (United States)

Triggered action statements can be one or more of the SQL statements summarized in the following table:

Trigger Type Valid Statements
AFTER
  • ABORT
  • CALL
  • DELETE
  • INSERT
  • INSERT … SELECT

    This applies only to standard INSERT … SELECT statements. INSERT … SELECT AND CONSUME statements are not valid as triggered action statements.

  • ROLLBACK
  • Atomic Upsert
  • UPDATE
  • EXEC of macros containing any of the valid statements
BEFORE
  • ABORT
  • EXEC of macros containing no data-changing statements
  • ROLLBACK
  • SET clause (INSERT and UPDATE row triggers only).

You can also execute UDFs and call stored procedures from within triggered action statements. Any valid triggered action statement can contain UDT expressions.

The general rules for the use of these statements in SQL also apply to their use as triggered action statements, with the following differences:
  • No CLIv2 response parcels are generated by the database after execution of the triggered action statements.
  • The triggered action statements report messages to the requesting user only for aborts and failures.
  • The execution of the triggered action statements is atomic, meaning that the SQL transaction cannot be explicitly terminated.

    If the execution of such atomic SQL statement is unsuccessful, then the system cancels all the data or structural changes made by the statement.

The action of the triggering statement returns a single response, with the following information:
  • Success or failure.
  • Activity type of the triggering statement.
  • A count of rows changed by the triggering statement.

The following points about some triggered action statements are important:

IF this statement is specified … THEN …
INSERT… SELECT direct references by this statement to the triggering table are treated as outer references.
Atomic Upsert the condition must be on a primary index, whether unique or nonunique.