PL/SQL Exception for Integrity Constraint Violation

[abhaysk]

Originally posted by hrishy
Hi

You acn have a work around..

create or replace trigger DEL_BINARY_CONTENT after delete on ATTRIBUTE_VALUE for each row
declare
child_exists exception;
pragma exception_init( child_exists, -2292 );
begin
delete from TABLE1 where oid = xxxx;
exception
when child_exists then
insert into test
values('Cannot delete BINARY_CONTENT as child exists')
WHEN others THEN
null;
end;

then you can query the test table and capture all your exacptions

regards
Hrishy

Hrishy:

U can perform an insert in trigger but it dosent allow u to commit
it throws an error....

SQL> create or replace trigger test before alter on schema
  2  begin
  3  insert into scott.test(created_by) values('hell');
  4  commit;
  5   end;
  6  /

Trigger created.

SQL> alter table test1 modify id4 number;
alter table test1 modify id4 number
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-04092: cannot COMMIT in a trigger
ORA-06512: at line 3


SQL>

and also the code in ur trigger theres no commit and obivously in the script they wud have set WHENEVER ERROR ROLLBACK command...and aagain u r in fix........

work around wud be to call the PROC in the trigger....and in PROC u can have commit ...

Abhay.