15.00 - Triggers and Stored Procedures - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

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

Triggers and Stored Procedures

Stored procedures called from within a triggered action statement can be one or more of the SQL statements summarized in the following table:

 

Trigger Type

Valid Statements

AFTER

  • ABORT
  • CALL
  • DELETE
  • INSERT
  • ROLLBACK
  • SELECT AND CONSUME
  • UPDATE (all forms)
  • EXEC of macros that contain valid SQL statements
  • BEFORE

  • ABORT
  • ROLLBACK
  • SET clause (INSERT and UPDATE row triggers only).
  • EXEC of macros that contain no data-changing SQL statements
  • Triggered statements must run within the same transaction as the statement that fires their trigger. Stored procedures can change the state of a transaction, but the statements within the body of a trigger cannot change the transaction state.

    Executing the SQL CALL statement does not initiate a transaction: it is the execution of the first SQL request or expression inside the stored procedure that initiates a transaction. The system then assigns a request number and transaction number to it if a transaction number has not already been assigned. The system then increments the request number for each SQL request inside the stored procedure. However, the transaction number of the SQL requests depends on the session mode and the status of the transaction in which the CALL request was submitted.

    For this reason, the following statements are not permitted inside the body of any stored procedure that is called from a trigger:

  • All DDL statements
  • All DCL statements
  • The BEGIN TRANSACTION and END TRANSACTION statements
  • All exception handling statements
  • This is not strictly true. Although a stored procedure called by a trigger can validly contain condition handlers, most of them can never be executed when the procedure is called because the first failure on a triggered statement causes the entire request to abort and roll back.

    In general, stored procedures support the following three types of parameters:

  • IN
  • OUT
  • INOUT
  • However, because triggered actions cannot return output to the requestor, the OUT and INOUT parameters are not allowed in any stored procedure that is called from a trigger.

    You can pass the old-values correlation name and the new-values correlation name as parameters, but not the old-values-table alias, new-values-table alias, or old-new-table alias.