How to catch and handle ORA-02292

Your trigger can be provided with an EXCEPTION SECTION to handle the exception

EXCEPTION
   WHEN OTHERS
   THEN
   DECLARE
      error_code NUMBER := SQLCODE;
   BEGIN
        IF error_code = -2292
        THEN
         ....
        END IF;
    END;

Thank you!!
 going to try thi scode

s.

This doesn't help ..
Still get this ORA-02292, child key found...

s.

You get the code, but can bypass it putting NULL in the IF statement

Yes, I wrote:

if error_code = -2292 then
null;
end if;

and still don't let me update ACTION's table

s.

Do you have a BEFORE trigger?

CREATE OR REPLACE TRIGGER removeChildsOnUpdate
BEFORE UPDATE ON T_ACTION
   if :new.CODE_ID <> :old.CODE_ID then
      DELETE FROM T_CODE WHERE CODE_ID = :old.CODE_ID
   end if;

I tryed both - before and after

s.

my code, witch raise ORA-02292 and don;t handle an exception:

after update or delete on T_ACTION

declare

e_no_delete exception;
pragma exception_init(e_no_delete,-02292);

begin

if deleting then
  delete from T_CODE where CODE_ID = :old.CODE_ID;
end if;

if updating then
  delete from T_CODE where CODE_ID = :old.CODE_ID;
end if;

EXCEPTION
when e_no_delete then
NULL;
end;

s.

My dear friend,
your parent record will be never updated or deleted before you
delete all child records pointing to the parent record.
This is the rule.
If you want to do this then in the IF statement
in the exception section you can delete all
child records pointing to the particular parent record.
After that in the exception section you can continue
to delete/update the parent record.

so what would be code for solution if this problem?
I only want to delete parent if there are no related record, but if there are - I don't want to delete, only to continue update child table and leave all CODE_ID in T_CODE.

could you please help?
s.

comenting:
schwertner advice - in the exception section you can delete all
child records pointing to the particular parent record.

actually I don't want to delete them, only want to leave everything and not delete parent :((

s.

add the following logic in your code

select code_id from T_CODE where :new.CODE_ID = CODE_ID
if it return something, do nothing more (end trigger)

TO DO THIS
set variable CHECK_CODE to something
do the select to return value in the CHECK_CODE variable
if value of CHECK_CODE is changed
   child exists then do nothing
else
 proceed to verify if it's a delete or update

But I should select from T_ACTION where CODE_ID = :old.CODE_ID and if it returns something, I should not to delete.
But then I get error message: table is mutating ...
I am updating and selecting from the same table in one time and data is not correct

s.

NOTE: on previous post (change T_CODE for T_ACTION)

You also can try this

begin

if deleting then
  delete from T_CODE where CODE_ID = :old.CODE_ID and CODE_ID not in (select CODE_ID from T_ACTION);
end if;

if updating then
  delete from T_CODE where CODE_ID = :old.CODE_ID and CODE_ID not in (select CODE_ID from T_ACTION);;
end if;

ok, going to try this

s.

The error happens before your trigger starts

There is a couple of ways to solve the problem with deleting in master table

1) create your foreign key as follows

alter table T_code  add constraint T_code_FK foreign key (code_id)
  references T_action (code_id) on delete cascade;


2) use:
    delete M_table cascade where <your condition>


According to UPDATING master:

I see the only posibility and it is to create an "instead of update" trigger

Sorry forget about

"According to UPDATING master: I see the only posibility and it is to create an "instead of update" trigger"

I created a constraint ( not on T_CODE but T_ACTION, because T_ACTION is child table in my DB structure), also added a constraint, but can't on delete cascade, becaus then it will delete all actions where I used this code_id, and it would be not god.
Actualy I don't want record would be deleted from T_CODE is there are related record in T_ACTION, but I want that T_ACTION was updated.

But now, if I attemp to delete record from parent table and there is some child table - my update fails.....

I enter data dyrectly to table, and now have no possibilities to create and use view from these two table... so instead of update trigger not useful for me :(

don't know what to do ...

s.

I am having difficulties understanding what you want to do.
Therefore I formulate it my way what I suspect you want to do:

If you delete an entry in T_ACTION, you want to automatically delete all entries in T_CODE that reference that item.
For this I would not use a trigger, but declare the constraint to cascade:

ALTER TABLE T_CODE ADD CONSTRAINT
      FK_T_CODE_T_ACTION FOREIGN KEY
      (
      CODE_ID
      ) REFERENCES T_ACTION
      (
      CODE_ID
      ) ON DELETE CASCADE;

If you change the CODE_ID of an entry in T_ACTION, you want to delete all entries in T_CODE with that CODE_ID.
This should work with a trigger like this:

CREATE OR REPLACE TRIGGER removeChildsOnUpdate
BEFORE UPDATE ON T_ACTION
BEGIN
   if :new.CODE_ID <> :old.CODE_ID then
      DELETE FROM T_CODE WHERE CODE_ID = :old.CODE_ID;
   end if;
END;

the foreign key with ON DELETE CASCADE you should create on your DETAIL table not on the MASTER one

to Nievergelt:

Actually no...

T_CODE is parent table and T_ACTION is child table

so I create FK_ACTION_CODE

ALTER TABLE T_ACTION ADD CONSTRAINT
     FK_T_ACTION_T_CODE FOREIGN KEY
     (
     CODE_ID
     ) REFERENCES T_CODE
     (
     CODE_ID
     )
but can't add on delete cascate, because I don't whant all actions would be deleted with this old code.

also creates trigger:

CREATE OR REPLACE TRIGGER removeParentOnUpdateIfItIsNotUsedInActions
BEFORE UPDATE ON T_ACTION
BEGIN
   if :new.CODE_ID <> :old.CODE_ID then
      DELETE FROM T_CODE WHERE CODE_ID = :old.CODE_ID;
   end if;
END;
 and it works!!!

but If there is related record I got an error ORA - 02292: constraint(FK_ACTION_CODE) violated - child key found.
That is ok, parent record is not deleted (as i need), but T_ACTION is not updated also (that is what I don't need)

so in conclution - only want to catch when this exception (ORA-02292) occurs.....
and then do only update but not delete from parent table

s.

YOU CREATED FK_T_ACTION_T_CODE not FK_ACTION_CODE
the FK_ACTION_CODE stays in its previous state

you should remove FK_ACTION_CODE

Yepp.... I removed, my is correct ...
but trigger works bad either :(

s.

What trigger works bad? You said on-update works perfectly. You do not need any on-delete trigger the deleting is executed automatically by your FOREIGN KEY

The goal as I understand is this

if an action is deleted, then delete the code_id associated with that action, unless there are other actions associated with the same code.

the problem with cascading deletes is that we are initiating the delete on a child record, not the parent.


The child table is T_ACTION
The parent is       T_CODE


SQL> select * from t_action;

 ACTION_ID    CODE_ID
---------- ----------
      2002          2
      2003          3
      2004          4
      2005          5
      2006          6
      2007          7
      2008          8
      2009          9
      2010         10
      2011         11
      2012         11
SQL> select * from t_code;

   CODE_ID
----------
         2
         3
         4
         5
         6
         7
         8
         9
        10
        11

 so if we delete action 2010, then we should delete code_id 10 from the T_CODE Table

but if we delete action 2011, we don't want to delete code_id 11 since Action 2012 references it as well.

I think you probably will have to manage that outside of a trigger. So after the delete of the action, then check to see if any other actions are using the same code_id and if not then delete the entry in T_CODE


create or replace function delete_action(action in number) return boolean
as

cnt int := 0;
old_code int;

begin
select code_id into old_code from t_action where action_id = action;

delete from t_action where action_id = action;
commit;
select count(*) into cnt from t_action where code_id = old_code;

if cnt = 0 then
   delete from t_code where code_id = old_code;
   dbms_output.put_line('Deleted Code '||old_code);
end if;

return true;

exception
             when others then
                     return false;
end;

tim

I need foreign key with no ON DELETE CASCADE, because
I don't need an automatical delete from T_ACTION.
These records are important and I can't delete them... simply - when I don't need to delete from T_CODE..

:)

s.

yes yes!!!! oratim is right! I post my last comment before I've read oratim's post!


s.

Hello,

So...
in after delete trigger I should write

if delete_action(:old.ACTION_ID) then
null;
end if;

??

s.

Hello dears :)

I solved my problem:
only needed to create statment trigger (not for each row)
after update or delete on T_ACTION

delete from T_CODE where CODE_ID not in (select CODE_ID from T_ACTION);

no exceptions, no mutating tables ...

Solveiga

Glad you Solved your problem,

My solution would require that instead of issuing a delete command for the action, you call the delete_action function.
No trigger would have been involved. If you put the code :

if delete_action(:old.ACTION_ID) then
null;
end if;

in a delete trigger, you would have a recursive situation and it would fail, since the function issues a delete command on the action table.

Sorry I didn't respond sooner, did not see you post until this AM.

Tim

SOLVE

That is what i suggested you in my post.

Yes, I've just wanted to accept your comment, but stopped, because now I see that this trigger deletes all CODEs witch are not used in T_ACTION, so I need anyway to create for each row trigger to have :old.CODE_ID from T_ACTION
that trigger would delete only this code related with updated action.
 
and if I write like you said:

delete from T_CODE where CODE_ID not in (select CODE_ID from T_ACTION) got error
table is mutating

s.

@peterside

your solution would not work because it references :old which is not available on a table level trigger, only a row level trigger.

thus, the table would be mutating, like solve says in his response to you.

so to Solve, I say, request a refund of your points, and save them for another day.

Or not, as you see fit :)

tim

I don't really cares for the points, my goal here is to help people.


I tought I helped you by telling you     "  not in (select CODE_ID from T_ACTION) "

ACtually, I could split my points.. I am no harpy...
But I still have a small hope that I will be lucky in solving my problem.
Thanks God, that I have time for this :)

s.

Everybody wins.

Points don't matter.

Lets be friends.

Take care.
Time to close this thread.

Good Luck All.

Tim

to peterside7

Yes are right 100%  - you helped me by telling "not in (select ...)

s.

And how could I refund my points?

This is question just for the future

s.

Hello,

I would like to share my practice according this question and how did I solved this problem.
1. I created a temporary table T_TEMP with one field (CODE_ID).
2. creates after update delete row trigger where
i insert into T_TEMP(CODE_ID) values (:old.CODE_ID)
3. creayed after update delete statment trigger where
i delete from T_CODE where CODE_ID in (select CODE_ID from T_TEMP) and not in (select CODE_ID from T_ACTION)

Best regards

Solveiga