Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Trigger creates without compilation error but while running shows errors

Trigger creates without compilation error but while running shows errors

From: gamaz <gamaz_at_eathlink.net>
Date: Tue, 19 Nov 2002 20:24:57 GMT
Message-ID: <t6xC9.7171$%32.669891@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'
Received on Tue Nov 19 2002 - 14:24:57 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US