Trigger Unlimited Recursion Error

From: Damjan <ListGroup_at_hotmail.com>
Date: 27 Jun 2003 04:17:34 -0700
Message-ID: <13d0aab8.0306270317.31ce79d8_at_posting.google.com>

WOW!
I am in owe. Thanks a lot. Maybe I can send you my project :)

Anyway, I've got few comments (if you have time):

1. I've learned many things from your code.
2. What I've noticed is that you (and I assume on purpose) repeat yourself, so for the update trigger you say:
   1. if no inserted row then exit
      [if 0 = (select count(*) from inserted) then return end if;]
      
   2. then you say:Are there any rows that have not already been processed by the trigger
      [if 0 = (select count(*) from inserted where not exists (select
      1 from FooFum_sync_state where FooFum_sync_state.ThePk= inserted.ThePk) )then return end if;] But you already verified in a) that the inserted row exists, so why not do just this: if ((select count(*) from FooFum_sync_state where FooFum_sync_state.ThePk=inserted.ThePk) > 0) then return end if; to verify that that there are now rows in state table?
   3. then again in the insert you verify everything again
      [insert into FooFum_sync_state (TriggerTs, ThePk) select _at_TriggerTs,
      ThePk from inserted where not exists (select 1 from FooFum_sync_state where FooFum_sync_state.ThePk= inserted.ThePk );] Where I would do insert into FooFum_sync_state (TriggerTs, ThePk) VALUES (_at_TriggerTs, inserted.ThePk)
   4. even in update you do it with the FROM clause Are you just extra hugely safe? I've got to re-write a lot of sql queries to keep up with your standard!
3. The GLOBAL TEMPORARY TABLE is a good idea. I added a boolean column to both FUM and FOO. In trigger I check if there is a true(or any) value for that colum. - If yes it means it is called from trigger, so I do not do an update. - If no, then it is called from application so an update with a true (or any) value for the boolean column.

Thanks a lot once more,

Damjan Received on Fri Jun 27 2003 - 13:17:34 CEST