CODEWITHSUNDEEP
plsqltriggersoracle11g
Bishan
Bishan

Reputation: 15702

After Insert Update Trigger PL/SQL

I have a table as Student and there is a varchar type column as status. i need to set value to this status column after insert or update process on this table. i have tried to write a Trigger for this. but i cant use as :new.status. it gives Error: ORA-04084: cannot change NEW values for this trigger type. how could i do this?

My Code

create or replace
TRIGGER STUDENT_AIU_TRI 
AFTER INSERT OR UPDATE ON STUDENT
FOR EACH ROW 
DECLARE

v_status VARCHAR2(2);

BEGIN


  v_status := '1';

  select v_status into :NEW.status from dual;
END;

Upvotes: 2

Views: 7627

Answers (1)

Justin Cave
Justin Cave

Reputation: 231661

If you want to change the value in a column, you'd need to use a BEFORE INSER OR UPDATET trigger, not an AFTER INSERT OR UPDATE trigger. Why do you believe that you need to use an AFTER INSERT OR UPDATE trigger?

You also don't need the local variable or to SELECT from dual

CREATE OR REPLACE TRIGGER trigger_name
  BEFORE INSERT OR UPDATE ON student
  FOR EACH ROW
BEGIN
  :new.status := '1';
END;

Upvotes: 7

Related Questions