Your review has helped me in fixing the problem that i had when -- commiting in a procedure which is called by a trigger.

Thanks for that.

Tom,

I am not sure whether this is clear to you.
I have this particular situation:

We have a before update trigger on col1 of table 1.
This trigger calls a procedure.
The procedure inserts into another table table2 ;
Now when there is no error, the trigger works fine. But when there is some exception in the procedure called by the trigger ( no data found ), i want to log the error in a log table.
I do not handle the exception in the procedure. I try to handle it in trigger by making it an autonomous transaction . But its giving "autonomous transaction detected " error. Also I want the initial update to fail when the procedure throws the exception and the error to propagate to the front end application.

I have the code sample below :

CREATE OR REPLACE TRIGGER TRIGGER1
BEFORE UPDATE OF FLAG1 ON
TABLE1
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
declare
pragma autonomous_transaction;
errormsg varchar2(500);
BEGIN
PROC1_SP(:new.COL1,:new.COL2 ,:new.COL3 ,..);
EXCEPTION
WHEN OTHERS THEN
errormsg:=SQLERRM;
INSERT INTO MY_LOG(run_date,ERROR_MSG) VALUES ( SYSDATE,errormsg);
COMMIT;
RAISE;
END;
Can you suggest me some solution ?

Tom,

That was really useful for me.I felt really stupid when I realized that I was compromising data integrity unknowingly...
Now one more question - Is it possible to return some value from the called procedure (using out parameter doesn't work ) when an execption occurs ?

Thanks again for your swift response.

Tom,

I have this scenario
Two columns of a table have before update triggers. The triggers call a procedure.
I handle the exception in the exception block of the trigger and send a mail on error.
When both columns are modified, and when procedure called in the first trigger completes without any exception,the second trigger is also fired. But when the procedure called in the first trigger raises an exception which iam handling , the second trigger is not getting fired.

Can you please explain why this is happening ?

nope -- but only cause I don't have a concrete test case from you with MINIMAL CODE (make it small small small)

here is my simple (and small) example showing this working:

ops$tkyte@ORA920LAP> drop table t;

Table dropped.

ops$tkyte@ORA920LAP>
ops$tkyte@ORA920LAP> create table t ( x int, y int );

Table created.

ops$tkyte@ORA920LAP>
ops$tkyte@ORA920LAP> create or replace procedure p( p_x in number )
  2  as
  3  begin
  4      if ( mod(p_x,2) = 1 )
  5      then
  6          raise PROGRAM_ERROR;
  7      end if;
  8  end;
  9  /

Procedure created.

ops$tkyte@ORA920LAP>
ops$tkyte@ORA920LAP> create or replace trigger t_trigger_1
  2  before insert on t for each row
  3  begin
  4      dbms_output.put_line( 'in trigger 1' );
  5      p(:new.x);
  6  exception
  7      when program_error then
  8          dbms_output.put_line( 'bummer, I got this exception' );
  9  end;
 10  /

Trigger created.

ops$tkyte@ORA920LAP>
ops$tkyte@ORA920LAP> create or replace trigger t_trigger_2
  2  after insert on t for each row
  3  begin
  4      dbms_output.put_line( 'in trigger 2' );
  5  end;
  6  /

Trigger created.

ops$tkyte@ORA920LAP>
ops$tkyte@ORA920LAP>
ops$tkyte@ORA920LAP> insert into t values ( 0, 1 );
in trigger 1
in trigger 2

1 row created.

ops$tkyte@ORA920LAP> insert into t values ( 1, 1 );
in trigger 1
bummer, I got this exception
in trigger 2

1 row created. 

Tom,

I have put a raise statement in the last line of the exception block in each of the triggers. Is that the reason why the second trigger is not fired when the first one raises an exception though they are independent triggers on two different columns ?

Please help me in resolving this .

Tom,
Thanks for your swift response and the explanation..
I didnt realize that there is a single statement that was updating both the columns, since i was new to the application..

Thanks very much..

"Is it possible to return some value from the called
procedure (using out parameter doesn't work ) when an execption occurs ? "


It is possible. You need to set the value of the out parameter in the exception block. I typically set this to 0 for success, or the Oracle error code vale (sqlcode) for failure.


Thus if value of out parameter p_error_out is 0, our code executed successfully.

--create a table to hold the events of objects creation


23:02:31 SQL> 
23:02:38 SQL> CREATE TABLE ddl_log (
23:02:39   2   operation   VARCHAR2(30),
23:02:39   3   obj_owner   VARCHAR2(30),
23:02:39   4   obj_type   VARCHAR2(30),
23:02:39   5   object_name VARCHAR2(30),
23:02:39   6   sql_text    VARCHAR2(64),
23:02:39   7   attempt_by  VARCHAR2(30),
23:02:39   8   attempt_dt  DATE); 
 
Table created.

--define a DDL trigger 
--store events into table ddl_log
--if someone is creating a new table
-- then set the numblks to 100


23:02:39 SQL> 
23:02:56 SQL>  CREATE OR REPLACE TRIGGER bcs_trigger
23:02:57   2    BEFORE CREATE
23:02:57   3    ON SCHEMA
23:02:57   4    
23:02:57   5    DECLARE
23:02:57   6     oper ddl_log.operation%TYPE;
23:02:57   7    BEGIN
23:02:57   8      INSERT INTO ddl_log
23:02:57   9      SELECT ora_sysevent, ora_dict_obj_owner,
23:02:57  10      ora_dict_obj_type,
23:02:57  11      ora_dict_obj_name, NULL, USER, SYSDATE
23:02:57  12      FROM dual;
23:02:57  13      
23:02:57  14      if ora_dict_obj_type='TABLE' then
23:02:57  15         dbms_stats.set_table_stats(ora_dict_obj_owner,
23:02:57  16          ora_dict_obj_name, numblks=>100);
23:02:57  17      end if;
23:02:57  18   END bcs_trigger;
23:02:57  19   /

Trigger created.



23:02:59 SQL> CREATE TABLE t1_test (
23:03:09   2  testcol VARCHAR2(20));
CREATE TABLE t1_test (
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-04092: cannot COMMIT in a trigger
ORA-06512: at "SYS.DBMS_STATS", line 5953
ORA-06512: at line 11


23:03:09 SQL> select count(*) from ddl_log;

  COUNT(*)
----------
         0


any way to work around it?

Hello Tom:

I have a after insert trigger defined on TABLE A with area_cod and telephone_numbers. When a new telephone_number is inserted, I have code which will insert in a seperate table the range of startTN and endTN ranges for that areacode. However, This trigger is seeing the new telephone_number that is just being inserted into TABLE A, causing the TABLE B range not displaying this new telephone number??? Is this how a trigger works?? Is there any work around to capture the the current inserted value as well???

I am using oracle 10g

thankyou for your help.

venkat

Hello Tom:

Sorry for not being clear. I have a table with lets say countrycode, areacode, telnumber. When a bulknumbers are inserted into the table tableA, I will have to populate another table tableRangeCount with range of these(and any already there).
TAble: TableRangeCount:
COUNTY
AREACODE
STARTNUMBER
ENDNUMBER

values could be:
USA 305 3052220001 3052225000
USA 305 3052227000 3052228000

When I insert a new block (ex: 5001-6000) trigger on TableA will kick-off and refresh the above table TableRangeCount and refresh the ranges for that areacode.

ISSUE: The code for ROW TRIGGER works correctly for all the numbers in the range except the very last number(OR if only one number is inserted then that number). When above block of numbers 5001-6000 are inserted, the table TableRangeCount is getting populated as follows:
USA 305 3052220001 3052225999
USA 305 3052227000 3052228000
INSTEAD OF
USA 305 3052220001 3052226000
USA 305 3052227000 3052228000

likewise, if only one number (ex: 6001) is inserted, the trigger doesnot even insert this one number. ie. The very last number somehow is not being recognized inside the trigger code.



create or replace trigger tab_aft_trigger on
after insert
on tableA
for each row
declare
CURSOR tabrowcur
IS
select telNumber
from tableRangeCount
where country='USA'
and area_code=:NEW.area_code;
BEGIN

FOR tcur in tablerowcur
LOOP

DELETE tableRangeCount
WHERE country='USA'
and area_code=:new.area_code;

FOR LOOP

logic to check for startNumber, EndNumber ranges
looking into tableA and populate tableRangeCount with correct startNumber, endNumber ranges.

END LOOP;

END;
COMMIT;

END;
/


I was wondering why inside the trigger on TableA, it is not able to recognize the last number inserted from tableA. Could this be a bug??? or some other issue??

thankyou very much for your help.

venkat

Hello Tom:

Thankyou very much for you feedback. As you have suggested, I started using the stored procedure instead of triggers. Thankyou very much for all your help.

thankyou
venkat

Tom,
Had a developer to call proc in exception clause of trigger. That proc does a commit without using autonomous transaction.

I turned on tracing for the process and specified a tracefile_identifier. Got the trace file from DBA
I found the insert statement happening in the proc
where the commit happens afterward
But yet in trace file there is no ora-0402 error.

Matter of fact the proc is called from exception b/c of no data found being raised in trigger.
Dont see that error before the insert statement either.

Shouldnt I see it in there ?
Should dba_errors have it ?

I see the 04092 and 01403 errors returned in sqlplus but not in trace. No PARSE ERROR blah blah

turned on tracing using
alter session set timed_statistics=TRUE;
alter session set events '10046 trace name context forever, level 12';