The WHEN clause

Use the WHEN clause to fine-tune the situations under which the body of the trigger code will actually execute. In the following example, I use the WHEN clause to make sure that the trigger code does not execute unless the new salary or new commission is changing to a different value:

CREATE OR REPLACE TRIGGER check_raise AFTER UPDATE OF salary, commission ON employee FOR EACH ROWWHEN ((OLD.salary != NEW.salary OR (OLD.salary IS NULL AND NEW.salary IS NULL)) OR (OLD.commission != NEW.commission OR (OLD.commission IS NULL AND NEW.commission IS NULL)))BEGIN ...

In other words, if a user issues an UPDATE to a row and for some reason sets the salary to its current value, the trigger will and must fire, but the reality is that you really don't need any of the PL/SQL code in the body of the trigger to execute. By checking this condition in the WHEN clause, you avoid some of the overhead of starting up the PL/SQL block associated with the trigger.

The genwhen.sp file offers a procedure that will generate a WHEN clause to ensure that the new value is actually different from the old.

The WHEN clause can be used only with row-level triggers. You will get a compilation error (ORA-04077) if you try to use it with statement-level triggers.

In most cases, you will reference fields in the OLD and NEW pseudo-records in the WHEN clause, as in the example shown above. You may also, however, write code that invokes built-in functions, as in the following WHEN clause that uses SYSDATE to restrict inserts to between 9 AM and 5 PM:

CREATE OR REPLACE TRIGGER valid_when_clauseBEFORE INSERT ON frameFOR EACH ROWWHEN ( TO_CHAR(SYSDATE,'HH24') BETWEEN 9 AND 17 ) ...

Here are some things to keep in mind when using the WHEN clause:

· Enclose the entire logical expression inside parentheses. These parentheses are optional in an IF statement, but required in the trigger WHEN clause.

· Do not include the ":" in front of the OLD and NEW names. This colon (indicating a host variable) is required in the body of the trigger PL/SQL code, but cannot be used in the WHEN clause.

· You can only invoke SQL built-infunctions from within the WHEN clause; you will not be able to call user-defined functions or functions defined in built-in packages (such as DBMS_UTILITY). Attempts to do so will generate an ORA-04076: invalid NEW or OLD specification error. If you need to invoke such functions, move that logic into the beginning of the trigger execution section.

FOR EACH ROW Before WHEN In some versions of PL/SQL, the compiler requires that FOR EACH ROW be specified before the WHEN clause because it assumes that a statement trigger is being created unless told otherwise. SQL> CREATE OR REPLACE TRIGGER row_must_be_before_when 2 BEFORE INSERT ON frame 3 WHEN ( new.strike = 'Y' ) 4 FOR EACH ROW 5 BEGIN NULL; END; 8 / ERROR at line 3:ORA-04077: WHEN clause cannot be used with table level triggers SQL> CREATE OR REPLACE TRIGGER row_must_be_before_when 2 BEFORE INSERT ON frame 3 FOR EACH ROW 4 WHEN ( new.strike = 'Y' ) 5 BEGIN NULL; END; 8 / Trigger created.