Thread

1. UPDATE keyword

   Ian Harding <ianh@healthdept.co.pierce.wa.us> — 2001-05-25T23:16:00Z

   Well, I am making progress in rewriting my MSSQL Server T-SQL triggers and stored procedures in Pl/Tcl.  However, I have run into a bit of an issue and I wonder if anyone knows the best way to address it...
   
   In T-SQL triggers, you can use the UPDATE keyword in conditional expressions as in 
   
   IF UPDATE(myfield)
   BEGIN
       do something
   END
   
   It detects the update of the field.  I have been comparing $NEW(myfield) and $OLD(myfield) which works ok, except where I used the UPDATE() test as a crutch.  I sometimes do an update like
   
   UPDATE mytable SET myfield = myfield WHERE...
   
   to fire an update trigger, and executing only the code in that trigger surrounded by UPDATE(myfield).
   
   Is there an easy way to duplicate this, or should I work around it?
   
   Ian A. Harding
   Programmer/Analyst II
   Tacoma-Pierce County Health Department
   (253) 798-3549
   mailto: ianh@tpchd.org
   
   
   

2. Re: UPDATE keyword

   Rene Pijlman <rpijlman@spamcop.net> — 2001-05-26T08:56:01Z

   ianh@healthdept.co.pierce.wa.us ("Ian Harding") schreef:
   >UPDATE mytable SET myfield = myfield WHERE...
   >to fire an update trigger, and executing only the code in that 
   >trigger surrounded by UPDATE(myfield).
   
   So you update a field by not changing its value, to execute a piece of
   code that has nothing to do with the field and the value :-)
   
   >Is there an easy way to duplicate this, or should I work around it?
   
   Isn't it easier (and more poratble) to add a dummy field to the table?
   Then you can do:
   
      UPDATE mytable
      SET dummy = 1 - dummy
      WHERE ...
   
   to execute the piece of code that responds to an update of 'dummy'.
   
   Regards,
   René Pijlman
   
   http://www.applinet.nl
   
   

3. Re: UPDATE keyword

   Will Trillich <will@serensoft.com> — 2001-06-02T09:11:12Z

   On Fri, May 25, 2001 at 04:16:00PM -0700, Ian Harding wrote:
   > Well, I am making progress in rewriting my MSSQL Server T-SQL triggers and stored procedures in Pl/Tcl.  However, I have run into a bit of an issue and I wonder if anyone knows the best way to address it...
   > 
   > In T-SQL triggers, you can use the UPDATE keyword in conditional expressions as in 
   > 
   > IF UPDATE(myfield)
   > BEGIN
   >     do something
   > END
   > 
   > It detects the update of the field.  I have been comparing
   > $NEW(myfield) and $OLD(myfield) which works ok, except where I
   > used the UPDATE() test as a crutch.  I sometimes do an update
   > like
   > 
   > UPDATE mytable SET myfield = myfield WHERE...
   > 
   > to fire an update trigger, and executing only the code in that trigger surrounded by UPDATE(myfield).
   > 
   > Is there an easy way to duplicate this, or should I work around it?
   
   how about
   
   	create view
   		relationname
   		as select * from _real_relation_name_;
   
   	create rule
   		on update to relationname
   		do instead (
   			update _real_relation_name_
   				set fld=new.fld,
   				col=new.col,
   				modified=current_timestamp,
   				differencefield=old.something-new.something,
   				whatever=some_function()
   			;
   			insert into _some_tracking_relation_
   				( field, list, here )
   				values
   				( ... )
   		);
   
   check out the manuals for 'create rule'. very handy stuff.
   
   -- 
   #95: We are waking up and linking to each other. We are watching. But
   we are not waiting.  -- www.cluetrain.com
   
   will@serensoft.com
   http://sourceforge.net/projects/newbiedoc -- we need your brain!
   http://www.dontUthink.com/ -- your brain needs us!