Home » SQL & PL/SQL » SQL & PL/SQL » Trigger problem with nested query (Oracle 9.2)
Trigger problem with nested query [message #317891] Sun, 04 May 2008 14:20 Go to next message
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 #317892 is a reply to message #317891] Sun, 04 May 2008 14:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
What is the problem with this code?

Tell us! Do you have any error or do you expect us we guess?

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.

Now, my crystal ball says your error is "mutating" one.
You can't select the table you're currently modifying.

Regards
Michel
Re: Trigger problem with nested query [message #317894 is a reply to message #317892] Sun, 04 May 2008 14:43 Go to previous messageGo to next message
Jakob Flygare
Messages: 6
Registered: May 2008
Junior Member
Apparently the problem is related to mutating.
I get ORA-04091.


I have tried PRAGMA AUTONOMOUS_TRANSACTION; ... COMMIT;
But the row was not deleted from order.

Re: Trigger problem with nested query [message #317898 is a reply to message #317894] Sun, 04 May 2008 14:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is the expected behaviour.
You have to understand what you are trying to do.
An autonomous transaction is another transaction and so doesn't see what you current one is doing.
NEVER EVER use autonomous transaction in trigger (but for logging purpose).

Regards
Michel
Re: Trigger problem with nested query [message #317900 is a reply to message #317894] Sun, 04 May 2008 15:33 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Although we aren't really supposed to fully answer the question, I guess that there've been quite a lot of "search and you'll find it" answers lately.

So, here's one way to do that - by using a PL/SQL (or INDEX-BY) table. We'll declare it in a package:
CREATE OR REPLACE PACKAGE pkg IS
   TYPE tip_tab_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
   tip_tab     tip_tab_type;
   tip_index   BINARY_INTEGER;
END pkg;

The next step is to create three triggers which will make it possible to delete a record in another table, depending on the result of a SELECT statement which reads currently affected table:
CREATE OR REPLACE TRIGGER TRG_01_BEF_STM
  BEFORE DELETE ON ordrespec
BEGIN
  pkg.tip_index := 0;
END;
/

CREATE OR REPLACE TRIGGER TRG_02_AFT_ROW
  AFTER DELETE ON ordrespec
  FOR EACH ROW
BEGIN
  pkg.tip_index := pkg.tip_index + 1;
  pkg.tip_tab(pkg.tip_index) := :OLD.ordrenr;
END;
/

CREATE OR REPLACE TRIGGER TRG_03_AFT_STM
  AFTER DELETE ON ordrespec
DECLARE
  cnt NUMBER;		
BEGIN
  FOR i IN 1 .. pkg.tip_index LOOP
    SELECT COUNT(*) INTO cnt 
      FROM ordrespec
      WHERE ordrenr = pkg.tip_tab(i);

    IF cnt = 0 THEN
       DELETE FROM ordre WHERE ordrenr = pkg.tip_tab(i);
    END IF;						
  END LOOP;
  pkg.tip_index := 0;
END;
/

If everyting's OK,
DELETE FROM ordrespec WHERE ordrenr = some_value;
should be successful and record(s) should be deleted from both tables.
Re: Trigger problem with nested query [message #317902 is a reply to message #317900] Sun, 04 May 2008 15:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Maybe the correct way to do it is not in a trigger but in a procedure and in multiple users environment the only safe way is to lock the master table.

Regards
Michel
Re: Trigger problem with nested query [message #317905 is a reply to message #317900] Sun, 04 May 2008 16:48 Go to previous messageGo to next message
Jakob Flygare
Messages: 6
Registered: May 2008
Junior Member
Many thanks!

Will deal with it with a package. Smile
Re: Trigger problem with nested query [message #317924 is a reply to message #317905] Mon, 05 May 2008 00:42 Go to previous messageGo to next message
Frank
Messages: 7880
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 Go to previous messageGo to next message
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).
Re: Trigger problem with nested query [message #318046 is a reply to message #318041] Mon, 05 May 2008 07:43 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
If you'd delete the record and catch the exception, there is no need to do a manual count of remaining children.
Re: Trigger problem with nested query [message #318052 is a reply to message #318041] Mon, 05 May 2008 08:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
This is what I want to do automatically

Then use a procedure to do the delete job.
Don't use trigger to do something "by magic".
Triggers are not made for this.
You badly use them.
You will have problems.

Regards
Michel
Re: Trigger problem with nested query [message #318389 is a reply to message #318052] Tue, 06 May 2008 14:19 Go to previous messageGo to next message
Jakob Flygare
Messages: 6
Registered: May 2008
Junior Member
There is nothing magic about dealing with a mutating table with packaged triggers Cool

Still cannot see what you mean Frank, but I have solved the problem with the solution presented by Littlefoot.
Re: Trigger problem with nested query [message #318391 is a reply to message #318389] Tue, 06 May 2008 14:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
There is nothing magic about dealing with a mutating table with packaged triggers

What is/seems magic is when you do something and other things happen behind the scene.
I want to delete a row and other things are changed. This is kind of magic. This is a very way to use Oracle or any other language.

You didn't solve the problem. This seems to work but it will not work unless you lock the tables before.

Regards
Michel
Re: Trigger problem with nested query [message #318393 is a reply to message #318391] Tue, 06 May 2008 14:35 Go to previous messageGo to next message
Jakob Flygare
Messages: 6
Registered: May 2008
Junior Member
That is the point of having an active database!
Re: Trigger problem with nested query [message #318400 is a reply to message #318393] Tue, 06 May 2008 14:56 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Jakob Flygare wrote on Tue, 06 May 2008 21:35
That is the point of having an active database!

What do you mean?

Regards
Michel

Previous Topic: Dynamically inserting records in output based on some conditions
Next Topic: How does google do pagination while showing # of results?
Goto Forum:
  


Current Time: Sun Dec 04 12:20:58 CST 2016

Total time taken to generate the page: 0.03983 seconds