Subqueries in the WHERE Clause of DELETE or UPDATE

Subqueries that are specified using the Condition with Subquery syntax in the WHERE clause of the DELETE statement or the UPDATE statement cannot be the triggering event for a Select trigger.

In the following example, the subquery is not the triggering event for any enabled Select triggers that are defined on col2 of tab1:

DELETE tab1 WHERE EXISTS 
   (SELECT col2 FROM tab1 WHERE col2 > 1024);

The DELETE operation in same example, however, activates any enabled Delete triggers that are defined on tab1. No enabled Select trigger on tab1 can be activated by a subquery within a DELETE statement that modifies a table referenced in the FROM clause of the subquery.

Similarly, the subquery in the WHERE clause of the following UPDATE statement is not the triggering event for any enabled Select triggers that are defined on col3 of tab1:

UPDATE tab1 SET col3 = col3 + 10 
   WHERE col3 > ANY 
      (SELECT col3 from tab1 WHERE col3 > 1);

The same example activates any enabled Update trigger that is defined on col3 of tab1, but no Select trigger can be updated by the subquery. For additional restrictions on Select triggers, see Circumstances When a Select Trigger Is Not Activated.