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.