after insert update trigger in Oracle

Question

I have a simple table with 3 columns, brand, division and subdivision. Subdivision column is nullable. User can insert or update .

Examples:

brand A , div1 , null
brand A , div2 , div2
brand B , div1 , div1

I need a trigger which sets the subdivision to null if the brand column is inserting or updating to another brand as brand A.

So the 3rd insert should fire the trigger to set the subdivision to Null. If a user updates the 2nd row, for example, set the brand to brand C then the trigger should set the subdivision to Null.

I use such a trigger but I get a mutating error:

create or replace TRIGGER TR_replace    
AFTER
INSERT OR UPDATE
ON table1 REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
BEGIN
UPDATE table1 SET subdivision = NULL WHERE :NEW.brand != 'brand A';
END;

Answer 1

• you can use IF, that's OK
• another option is to use trigger's WHEN clause which then makes executable section simpler and clearer
• also, there's no point in REFERENCING clause which actually doesn't do anything; old and new are defaults

---

CREATE OR REPLACE TRIGGER tr_replace
   BEFORE INSERT OR UPDATE
   ON table1
   FOR EACH ROW
   WHEN (new.brand <> 'brand A')
BEGIN
   :new.subdivision := NULL;
END;
/