CREATE OR REPLACE TRIGGER check_week_temp_table_4_33
AFTER INSERT OR UPDATE OF WEEK ON temp_table_4_33
FOR EACH ROW
minWeek number(1) := 0;
maxWeek number(1) := 4;
--Check if the weeks entered are out of bounds
IF (:new.WEEK < minWeek or :new.WEEK > maxWeek) then
raise_application_error(-20001, 'Week entered out of bounds.');
delete from temp_table_4_33 where WEEK = :new.WEEK;
It compiles and stuff. Here is the test sql statement:
SQL> insert into temp_table_4_33 values('Ravi Singh', 5, 100, 200, 300);
insert into temp_table_4_33 values('Ravi Singh', 5, 100, 200, 300)
ERROR at line 1:
ORA-20001: Week entered out of bounds.
ORA-06512: at "SCOTT.CHECK_WEEK_TEMP_TABLE_4_33", line 7
ORA-04088: error during execution of trigger 'SCOTT.CHECK_WEEK_TEMP_TABLE_4_33'
I check the table and it doesn't have the illegal, but why the error executing message? Should I even care if it works?
Calling raise_application_error raises an exception in the pl/sql block, i.e. control is transferred to the exception handler of that block (the end of your trigger code). As you don't have any exception handler, the exception is propagated to the calling block (your insert statement). As the exception is never handled explicitely, the transaction gets rolled back.
In other words,
a) The execution of the trigger code NEVER gets to DELETE FROM TEMP_TABLE_. If it did, you would hit the error "table TEMP_TABLE_ is mutating..."
b) You will never see the inserted row, as a rollback occurs
c) The error message is "normal" behaviour when calling raise_application_error
d) An alternative would be to drop the trigger, and replace it by a check constraint, for example :
ALTER TABLE TEMP_TABLE ADD CONSTRAINT CHK_TEMP_TABLE_WEEK CHECK (WEEK BETWEEN 0 AND 4);