UPDATE keyword

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

True, true... but the next time I need the code to fire I would need ot remember that I had set it to 1 and set it to something else...

Ian A. Harding
Programmer/Analyst II
Tacoma-Pierce County Health Department
(253) 798-3549
mailto: ianh@tpchd.org

Rene Pijlman <rpijlman@spamcop.net> 05/26/01 01:56AM >>>

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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

ianh@co.pierce.wa.us ("Ian Harding") schreef:

True, true... but the next time I need the code to fire I would
need ot remember that I had set it to 1 and set it to something else...

False, false, I've thought of that :-)

The statement says:

dummy = 1 - dummy

a) Initially, dummy == 0. Then dummy becomes 1 - 0 which equals 1.

b) So dummy == 1. Then dummy becomes 1 - 1 which equals 0.

c) Goto a.

Dummy changes value from 0->1 or 1->0 on every execution of the
statement.

If you don't believe it, you can also try dummy = -dummy, but then you
have to remember to initialize it to a non-zero value :-)

--
Vriendelijke groet,
Ren� Pijlman <rpijlman@spamcop.net>

Wat wil jij leren?
http://www.leren.nl/

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!