Trigger problem with nested query [message #317891] |
Sun, 04 May 2008 14:20  |
Jakob Flygare
Messages: 6 Registered: May 2008
|
Junior Member |
|
|
What is the problem with this code?:
CREATE OR REPLACE TRIGGER slet_tom_ordre_trg
AFTER DELETE ON ordrespec
REFERENCING OLD AS gammel
FOR EACH ROW
DECLARE x NUMBER;
BEGIN
SELECT COUNT(*) INTO x FROM ordrespec
WHERE ordrenr = :gammel.ordrenr;
IF x = 0
THEN
DELETE FROM ordre
WHERE ordrenr = (SELECT :gammel.ordrenr
FROM ordrespec
GROUP BY :gammel.ordrenr
HAVING COUNT(*) = 0);
END IF;
END;
Is nested queries not allowed in a trigger?
[Updated on: Sun, 04 May 2008 14:21] Report message to a moderator
|
|
|
|
|
|
|
|
|
Re: Trigger problem with nested query [message #317924 is a reply to message #317905] |
Mon, 05 May 2008 00:42   |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
If you choose to go this way, why not create a foreign key to the master table and try to delete the row in the master. Catch the exception that is thrown if there is still a detail present.
But I agree with Michel: to make this thing "work" in a multiuser env, you'd have to lock the master first. This might introduce the danger of deadlocks, so make sure you have a solid locking strategy (eg. always lock tables in the same order)
[Updated on: Mon, 05 May 2008 00:44] Report message to a moderator
|
|
|
Re: Trigger problem with nested query [message #318041 is a reply to message #317924] |
Mon, 05 May 2008 07:08   |
Jakob Flygare
Messages: 6 Registered: May 2008
|
Junior Member |
|
|
Hi Frank,
There is a foregin key on ordrespec (i.e., order specification)referencing the master table ordre (i.e., orders).
Cannot see how it would help to:
"create a foreign key to the master table and try to delete the row in the master. Catch the exception that is thrown if there is still a detail present."
This is what I want to do automatically: if there are no longer orderlines for a certain order in the order's specification (child table) the order shall be deleted in the order tabel (master table).
|
|
|
|
|
|
|
|
|