15.00 - Triggered Action Statements - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

prodname
Teradata Database
vrm_release
15.00
category
Programming Reference
featnum
B035-1184-015K

Triggered Action Statements

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 Teradata 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 non‑unique.