17.10 - Rules for Using Scalar Subqueries in Triggers - 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)
The following rules apply to specifying scalar subqueries in triggers:
  • You can specify an ABORT or ROLLBACK statement with a scalar subquery in the body of a trigger. However, Vantage processes any uncorrelated scalar subquery you specify in the WHERE clause of an ABORT statement in a row trigger as a single-column single-row spool instead of as a parameterized value.
  • You can specify a DELETE statement with a scalar subquery in the body of a trigger. However, Vantage processes any uncorrelated scalar subquery you specify in the WHERE clause of a DELETE statement in a row trigger as a single-column single-row spool instead of as a parameterized value.
  • You can specify an INSERT statement with scalar subqueries in the body of a trigger.
  • You cannot specify a uncorrelated scalar subquery as a value in the multivalue of a simple INSERT in the body of a row trigger.
  • Vantage processes any uncorrelated scalar subquery specified in the SELECT component of an INSERT … SELECT in a row trigger as a single-column single-row spool instead of as a parameterized value.
  • You can specify an UPDATE statement with scalar subqueries in the body of a trigger.

    However, Vantage processes any uncorrelated scalar subqueries specified in the WHERE or SET clauses of an UPDATE statement in a row trigger as a single-column single-row spool instead of as a parameterized value.