| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Trigger exception problem
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: KathyD_at_belkin.com 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mercadante, Thomas F INET: NDATFM_at_labor.state.ny.us 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).Received on Fri Dec 07 2001 - 08:42:18 CST
 |
 |