Обсуждение: delete with referential integrity

i promise this will be my last post for a long time.  my last problem on my db is caused by a trigger.  i have three tables involved; assets, book_asset, and book.  because of integrity constraints, i have to delete the record from book_asset, then book, and finally assets.  all inserts, updates, and deletes are done through the assets table, and i have a trigger on assets that first deletes book_asset, then books, and finally assets.  the problem is when it deletes assets on that third delete, it sends the db into a recursive loop(the delete on assets starts the trigger again).  i am searching for an answer, but have not found one in the last hour and a half.  if someone has encounted this problem before and knows a solution, i would be very grateful.  the only thing i have found is  a function called funny_dup17(), but this seems to be only for regression tests.  besides from that, i am out of ideas.

thanks,

Mike Zouroudis
Intern
__________________________________________________
I.D.E.A.L. Technology Corporation - Orlando Office
http://www.idealcorp.com - 407.999.9870 x14

On Thu, Aug 29, 2002 at 12:54:23PM -0400, Michael Zouroudis wrote:
> i promise this will be my last post for a long time.  my last problem on
> my db is caused by a trigger.  i have three tables involved; assets,
> book_asset, and book.  because of integrity constraints, i have to
> delete the record from book_asset, then book, and finally assets.  all
> inserts, updates, and deletes are done through the assets table, and i
> have a trigger on assets that first deletes book_asset, then books, and
> finally assets.  the problem is when it deletes assets on that third
> delete, it sends the db into a recursive loop(the delete on assets
> starts the trigger again).  i am searching for an answer, but have not
> found one in the last hour and a half.  if someone has encounted this
> problem before and knows a solution, i would be very grateful.  the only
> thing i have found is  a function called _funny_dup17()_, but this seems
> to be only for regression tests.  besides from that, i am out of ideas.

You should be able to set you constraint to delete the entries in the
other tables automaticly instead of refusing. Then you don't need the
triggers.


--
Ragnar Kjørstad

Michael Zouroudis <mzouroudis@idealcorp.com> writes:
> because of integrity constraints, i have to
> delete the record from book_asset, then book, and finally assets.  all
> inserts, updates, and deletes are done through the assets table, and i
> have a trigger on assets that first deletes book_asset, then books, and
> finally assets.  the problem is when it deletes assets on that third
> delete, it sends the db into a recursive loop(the delete on assets
> starts the trigger again).

Why don't you simply let the system carry on with the deletion that the
trigger was called for?

Seems to me the answer to the problem "my trigger is infinitely
recursive" is "make your trigger not recurse".

            regards, tom lane