Re: Trigger creates without compilation error but while running shows errors

"gamaz" <gamaz_at_eathlink.net> wrote in message news:t6xC9.7171$%32.669891_at_newsread1.prod.itd.earthlink.net...
> Hi,
> I have created a trigger that would not allow any insert or delete on a
> emp_test table from now on.(The table already has values inside). While
> creating the trigger it does not give error. However, while trying to
delete
> one record it is showing errors. Any help or advise is appreciated in
> adavance. The following is the code of trigger along with the MESSAGES.
> Thanks, Regards.
>
> create trigger emp_test_IDtg
> before insert or delete on emp_test
> for each row
> declare
>
> INSERT_DELETE_ERROR EXCEPTION;
>
> begin
> RAISE INSERT_DELETE_ERROR;
>
> EXCEPTION
>
> WHEN INSERT_DELETE_ERROR THEN
> RAISE_APPLICATION_ERROR (-20001, 'Inserting or deleting not allowed at
this
> stage');
> end;
> /
>
> THE MESSAGES:
> SQL> create trigger emp_test_IDtg
> 2 before insert or delete on emp_test
> 3 for each row
> 4 declare
> 5
> 6 INSERT_DELETE_ERROR EXCEPTION;
> 7
> 8 begin
> 9 RAISE INSERT_DELETE_ERROR;
> 10
> 11 EXCEPTION
> 12
> 13 WHEN INSERT_DELETE_ERROR THEN
> 14 RAISE_APPLICATION_ERROR (-20001, 'Inserting or deleting not allowed
at
> this stage');
> 15 end;
> 16 /
>
> Trigger created.
>
> SQL> delete from emp_test where empno = 7900;
> delete from emp_test where empno = 7900
> *
> ERROR at line 1:
> ORA-20001: Inserting or deleting not allowed at this stage
> ORA-06512: at "SCOTT.EMP_TEST_IDTG", line 11
> ORA-04088: error during execution of trigger 'SCOTT.EMP_TEST_IDTG'
>

Look closely at the first error number: it's 20001, which is the error number your trigger calls. The other errors are just the trigger handling the fact (or rather, handling it badly!) that the 'before delete' condition has triggered a 20001 error.

The trigger is working perfectly, as far as I can see.

Regards
HJR
>
Received on Tue Nov 19 2002 - 15:33:24 CST