Path: news.easynews.com!core-easynews!newsfeed1.easynews.com!easynews.com!easynews!small1.nntp.aus1.giganews.com!border1.nntp.aus1.giganews.com!nntp.giganews.com!cyclone-sf.pbi.net!216.218.192.242!news.he.net!news-hog.berkeley.edu!ucberkeley!newsfeed.stanford.edu!postnews1.google.com!not-for-mail
From: ListGroup@hotmail.com (Damjan)
Newsgroups: comp.databases.sybase,comp.databases.theory
Subject: Trigger Unlimited Recursion Error
Date: 27 Jun 2003 04:17:34 -0700
Organization: http://groups.google.com/
Lines: 43
Message-ID: <13d0aab8.0306270317.31ce79d8@posting.google.com>
NNTP-Posting-Host: 80.93.229.242
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit
X-Trace: posting.google.com 1056712654 13816 127.0.0.1 (27 Jun 2003 11:17:34 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: 27 Jun 2003 11:17:34 GMT
Xref: core-easynews comp.databases.sybase:27102 comp.databases.theory:27041
X-Received-Date: Fri, 27 Jun 2003 04:16:44 MST (news.easynews.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:
   a) if no inserted row then exit
     [if 0 = (select count(*) from inserted) then return   end if;]
   b) 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?
   c) then again in the insert you verify everything again
     [insert into FooFum_sync_state (TriggerTs, ThePk) select @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 (@TriggerTs, inserted.ThePk)
   d) 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