BEFORE TRIGGER causes ORA-04091

Question

i just started with SQL and having a Problem: i wanted to write a Before Update Trigger but when i try to Update the Table i always get that error (ORA-04091). I dont really get why; since i thought the before update trigger has no problems with mutated tables.

 BEFORE UPDATE ON ORD
 FOR EACH ROW
 DECLARE
 shipping2 varchar(10);
 BEGIN
 SELECT SHIPDATE into shipping2 
 FROM ORD 
 WHERE ORDID = :old.ORDID;

 IF (shipping2 is not NULL) then
    raise_application_error(20121,'ORDER already on the way!!!');
 end if;

 END;
-------------------------------------------------------

UPDATE ORD
SET TOTAL = 222
WHERE ORDID = 603;

Answer 1

This error indicates that you cannot query the table on which the trigger was fired within the trigger itself.

But in your use case, it seems like you do not need to query the table. If you want to access the current value of column SHIPDATE on the record that is about to be updated, you can simply use :OLD.SHIPDATE.

So something like:

BEFORE UPDATE ON ORD
FOR EACH ROW
BEGIN
    IF (:old.SHIPDATE IS NOT NULL) THEN
        RAISE_APPLICATION_ERROR(20121,'ORDER already on the way!!!');
    END IF;

END;