RE: Update trigger object (datetime stamping records)

Well other than upgrading (7.31 and probably 7.3 (Online versions anyway) allow this) So long as the original trigger isn't for an update of either of the two audit fields, you can: 1) Have the trigger call a stored procedure 2) The stored procedure calls a shell script (with an & so that it returns immediately) i.e. system "update_script.shl &" 3) The shell script does 'sleep 1' to allow the engine to release the lock 4) Then the shell script calls dbaccess to do the update Of coarse you'll have to pass the primary key to the shell script so that it knows what record to update. I told you, you wouldn't like it... Your method is probably better if it works. I seem to remember trying something like that on an old version and there were problems with locks but YMMV. Good luck. -----Original Message----- From: Ralph Hardy [mailto:ralphh@accessone.com] Sent: Thursday, April 27, 2000 4:05 PM Posted To: informix Conversation: Update trigger object (datetime stamping records) Subject: Re: Update trigger object (datetime stamping records) Defaults are a good idea for INSERTs - but need to handle UPDATEs. The only yet-untest-idea I had was really ugly - to insert a record into a table whose sole function is on its insert-trigger event to UPDATE the target table's datetime & user columns, then I'd have to get rid of this record. What's your method? Ralph "Scott Black" <sblack@elsouth.com> wrote in message news:8ea20f$sa6$1@news.xmission.com... > > Would it be possible instead to set the fields to 'default' to 'current > year to second' and 'user', rather than firing a trigger after the fact? > > If for some reason this is not feasible, I do have a method to trick > triggers into acting on the calling object, but you're not going to like > it. > > > > -----Original Message----- > From: Ralph Hardy [mailto:ralphh@accessone.com] > Sent: Thursday, April 27, 2000 1:58 PM > Posted To: informix > Conversation: Update trigger object (datetime stamping records) > Subject: Update trigger object (datetime stamping records) > > It seems to be impossible to update the trigger object using Version 7 > SE. > > I understand the desire to avoid infinitely nested firings of the update > trigger - but isn't there a way to work around this? > > We want to stamp the server time and user on each record posted. I can > avoid > the recurrence problem by using the WHEN condition and not doing the > update > if the time stamp is within a second of the CURRENT datetime. > > Has anybody done this? > > Thanks in advance, > > Ralph >

You're right, there is something I don't like about it - the thought of doing an update on a table of over 50,000 records ... really would have a time-performance impact ... but otherwise, it's a great idea! Thanks, Ralph "Scott Black" <sblack@elsouth.com> wrote in message news:8ea90t$20g$1@news.xmission.com... > > Well other than upgrading (7.31 and probably 7.3 (Online versions > anyway) allow this) > > So long as the original trigger isn't for an update of either of the two > audit fields, you can: > > 1) Have the trigger call a stored procedure > 2) The stored procedure calls a shell script (with an & so that it > returns immediately) i.e. system "update_script.shl &" > 3) The shell script does 'sleep 1' to allow the engine to release the > lock > 4) Then the shell script calls dbaccess to do the update > > Of coarse you'll have to pass the primary key to the shell script so > that it knows what record to update. > > I told you, you wouldn't like it... > > Your method is probably better if it works. I seem to remember trying > something like that on an old version and there were problems with locks > but YMMV. > > Good luck. > > > > -----Original Message----- > From: Ralph Hardy [mailto:ralphh@accessone.com] > Sent: Thursday, April 27, 2000 4:05 PM > Posted To: informix > Conversation: Update trigger object (datetime stamping records) > Subject: Re: Update trigger object (datetime stamping records) > > Defaults are a good idea for INSERTs - but need to handle UPDATEs. The > only > yet-untest-idea I had was really ugly - to insert a record into a table > whose sole function is on its insert-trigger event to UPDATE the target > table's datetime & user columns, then I'd have to get rid of this > record. > > What's your method? > > Ralph > > "Scott Black" <sblack@elsouth.com> wrote in message > news:8ea20f$sa6$1@news.xmission.com... > > > > Would it be possible instead to set the fields to 'default' to > 'current > > year to second' and 'user', rather than firing a trigger after the > fact? > > > > If for some reason this is not feasible, I do have a method to trick > > triggers into acting on the calling object, but you're not going to > like > > it. > > > > > > > > -----Original Message----- > > From: Ralph Hardy [mailto:ralphh@accessone.com] > > Sent: Thursday, April 27, 2000 1:58 PM > > Posted To: informix > > Conversation: Update trigger object (datetime stamping records) > > Subject: Update trigger object (datetime stamping records) > > > > It seems to be impossible to update the trigger object using Version 7 > > SE. > > > > I understand the desire to avoid infinitely nested firings of the > update > > trigger - but isn't there a way to work around this? > > > > We want to stamp the server time and user on each record posted. I can > > avoid > > the recurrence problem by using the WHEN condition and not doing the > > update > > if the time stamp is within a second of the CURRENT datetime. > > > > Has anybody done this? > > > > Thanks in advance, > > > > Ralph > > >