I have this table

CREATE table Pharmacists (
PhFname varchar2(10),
PhLname varchar2(10),
Salary number(7),
Nationality varchar2(10),
Shift_Start varchar2(2),
Shift_End varchar2(2),
PhID number(7) primary key,
Super_ID number(7) ,
bdate date,
constraint super_fk foreign key (Super_ID) references Pharmacists (PhID) on delete cascade);

I tried to create this trigger

create or replace trigger updShE
after update of Shift_Start on Pharmacists
update Pharmacists
set Shift_End=to_number(:new.Shift_Start, '99') +8 
where PhID = :New.PhID ;

but this error appears

new or old reference not allowed in table level trigger

  • 1
    Unrelated, but: I would store shift_start and shift_end (which seems to be a time of day information) as a number to make calculations easier. E.g. minutes since midnight or just "hours" if you don't need minutes. Storing numbers in varchar columns is usually a bad idea – a_horse_with_no_name Dec 8 '17 at 7:37

You need a row before level trigger for that.

And you don't need an update, just assign the new value:

create or replace trigger updshe
  BEFORE update of Shift_Start on Pharmacists
  :new.shift_end := to_number(:new.Shift_Start, '99') + 8;
  • error can't change new values for this trigger type – Rand alrand Dec 8 '17 at 7:46
  • @Randalrand: ah sorry, I overlooked that you are using an after trigger. You obviously need a BEFORE trigger for that – a_horse_with_no_name Dec 8 '17 at 7:49
  • thank you it's working now but why before not after? why should we not use update? – Rand alrand Dec 8 '17 at 8:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.