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
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
i knew how decide error with constraint, but i need only print another message — VasyPupkin
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
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 - Reinstate Monica

Aleksej Aleksej · Accepted Answer · 2016-08-19T10:38:16

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.

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

3 Answers