Defines a condition that narrows the cases in which the trigger occurs in a Delete statement, Set Variable statement, or Update Window statement.
The WHEN clause is syntactically almost identical to the selection condition version of the WHERE clause. It is optionally used in a Delete statement, Set Variable statement, or Update Window statement to create a trigger condition that evaluates rows arriving in the stream or named window specified by the ON clause. When a WHEN clause is present, the trigger that initiates the deletion, update, or insertion of rows from the named window, or the setting of a variable occurs only when the incoming row in the triggering stream or named window meets the trigger condition. In the absence of the WHEN clause, the deletion or variable setting is triggered whenever a row arrives in the stream or window specified by the ON clause.
The trigger condition in this clause can include literals, column references from the data stream or window specified by the ON clause, operators, scalar and miscellaneous functions, and parentheses. The WHEN clause cannot include subqueries or aggregate functions.A WHEN clause cannot include subqueries.
A WHEN clause cannot include aggregate functions.
In a Delete statement, column references in the WHEN clause cannot refer to the columns of the named window specified in the DELETE FROM clause.
Delete Statement
Set Variable Statement
Update Window Statement
ON
WHERE
The WHEN clause in the following example initiates a trigger for deletion from a named window only when the Price column in Win1 contains a value greater than 50.00:
ON Win1 WHEN Price > 50.00 DELETE FROM Win2 WHERE Win2.Price < 50.00;
The WHEN clause in this example initiates a trigger for the setting of the Delay_Length variable only when the Delay column in StreamIn is smaller than 10:
ON StreamIn WHEN Delay < 10 SET Delay_Length = 'Short';