Constraint violation error while executing before update trigger

Question

i am experimenting with triggers using the sample HR schema provided by oracle. I am trying to delete or update all the employees whenever their respective department id is updated or deleted in the DEPARTMENTS TABLE using a trigger.

This is the code:

create or replace trigger UPDATE_EMPLOYEES_DEPT_ID
before update or delete of DEPARTMENT_ID on DEPARTMENTS
for each row
begin
  if UPDATING then
   update EMPLOYEES set DEPARTMENT_ID = :new.DEPARTMENT_ID where DEPARTMENT_ID = :OLD.DEPARTMENT_ID;
  ELSIF DELETING then
   update employees set department_id = null where DEPARTMENT_ID = :OLD.DEPARTMENT_ID;
  end if;

end;

When i execute :

UPDATE departments SET department_ID = 112 WHERE department_ID = 110;

it gives me constraint violation error.

Error report:
SQL Error: ORA-02292: integrity constraint (HR.JHIST_DEPT_FK) violated - child record found
02292. 00000 - "integrity constraint (%s.%s) violated - child record found"
*Cause:    attempted to delete a parent key value that had a foreign
       dependency.
*Action:   delete dependencies first then parent or disable constraint.

Where am i going wrong? Does integrity constraint gets checked before the 'BEFORE TRIGGER'?

Updating primary key is a very bad idea and should not be done. Error is obvious as you are trying to update a key which is referred in other tables (foreign key constraint). — Abhijith Nagarajan, Dec 02 '13 at 11:06
@Neal if you just want to propagate deletion, use ON DELETE UPDATE on the Foreign Key's definition — mcalmeida, Dec 02 '13 at 12:20
@Neal what about if you want to have your former data be shown? By editing key information you lose your references. You wont be able to show former data anymore unless you store it in some other table, like a history table or something like that. For example, you want to know which departments your employee belonged to in the past. But its just a thought of mine, maybe its not needed in your case — Dom84, Dec 02 '13 at 12:22

score 1 · Answer 1 · edited May 23 '17 at 12:28

Updating a primary key is probably not a good idea.. If you absolutely have to do it, I would advise against using triggers. Having business rules in triggers tend to lead to difficult to maintain applications, because the code is hidden/fragmented and it makes standard DML look like magic (unexpected side effects).

Having said that though, it appears that in your case your error comes from a foreign key from another table (probably JOB_HISTORY?) and your code logic should work in most cases, provided you update all child records from all referencing tables.

Ceiling Gecko · Answer 2 · 2013-12-02T12:27:07.517

It's quite problematic to achieve what you want, but not impossible.

The deal here is that you must deal with any possible constraints before setting the key to null.

I believe we need to use a little dynamic SQL here if we want to achieve a solution.

Here's the modified version of your trigger I quickly adjusted (Note: I haven't tested it):

create or replace trigger UPDATE_EMPLOYEES_DEPT_ID
before update or delete of DEPARTMENT_ID on DEPARTMENTS
 for each row
begin
  if UPDATING then
   update EMPLOYEES set DEPARTMENT_ID = :new.DEPARTMENT_ID where DEPARTMENT_ID = :OLD.DEPARTMENT_ID;
  elsif DELETING then
   for i in ( select CONSTRAINT_NAME from USER_CONSTRAINTS where CONSTRAINT_TYPE='R' and R_CONSTRAINT_NAME in (select CONSTRAINT_NAME from USER_CONSTRAINTS where CONSTRAINT_TYPE in ('P','U') and TABLE_NAME='EMPLOYEES') )
    loop
     for j in ( select TABLE_NAME, COLUMN_NAME from USER_CONS_COLUMNS where CONSTRAINT_NAME = i.CONSTRAINT_NAME )
      loop
       update j.TABLE_NAME set j.COLUMN_NAME = null where j.COLUMN_NAME = :OLD.DEPARTMENT_ID;
      end loop;
    end loop;
   update EMPLOYEES set DEPARTMENT_ID = null where DEPARTMENT_ID = :OLD.DEPARTMENT_ID;
  end if;
end;

Let me explain a little how it works regarding the delete:

First it grabs all the foreign keys referencing the table 'EMPLOYEES'
Then for each foreign key found it grabs the table name and column it references
Then for each table-column pair it nulls the values of those fields which match the :OLD.DEPARTMENT_ID
Finally after dealing with all the foreign keys it nulls the DEPARTMENT_ID in the EMPLOYEES table as you had planned initially.

As I haven't really tested this, let me know if this causes any problems.

Fortunately this won't work! You really don't want to update child records blindly. Also this is not how dynamic SQL works. — Vincent Malgrat, Dec 02 '13 at 12:21

score 0 · Answer 3 · edited Feb 20 '14 at 18:03

0

If you want to use trigger then you can remove the constraint,

I mean, perform all the operation of update delete by trigger only. and remove the constraint.
but doing so is not a good practice. So be sure with whatever you want to do.

edited Feb 20 '14 at 18:03

BenMorel

34,448
50
182
322

answered Dec 02 '13 at 12:23

dpk

641
1
5
15

Constraint violation error while executing before update trigger

3 Answers3