16.20 - Rules for Using Scalar Subqueries in Triggers - Teradata Database - Teradata Vantage NewSQL Engine

Teradata Vantage™ SQL Data Definition Language Detailed Topics

Product
Teradata Database
Teradata Vantage NewSQL Engine
Release Number
16.20
Release Date
March 2019
Content Type
Programming Reference
Publication ID
B035-1184-162K
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, Teradata Database 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, Teradata Database 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.
  • Teradata Database 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, Teradata Database 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.