OraFAQ Forum: SQL & PL/SQL » Cascading Trigger

Home » SQL & PL/SQL » SQL & PL/SQL » Cascading Trigger

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Cascading Trigger [message #1920]

Tue, 11 June 2002 07:29

goku24
Messages: 2
Registered: June 2002

Junior Member

I have a trigger that should delete all children
of the deleted message in the same table. ex.
********************************************
create trigger bp_messages
before delete
for each row
begin
DELETE FROM BP_MESSAGES
WHERE :old.MESSAGEID =
BP_MESSAGES.PARENTMESSAGEID;
end;
*******************************************
When I delete a message I get a bp_messages is
mutating, trigger/function may not see it error.
Anyone have any ideas as to what I am doing wrong?

Thanks,
GKU

Report message to a moderator

Re: Cascading Trigger [message #1923 is a reply to message #1920]

Tue, 11 June 2002 09:15

Todd Barry
Messages: 4819
Registered: August 2001

Senior Member

You cannot delete from the same table in a row-level trigger. Basically, in the row-level trigger, you need to make an entry in an array (PL/SQL table) and then, in an after-statement trigger, loop through the array and delete the child rows.

See here for more info on this method:

http://osi.oracle.com/~tkyte/Mutate/index.html

Report message to a moderator

Re: Cascading Trigger [message #1945 is a reply to message #1920]

Wed, 12 June 2002 07:24

goku24
Messages: 2
Registered: June 2002

Junior Member

I think my situation is unique since I will be deleting/updating records from the same table. The parent and child records are all in one table, the messages table. Once a user updates a message as archived I want all the child messages to be updated as archived as well. I hope this makes sense.

Report message to a moderator

Previous Topic:	CLOB+test on NULL
Next Topic:	pl/sql

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 18:56:59 CDT 2024