16.20 - Triggers and Stored Procedures - Teradata Database - Teradata Vantage NewSQL Engine

Teradata Vantage™ SQL Data Definition Language Detailed Topics

Teradata Database
Teradata Vantage NewSQL Engine
Release Number
March 2019
Content Type
Programming Reference
Publication ID
English (United States)

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 Statement
  • UPDATE (all forms)
  • EXEC of macros that contain valid SQL statements
  • 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
  • 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

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.