Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2003
    Posts
    1

    Unanswered: It works, but why the error with the trigger?

    Here is the table declaration:

    REMARK Create the basic TEMP_TABLE_4_33 table
    CREATE TABLE TEMP_TABLE_4_33
    (
    BNAME VARCHAR2(15) NOT NULL,
    WEEK NUMBER(1),
    GAME1 NUMBER(3),
    GAME2 NUMBER(3),
    GAME3 NUMBER(3)
    );

    Here is the trigger code:

    CREATE OR REPLACE TRIGGER check_week_temp_table_4_33
    AFTER INSERT OR UPDATE OF WEEK ON temp_table_4_33
    FOR EACH ROW
    DECLARE
    minWeek number(1) := 0;
    maxWeek number(1) := 4;
    BEGIN
    --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;
    END IF;
    END check_week_temp_table_4_33;

    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?

  2. #2
    Join Date
    Oct 2003
    Location
    Switzerland
    Posts
    140
    Don't know if I understand this correctly but :

    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);

    Good luck.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •