Home » SQL & PL/SQL » SQL & PL/SQL » Delete Cascade, Triggers and Mutating errors
Delete Cascade, Triggers and Mutating errors [message #346252] Sun, 07 September 2008 23:15 Go to next message
alibsd
Messages: 9
Registered: November 2007
Junior Member
Hi all,

The problem I have is with triggers on dependent tables that
updates parent tables. On the other hand, there is a
on delete cascade relationship between these two tables.

Whenever the main table is to be deleted, dependent tables are
deleted also, because of delete cascade, but this causes the
problem of Mutating tables.

Example: There is a table named "A" with a column named "nChilds"
that shows the number of rows in another table named "B". This
column is manipulated by triggers defined on table "B". Whenever
a new record is inserted or deleted in table "B", the triggers on
"B", updates the "nChilds" of their corresponding row in "A".
The problem arises when a row of "A" is to be deleted, since
triggers on "B" are run because rows of "B" are also being
deleted, and these triggers want to change "nChilds", Oracle
complains that "A" is mutating.

Is there any solution for these kind of problem? I know Packages
can solve this problem, but I want to find the best solution.
For example, if it is possible to find out in a trigger that
because of a "Cascade Delete" it is run or because of a normal
delete. If it is possible, then in trigger I can decide to not
update the parent table, if the trigger is run because of a
cascade delete.

Regards
Alireza


Re: Delete Cascade, Triggers and Mutating errors [message #346253 is a reply to message #346252] Sun, 07 September 2008 23:20 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & FOLLOW the Posting Guidelines as stated in URL above

I can not understand your long running narrative.
Too bad you decided not to post actual reproducible example.
Re: Delete Cascade, Triggers and Mutating errors [message #346260 is a reply to message #346252] Sun, 07 September 2008 23:54 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Quote:

I know Packages can solve this problem



First Search why Mutating Table Error Occurs.
And study the consequences that will occur if you don't
consider it.

Regards,
Rajat
Re: Delete Cascade, Triggers and Mutating errors [message #346263 is a reply to message #346252] Mon, 08 September 2008 00:17 Go to previous messageGo to next message
alibsd
Messages: 9
Registered: November 2007
Junior Member
Ok, sorry for my long running narrative.

The definition of tables:

SQL> desc tbl_oa_letter;
Name                     Null?    Type
------------------------ -------- ----------
PK_LETTER                NOT NULL NUMBER(12)
LETTER_ATTACHMENT_COUNT  NOT NULL NUMBER(2)


SQL> desc tbl_oa_letter_text
Name                     Null?    Type
------------------------ -------- ----------
PK_LETTER_TEXT           NOT NULL NUMBER(12)
FK_LTTR                  NOT NULL NUMBER(12)


Constraint on tbl_oa_lette_text:
ALTER TABLE TBL_OA_LETTER_TEXT ADD (
	CONSTRAINT FKC_LTXT_LTTR_2_LTTR_LETTER 
	FOREIGN KEY (FK_LTTR)
	REFERENCES TBL_OA_LETTER (PK_LETTER)
	ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED);


Defined Trigger on tbl_oa_letter_text:
CREATE OR REPLACE TRIGGER TRG_BIDR_LTXT_SET_LTTR_ATTACH
BEFORE INSERT OR DELETE ON TBL_OA_LETTER_TEXT FOR EACH ROW
BEGIN
	IF DBMS_REPUTIL.FROM_REMOTE = true THEN
		return;
	END IF;
	IF (inserting) THEN
		UPDATE tbl_oa_letter
		SET letter_attachment_count = letter_attachment_count + 1
		WHERE pk_letter = :new.fk_lttr;
	ELSIF (deleting) THEN
		UPDATE tbl_oa_letter
		SET letter_attachment_count = letter_attachment_count - 1
		WHERE pk_letter = :old.fk_lttr;
	END IF;
END;


Inserting new records into tables above:
INSERT INTO tbl_oa_letter VALUES (1, 0);
INSERT INTO tbl_oa_letter_text VALUES (1, 1);


Running the command below causes the mutating table error:
DELETE FROM tbl_oa_letter WHERE pk_letter = 1;


I hope it is clear enough.
Thanks

Re: Delete Cascade, Triggers and Mutating errors [message #346264 is a reply to message #346263] Mon, 08 September 2008 00:21 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
DELETE FROM tbl_oa_letter WHERE pk_letter = 1;


Oracle is doing it right way.
Your program logic is not ok.

Regards,
Rajat
Re: Delete Cascade, Triggers and Mutating errors [message #346266 is a reply to message #346252] Mon, 08 September 2008 00:37 Go to previous messageGo to next message
alibsd
Messages: 9
Registered: November 2007
Junior Member
You are right Rajat, but what is the solution for this kind
of problem? The LETTER_ATTACHMENT_COUNT is a redundant column,
because I do not want to join tbl_oa_letter and
tbl_oa_letter_text each time the information of letter is going
to be shown.

Can you suggest any other solution for this problem?



Re: Delete Cascade, Triggers and Mutating errors [message #346268 is a reply to message #346266] Mon, 08 September 2008 00:52 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
For Making It Work Don't Use Cascade.

But i don't think this is the better solution.

And joining tables is not a bad option in oracle.

Regards,
Rajat

Re: Delete Cascade, Triggers and Mutating errors [message #346270 is a reply to message #346266] Mon, 08 September 2008 00:56 Go to previous message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
Redundant data storage is not a good practice. What is wrong with joining the tables to get the count? You could use a view.
Previous Topic: problem solving mutating trigger
Next Topic: System hang
Goto Forum:
  


Current Time: Fri Dec 09 17:31:15 CST 2016

Total time taken to generate the page: 0.12590 seconds