trigger to override the message to error(ORA-02292)

Question

I want override the message that generate error (ORA-02292). It is message like that

ORA-02292: integrity constraint (IVANKA.FK_SUPPLIER) violated - child record found

I want a trigger to override the above message to his example on this (MY override :))

I tried do like that

for first create table

CREATE TABLE supplier
( supplier_id numeric(10) not null,
  supplier_name varchar2(50) not null,
  contact_name varchar2(50),
  CONSTRAINT supplier_pk PRIMARY KEY (supplier_id)
);

CREATE TABLE products
( product_id numeric(10) not null,
  supplier_id numeric(10) not null,
  CONSTRAINT fk_supplier
    FOREIGN KEY (supplier_id)
    REFERENCES supplier (supplier_id)
);

then insert data

INSERT INTO supplier
(supplier_id, supplier_name, contact_name)
VALUES (1000, 'Microsoft', 'Bill Gates');

INSERT INTO products
(product_id, supplier_id)
VALUES (50000, 1000);

then do trigger

create or replace trigger  sup_z 
after delete on supplier
for each row
declare 
v_error_constraint exception;
  pragma exception_init(v_error_constraint, -2292);
begin
     null;
exception
      When v_error_constraint then
      raise_application_error(-20001, 
         'My ovvervide:)');
End;

then do delete to generate message

DELETE from supplier
WHERE supplier_id = 1000

but I see not my message in trigger I see

   ORA-02292: integrity constraint (IVANKA.FK_SUPPLIER) violated - child record found

Can you help me? What am I doing wrong?

The constraint violation is being thrown before the trigger fires. — Alex Poole, Aug 19 '16 at 10:30
Your proc is violating a parent child constraint, which violation probably would leave orphaned records lying around. You should either delete the children before deleting the parent record, or maybe drop the constraint. — Tim Biegeleisen, Aug 19 '16 at 10:30
i knew how decide error with constraint, but i need only print another message — VasyPupkin, Aug 19 '16 at 10:34
if you want to display a more meaningful message for an end user (that's the primary reason I think) you either put your DML statements inside a PL/SQL block with an exception section where you'll catch that exception, or create a database event trigger (would be an overkill, just pointing out the possibility). — Nick Krasnov, Aug 19 '16 at 10:44
I don't think you understand how triggers work. You cannot use a trigger to "intercept" an exception. And ignoring the exception won't do you any good because the database will still refuse to commit your changes. You don't have the option of ignoring constraint violations if you don't happen to like them - you have to write code which works properly given the constraints which exist in your database. Best of luck. — Bob Jarvis - Слава Україні, Aug 19 '16 at 13:44

Aleksej · Answer 1 · 2016-08-19T11:30:56.063

3

Your trigger is fired after the DELETE has been done, so it will never fire if the DELETE gives an error. You could use a BEFORE trigger to avoid the DELETE, if you find some child records; for example:

create or replace trigger  sup_z 
before delete on supplier
for each row
declare 
  vNumberOfChild number;
begin
     select count(1)
     into vNumberOfChild
     from products
     where  supplier_id = :old.supplier_id;
     --
     if vNumberOfChild > 0 then
        raise_application_error(-20001, 'My ovveride:)');
     end if;
End;

Another way could be defining a procedure to handle the delete:

SQL> create or replace procedure deleteSupplier(p_id in numeric) is
  2  vNumberOfChild number;
  3  begin
  4       select count(1)
  5       into vNumberOfChild
  6       from products
  7       where  supplier_id = p_id;
  8       --
  9       if vNumberOfChild > 0 then
 10          dbms_output.put_line('My ovveride');
 11       else
 12          delete supplier
 13          where supplier_id = p_id;
 14       end if;
 15  end;
 16  /

Procedure created.

SQL> set serveroutput on
SQL> exec deleteSupplier(1000);
My ovveride

PL/SQL procedure successfully completed.

This avoids running the delete checking the data before, so you need no triggers.

edited Aug 19 '16 at 11:30

answered Aug 19 '16 at 10:38

Aleksej

22,443
5
33
38

if i use your variant trigger. i see messeges ORA-20001: My ovveride:) ORA-06512: at "IVANKA.SUP_Z", line 10 ORA-04088: error during execution of trigger 'IVANKA.SUP_Z' 1. delete from supplier where supplier_id=1 (how see only my messege?) – VasyPupkin Aug 19 '16 at 10:57
What you see is the error stack; I just edited to show a different way you could use, but it depends on the way you want to call your delete – Aleksej Aug 19 '16 at 11:31
but i dont udertand how add your procedure in trigger ? – VasyPupkin Aug 19 '16 at 11:59
If you use the procedure, the trigger will be unuseful: the procedure does it all – Aleksej Aug 19 '16 at 12:28
i real need trigger that show my messege INSTEAD constraint error ?but when i youse your trigger is show three message like(ORA-20001: My ovveride:) ORA-06512: at "IVANKA.SUP_Z", line 10 ORA-04088: error during execution of trigger 'IVANKA.SUP_Z' 1), how fix it to see only my message in trigger,can you help? – VasyPupkin Aug 22 '16 at 08:35

score 0 · Answer 2 · edited May 23 '17 at 12:14

0

Check this out (Display custom message when constraint is violated PL/SQL). I think you want something similar.

edited May 23 '17 at 12:14

Community

1
1

answered Aug 19 '16 at 10:37

PKey

3,715
1
14
39

i do like you find create or replace trigger sup_z before delete on supplier for each row declare v_error_constraint exception; pragma exception_init(v_error_constraint, -2292); begin null; exception When v_error_constraint then raise_application_error(-20001, 'My ovvervide:)'); End; (but after this trigger i dot see message what i want),can help? – VasyPupkin Aug 19 '16 at 11:00

score 0 · Answer 3 · answered Aug 19 '16 at 14:12

When all you need is just the message to be printed, why do we need a trigger in the first place? Shouldn't we be handling this just with an exception alone?

declare 
    v_error_constraint exception;
    pragma exception_init(v_error_constraint, -2292);
begin
     DELETE from supplier
     WHERE supplier_id = 1000;
exception
      When v_error_constraint then
      raise_application_error(-20001, 
         'My ovvervide:)');
End;

trigger to override the message to error(ORA-02292)

3 Answers3