Trigger / Table mutating

I created two tables, a parent and a child. The parent should at least have one child. So if I delete the last child, the parent has also to be deleted. For this I use a trigger:

CREATE TRIGGER child_delete
  AFTER DELETE ON child
  FOR EACH ROW
DECLARE
  NUMBER x;
BEGIN
  SELECT Count(*) INTO x
    FROM child
    WHERE child.parent_id = :old.parent_id;   IF x = 0 THEN
    DELETE FROM parent
      WHERE parent.parent_id = :old.parent_id;   END IF;
END; If I try to delete a row in the child-table, the following error message occurs:

ORA-04091: table SCOTT.child is mutating, trigger/function may not see it

The only solution in the docs is to use a copy of the child-table and to alter this by the row-trigger.
After the execution of the delete-statement, a table-trigger has to write the child-copy into the child-original.

But I can't believe that this is the best solution!

Any suggestions or explanations are welcome.

Thanks

   Stefan Received on Wed Nov 04 1998 - 03:51:55 CST