Kathy,
Read the error messages from the bottom up, and you can figure out what's
happening.
First, it looks to me that the primary key on the USER2.ARCH_ADDRESS table
is being violated - like the record already exists so the insert statement
fails.
Then, in the exception clause, you 'select process into v_process from
v$session;'. This causes the ORA-01422 error. This causes the trigger to
fail, so that you do not get the opportunity to call the trig_error_proc
procedure.
Finally, in your TRIG_ERROR_PROC procedure, remove the 'commit' clause. You
will eventually get an error here when you fix the other problems - you do
not want a commit in a proc called by a trigger, as a commit is already
underway.
Hope this helps.
Tom Mercadante
Oracle Certified Professional
-----Original Message-----
Sent: Thursday, December 06, 2001 6:35 PM
To: Multiple recipients of list ORACLE-L
I am brain dead and can't figure this one out probably something really
basic.
8.1.6 database
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 myself
I excepted the constraint to be violated for the archive address table
I don't get an entry into my exception table
TRIGGER CODE:
CREATE OR REPLACE TRIGGER D_ARCH_ADDRESS
BEFORE DELETE ON ADDRESS
FOR EACH ROW
DECLARE
v_err_msg VARCHAR2(255) := NULL;
v_err_code VARCHAR2(20) := NULL;
v_process VARCHAR2(9) := NULL;
V_ARCH_CREATED_BY VARCHAR2(30) := NULL;
BEGIN
select user into v_arch_created_by from dual;
INSERT INTO USER2.ARCH_ADDRESS
(
WHSE ,
ADDR_ID ,
ADDR_TYPE ,
ADDR_KEY_1 ,
ADDR_KEY_2 ,
ADDR_LINE_1 ,
ADDR_LINE_2 ,
ADDR_LINE_3 ,
CITY ,
STATE ,
ZIP ,
CNTRY ,
CONTACT ,
PHONE ,
FAX ,
EMAIL ,
CREATE_DATE_TIME ,
MOD_DATE_TIME ,
USER_ID ,
ARCH_CREATE_DATE_TIME ,
ARCH_CREATED_BY
) VALUES
(
'IY' ,
:old.ADDR_ID ,
:old.ADDR_TYPE ,
:old.ADDR_KEY_1 ,
:old.ADDR_KEY_2 ,
:old.ADDR_LINE_1 ,
:old.ADDR_LINE_2 ,
:old.ADDR_LINE_3 ,
:old.CITY ,
:old.STATE ,
:old.ZIP ,
:old.CNTRY ,
:old.CONTACT ,
:old.PHONE ,
:old.FAX ,
:old.EMAIL ,
:old.CREATE_DATE_TIME ,
:old.MOD_DATE_TIME ,
:old.USER_ID ,
sysdate,
v_arch_created_by
);
EXCEPTION
WHEN OTHERS THEN
select process into v_process from v$session;
v_err_code:=sqlcode;
v_err_msg :=sqlerrm;
trig_error_proc('IY',v_process,v_err_code,v_err_msg,sysdate,v_arch_created_
by);
END;
/
PROCEDURE ---
CREATE OR REPLACE PROCEDURE TRIG_ERROR_PROC
(in_whse varchar2,
in_process varchar2,
in_err_code varchar2,
in_err_msg varchar2,
in_create_date_time date,
in_created_by varchar2)
is
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO USER2.TRIGGER_EXCEPTIONS
(
WHSE,
PROCESS,
ERROR_CODE,
ERROR_MSG,
CREATE_DATE_TIME,
CREATED_BY
) VALUES
(
in_whse,
in_process,
in_err_code,
in_err_msg,
in_create_date_time,
in_created_by
);
commit;
END;
/
sho err
Confidential
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
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Mercadante, Thomas F
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
