Trigger tuning question

[geerae]

I'm trying to clean up some triggers on our database by using "WHEN" statements and am having trouble with those requiring "WHEN INSERTING OR UPDATING". The code below is generating an ORA-00920 Invalid relational operator error.

Create or Replace Trigger Validate_EMP_T1 on EMP
After Insert OR Update
On EMP For Each Row
WHEN ( UPDATING AND ( NEW.salary != OLD.salary
OR NEW.dept_no != OLD.dept_no )
OR INSERTING AND NEW.salary != 0 )
BEGIN
IF UPDATING THEN
code...
ELSIF INSERTING THEN
code...
END IF;
END;

Thanks for any help,
geerae

 

[AOLEURO]

Hi Geerae

You can’t use UPDATING, INSERTING or DELETING in the WHEN clause.

You are telling Oracle that the trigger is a UPDATERING AND INSERTING ON xxx trigger.

The reason is, that Oracle check conditions for the trigger to be executed.

Then Oracle decides to execute the trigger the UPDATING, INSERTING and DELETING are visible insight BEGIN…END.

The OLD.columns value is NULL on inserting rows.

The NEW.columns value is NULL on deleting rows.


You can:
---------
Ether you got the “updating” information based on OLD.column value. Ex. we know that if updating the OLD.EMPNO must have a value – but is NULL on inserting. And you still have a combined trigger with combined code.

Or you should split the trigger in a pure UPDATE and INSERT trigger. Oracle handles this perfectly because then the application makes DML – Oracle search relevant triggers. The code is also faster, because you save a IF UPDATING | INSERTING THEN….END IF; clause.

Ex – Combined trigger:
--------------------------
Create or Replace Trigger Validate_EMP_T1
After Insert OR Update On EMP
For Each Row
WHEN (
( ( NEW.sal != OLD.sal OR NEW.deptno != OLD.deptno ) AND OLD.EMPNO IS NOT NULL) OR
( OLD.EMPNO IS NULL AND NEW.sal != 0 )
)
BEGIN
IF UPDATING THEN
Call Procedure_with_Update_Code;
ELSIF INSERTING THEN
Call Procedure_with_Insert_Code;
END IF;
END;



Ex. Separate triggers:
------------------------
Create or Replace Trigger Validate_EMP_T1
After Update On EMP
For Each Row
WHEN ( NEW.sal != OLD.sal OR NEW.deptno != OLD.deptno )
BEGIN
Call Procedure_with_Update_Code;
END;

Create or Replace Trigger Validate_EMP_T2
After insert On EMP
For Each Row
WHEN ( NEW.sal != 0 )
BEGIN
Call Procedure_with_Insert_Code;
END;


A note about performance in triggers:
------------------------------------------
If you are concerned about performance, you should make the “CODE” in procedure insight package and not write code directly in the trigger. The reason is, that triggers are translated each time but procedures are compiled ones and used several times. And because the call has a reference to the package header, your trigger is not invalidated then “something” changes.


Regards
Allan