Home
SQL programmingYou can use the HCL OneDB™ implementation of the SQL language to develop applications for HCL OneDB™ database servers.
Guide to SQL: SyntaxThe HCL OneDB™ Guide to SQL: Syntax describes the syntax of the statements, data types, expressions, operators, and built-in functions of the HCL OneDB™ dialect of the SQL language.
SQL statementsThis chapter describes the syntax and semantics of SQL statements that are recognized by HCL OneDB™.
CREATE TRIGGER statementUse the CREATE TRIGGER statement to define a trigger on a table. You can also use CREATE TRIGGER to define an INSTEAD OF trigger on a view.
Subqueries in the WHERE Clause of DELETE or UPDATESubqueries 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.

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.