CODEWITHSUNDEEP
sqloracle-databasetriggersoracle11g
Henrique
Henrique

Reputation: 57

Updating value via trigger AFTER UPDATE Oracle 11g

I'm developing a small library database and I don't want to allow someone to update someone's ID. But I need to use AFTER UPDATE and FOR EACH STATEMENT (which I'm told is Oracle's default). So, basically, if someone updates the customer info and alter his/her ID or mistypes it, the trigger will automatically update it again to the old value. The problem is that Oracle won't let me use :NEW and :OLD when using FOR EACH STATEMENT. Are there any workarounds to this issue? 

CREATE OR REPLACE TRIGGER alter_id_trigger
AFTER UPDATE ON CUSTOMER
BEGIN
   UPDATE CUSTOMER SET ID = :OLD.ID
   WHERE ID = :NEW.ID;
END;

Thank you!

Upvotes: 1

Views: 13604

Answers (2)

ngrashia
ngrashia

Reputation: 9904

Use the below code for trigger. Changes done:

  1. Using BEFORE UPDATE instead of AFTER UPDATE.

  2. Setting the value of ID to what it was previously. (The ID Field would never be modified)

    CREATE OR REPLACE TRIGGER ALTER_ID_TRIGGER BEFORE UPDATE ON CUSTOMER BEGIN SET :NEW.ID = :OLD.ID END;

Note: With BEFORE UPDATE:

  • You can not create a BEFORE trigger on a view.
  • You can update the :NEW values.
  • You can not update the :OLD values.

Upvotes: 4

Gordon Linoff
Gordon Linoff

Reputation: 1271241

I think you want a before update trigger:

CREATE OR REPLACE TRIGGER alter_id_trigger
BEFORE UPDATE ON CUSTOMER
BEGIN
   SET :NEW.ID = :OLD.ID
END;

You could test to see if the value is being changed, but that seems unnecessary.

Upvotes: 2

Related Questions