How to create trigger prevent update if the value of foreign key not correct - Oracle

Question

I'm working in a java project using hibernate, when I select data from one table I got the following error : ORA-02291: integrity constraint violated - parent key not found when I made some analysis I found that hibernate make update before each select, and this update cause the problem because the foreign key in this update is not found in the parent table.

I can't make the correction in the app, so my idea is to make a trigger before update each row to prevent the update if the value of the foreign key is not exists in the parent table.

I'm using Oracle.

This is my code:

create or replace TRIGGER trg_prevent_update
BEFORE UPDATE  ON my_table FOR EACH ROW

BEGIN


--I'm not sure if this if will works
if :new.column_fk not in(select pk from parent_table) then

--do something to prevent update whithout generation exception to keep the app working

end if;


END;

Can anyone guide me to achieve my correction.

Regards!

Answer 1

Purpose of this "answer" is to demonstrate that:

1. trigger Barbaros suggested is useless, as foreign key constraint does that job itself.
2. disabling the foreign key constraint Ali performed is a big, HUGE no-no

I'm creating two "classic" master-detail tables, DEPT and EMP, where EMP.DEPTNO has a foreign key constraint pointing to DEPT.DEPTNO. Then, there's a trigger (more or less copy/paste of Barbaros' code).

SQL> create table dept
  2    (deptno  number   constraint pk_dept primary key,
  3     dname   varchar2(20));

Table created.

SQL> create table emp
  2    (empno   number   constraint pk_emp primary key,
  3     ename   varchar2(20),
  4     deptno  number   constraint fk_emp_dept references dept (deptno));

Table created.

SQL> create or replace trigger trg_prevent_update
  2    before insert or update on emp
  3    for each row
  4  declare
  5    v_cnt int;
  6  begin
  7    select count(*) into v_cnt
  8      from dept t
  9      where t.deptno = :new.deptno;
 10
 11    if v_cnt = 0 then
 12       raise_application_error(-20101, 'My own trigger prevents insert or update');
 13    end if;
 14  end;
 15  /

Trigger created.

SQL>

Now, testing.

SQL> -- Insert some valid data
SQL> insert into dept (deptno, dname) values (10, 'Accounting');

1 row created.

SQL> insert into emp (empno, ename, deptno) values (1, 'LF', 10);

1 row created.

1. Barbaros' suggestion:

SQL> -- Update EMP.DEPTNO to a non-existent value
SQL> update emp set deptno = 20;
update emp set deptno = 20
       *
ERROR at line 1:
ORA-20101: My own trigger prevents insert or update
ORA-06512: at "HR.TRG_PREVENT_UPDATE", line 9
ORA-04088: error during execution of trigger 'HR.TRG_PREVENT_UPDATE'


SQL>

OK, trigger prevented me to update EMP.DEPTNO to an invalid value. I'll now disable that trigger and repeat the same UPDATE statement.

SQL> -- Disable trigger
SQL> alter trigger trg_prevent_update disable;

Trigger altered.

SQL> -- Update EMP.DEPTNO to a non-existent value, again
SQL> update emp set deptno = 20;
update emp set deptno = 20
*
ERROR at line 1:
ORA-02291: integrity constraint (HR.FK_EMP_DEPT) violated - parent key not
found


SQL>

See? UPDATE failed due to foreign key constraint violation. So, what's the purpose of creating a trigger? It doesn't solve nor add anything.

2. Ali's disabled foreign key constraint

SQL> -- Disable the constraint
SQL> alter table emp disable constraint fk_emp_dept;

Table altered.

SQL> update emp set deptno = 20;

1 row updated.

SQL> insert into emp (empno, ename, deptno) values (2, 'Mickey', 666);

1 row created.

SQL> select * from dept;

    DEPTNO DNAME
---------- --------------------
        10 Accounting

SQL> select * From emp;

     EMPNO ENAME                    DEPTNO
---------- -------------------- ----------
         1 LF                           20
         2 Mickey                      666

SQL>

Now that's beautiful! Both UPDATE and INSERT went well, and now you have inconsistent data all over your tables.

Just to repeat myself:

You (Ali) should fix the issue in Java application which performs unnecessary INSERT that fails, and yes - it fails for a reason. Don't disable the constraint. You might deeply regret it, sooner than later.

Answer 2

To Perform prevention, you can use this trigger body :

CREATE or REPLACE TRIGGER trg_prevent_update
BEFORE UPDATE ON my_table FOR EACH ROW
DECLARE
     v_cnt int;
BEGIN
     select count(1) into v_cnt from parent_table t where t.code = :new.column_fk;   
  if ( v_cnt = 0 ) then
     raise_application_error(-20101,'the foreign key does not exist in the parent table!'); 
  end if;  
END;  

to warn you about problem before update, and prevents update operation.