CODEWITHSUNDEEP
sqloracle-databasetriggers
Ali
Ali

Reputation: 8100

Oracle trigger does not allow Inserts on table

An "After Insert" oracle trigger stops any inserts that meet a certain condition. The trigger is checking the column "CLASS_TIME" and if it is less than 9 am or greater than 6 pm then update the inserted row. But the row is not inserted altogether hence its not possible to update the same row. Below is the trigger code 

create or replace TRIGGER ENFORCE_CLASS_TIMINGS 
AFTER INSERT ON SYSTEM.TUTPRAC 
REFERENCING OLD AS OLD1 NEW AS NEW1
FOR EACH ROW
WHEN ( 
  to_number(to_char(to_date(NEW1.CLASS_TIME,'hh24:mi'),'sssss')) < 
    to_number(to_char(to_date('09:00', 'hh24:mi'), 'sssss')) OR
  to_number(to_char(to_date(NEW1.CLASS_TIME,'hh24:mi'),'sssss')) > 
    to_number(to_char(to_date('18:00', 'hh24:mi'), 'sssss')))

BEGIN

  UPDATE SYSTEM.TUTPRAC
    SET STAFFNO = NULL
  WHERE
    CLASSID = :NEW1.CLASSID;

    COMMIT;
END;

Please suggest how to fix it.

Upvotes: 0

Views: 168

Answers (1)

Michael Broughton
Michael Broughton

Reputation: 4055

Try this (I removed REFERENCING OLD as OLD1 as it has no meaning in an insert trigger):

create or replace TRIGGER ENFORCE_CLASS_TIMINGS 
BEFORE INSERT ON SYSTEM.TUTPRAC 
REFERENCING NEW AS NEW1
FOR EACH ROW
WHEN ( 
  to_number(to_char(to_date(:NEW1.CLASS_TIME,'hh24:mi'),'sssss')) < 
    to_number(to_char(to_date('09:00', 'hh24:mi'), 'sssss')) OR
  to_number(to_char(to_date(:NEW1.CLASS_TIME,'hh24:mi'),'sssss')) > 
    to_number(to_char(to_date('18:00', 'hh24:mi'), 'sssss')))

BEGIN
  :NEW1.STAFFNO := NULL;
END;

Upvotes: 1

Related Questions