CODEWITHSUNDEEP
sqloracle-databaseplsqltriggers
Ankush Kolte
Ankush Kolte

Reputation: 1

Need to create a trigger

select id, status, name , status_change_dt from account;

whenever status changes to any other status status_change_dt should be updated to sysdate 

create or replace
  TRIGGER ADD_DT 
  after UPDATE of status ON account 
  for each row
  BEGIN 
  :new.status_change_dt := sysdate ;
  END;

I am getting error in creating this trigger. Thank you in advance.

Upvotes: 0

Views: 57

Answers (1)

Gurwinder Singh
Gurwinder Singh

Reputation: 39527

You can't change the new value after the update.

Even error is pretty clear on this:

ORA-04084: cannot change NEW values for this trigger type

Use BEFORE instead of AFTER.

Try this:

create or replace
  TRIGGER ADD_DT 
  before UPDATE of status ON account 
  for each row
  BEGIN 
  :new.status_change_dt := sysdate ;
  END;

EDIT:

If you have another table 

account_dtl (ref_id references account(id), status_2);

And account table has one more column to keep track of status_2 changes, use this:

create or replace
  TRIGGER ADD_DT_Status_2 
  before UPDATE of status_2 ON account_dtl
  for each row
  BEGIN 
    update account
    set status2_change_dt := sysdate
    where id = :new.ref_id;
  END;

Although it would make more sense to keep track of status_2 changes in its own table i.e. account_dtl

Upvotes: 4

Related Questions