Oracle SQL trigger on update of column

Question

I'm trying to create a trigger for my table which automatically adds a published date based on when a certain flag is set to 'Y'

I don't have much experience with creating triggers but so far this is what i've got

  create or replace
  TRIGGER ADD_CREATE_DT 
  after UPDATE of approved ON articles 
  for each row
  BEGIN
  :new.create_dt := sysdate where approved = 'Y';
  END;

I get this error when updating the column

trigger 'USER.ADD_CREATE_DT' is invalid and failed re-validation

Any ideas?

Thanks

Tony Andrews · Accepted Answer · 2011-11-02 14:35:27Z

35

Use the WHEN clause:

create or replace
  TRIGGER ADD_CREATE_DT 
  after UPDATE of approved ON articles 
  for each row
  when (new.approved = 'Y')
  BEGIN
  :new.create_dt := sysdate;
  END;

Or use IF:

create or replace
  TRIGGER ADD_CREATE_DT 
  after UPDATE of approved ON articles 
  for each row
  BEGIN
  if :new.approved = 'Y' then
   :new.create_dt := sysdate;
  end if;
  END;

In this case, WHEN is more appropriate and efficient.

answered Nov 2, 2011 at 14:35

Tony Andrews

133k23 gold badges230 silver badges266 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jamie Taylor Over a year ago

Thanks - couldn't get the 'WHEN' to work but the 'IF' works perfectly

schurik · Accepted Answer · 2011-11-02 14:36:22Z

3

create or replace
  TRIGGER ADD_CREATE_DT 
  after UPDATE of approved ON articles 
  for each row
  BEGIN
    IF :NEW.approved = 'Y' THEN
      :new.create_dt := sysdate;
    END IF;
  END;

answered Nov 2, 2011 at 14:36

schurik

7,9582 gold badges26 silver badges30 bronze badges

Comments

Jeff_Alieffson · Accepted Answer · 2018-09-21 07:37:22Z

2

I don't know What version of Oracle do you use? In Oracle 10g I got the following error:

ORA-04084: cannot change NEW values for this trigger type
04084. 00000 -  "cannot change NEW values for this trigger type"
*Cause:    New trigger variables can only be changed in before row
           insert or update triggers.
*Action:   Change the trigger type or remove the variable reference.

It does not allow to change the field on AFTER triggers.

answered Sep 21, 2018 at 7:37

Jeff_Alieffson

2,8921 gold badge32 silver badges34 bronze badges

1 Comment

David Faber Over a year ago

A BEFORE trigger definitely seems more appropriate in this case (where the column being updated belongs to the same table that the trigger is on).

Collectives™ on Stack Overflow

Oracle SQL trigger on update of column

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related