I have a delete trigger when I delete a row from user #1 address table and I want to
insert a corresponding row into an archive address table that belongs to user #2.
If there is any error inserting into the archive address table, I want to insert some
information to an exception table owned by user #2
Insert priviledges have been granted to the 1st user on all the archive tables and the
exception table by user #2. The procedure that the trigger calls is valid. The
procedure is owned by user1.
The initial delete from user 1 works fine and populates the archive log table.
I reinserted the original row and tried to delete it again to get the exception to
write to the
exception table. I get:
SQL> delete from address where addr_id = 3;
delete from address where addr_id = 3
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "USER1.D_ARCH_ADDRESS", line 57
ORA-00001: unique constraint (USER2.PK_ARCH_ADDRESS) violated
ORA-04088: error during execution of trigger 'PKMSPROD.D_ARCH_ADDRESS'
There is only 1 row to delete from the address table. I inserted it and checked it
I excepted the constraint to be violated for the archive address table
I don't get an entry into my exception table
CREATE OR REPLACE TRIGGER D_ARCH_ADDRESS
BEFORE DELETE ON ADDRESS
FOR EACH ROW
v_err_msg VARCHAR2(255) := NULL;
v_err_code VARCHAR2(20) := NULL;
v_process VARCHAR2(9) := NULL;
V_ARCH_CREATED_BY VARCHAR2(30) := NULL;
select user into v_arch_created_by from dual;
INSERT INTO USER2.ARCH_ADDRESS
WHEN OTHERS THEN
select process into v_process from v$session;
CREATE OR REPLACE PROCEDURE TRIG_ERROR_PROC
INSERT INTO USER2.TRIGGER_EXCEPTIONS
This e-mail and any files transmitted with it are the property
of Belkin Components and/or its affiliates, are confidential,
and are intended solely for the use of the individual or
entity to whom this e-mail is addressed. If you are not one
of the named recipients or otherwise have reason to believe
that you have received this e-mail in error, please notify the
sender and delete this message immediately from your computer.
Any other use, retention, dissemination, forwarding, printing
or copying of this e-mail is strictly prohibited.
Please see the official ORACLE-L FAQ: http://www.orafaq.com
Author: Kathy Duret
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).