CODEWITHSUNDEEP
oracle-databasetriggers
Pato
Pato

Reputation: 311

after insert update trigger in Oracle

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;

Upvotes: 0

Views: 56

Answers (2)

Littlefoot
Littlefoot

Reputation: 142720

  • 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;
/

Upvotes: 2

Alex Poole
Alex Poole

Reputation: 191235

Don't try to update the row in the table, update the NEW pseudorecord before insert:

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

fiddle

Upvotes: 2

Related Questions