| Trigger to stop deletion of row [message #2337] |
Mon, 08 July 2002 07:39  |
Anna
Messages: 30
Registered: March 2001
|
Member |
|
|
I have a table with columns id and parent_id
I want a trigger that will stop the deletion of a row
if it's id is listed as a parent_id in any other row.
How would I do this?
|
|
|
|
| Re: Trigger to stop deletion of row [message #2339 is a reply to message #2337] |
Mon, 08 July 2002 08:05  |
K.SREENIVASAN
Messages: 110
Registered: January 2001
Location: banglore
|
Senior Member |
|
|
SIR,
CREATE OR REPLACE PROCEDURE TRIEXAMPLE
BEFORE DELETE ON TABLE1
ON EACH ROW
I NUMBER;
BEGIN
I:=0;
SELECT COUNT(*) INTO I FROM TABLE1 WHERE ID=
(SELECT A.ID FROM TABLE1 A,TABLE1 B WHERE A.ID=B.PARENT_ID AND A.ID=OLD.ID);
IF I>2 THEN
RAISE_APPLICATION_ERROR(200000,'CAN'T DELETE THE ROW');
END IF;
END;
/
K.SREENIVASAN
|
|
|
|
| Re: Trigger to stop deletion of row [message #2342 is a reply to message #2337] |
Mon, 08 July 2002 09:55 |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
|
To handle this in triggers and to avoid the mutating table error, you need to build a collection of three triggers - a before statement trigger, an after row trigger, and an after statement trigger. This requirement is best met (and easier handled) through a foreign key constraint. See my posting in the PL/SQL forum in response to your question.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
