Thanks for the advice Helen and for picking me up on my incorrect syntax.

I think I have got something that works now.



My mistaken logic with nulls lead me to believe that new.* may contain a
null

value if the field had not been edited and this false assumption lead me to
writing

code with invalid logic.



Graeme Edwards





From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Helen Borrie
Sent: Wednesday, 15 February 2006 1:12 PM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] The New command in triggers



At 12:18 PM 15/02/2006, you wrote:

>
>
>I am currently having trouble developing a before update trigger to set a
>timestamp field in a record based
>
>on values in other fields.
>
>The question I need answered is
>
>What is stored in new.Field if no change has been made to Field?
>
>Is it null or is it the same as Old.Field?

The two variables will be same if nothing changed, different if something
did.

>My logic seems to break down when I assume that new.Field=old.Field
>indicates that no change has been made to the
>Field, and the possibility of Null in either new.Field or Old.Field seems

to

>complicate it more.
>I am using the current release 1.5.3 of firebird

Yes, because (something = null) is not a valid comparison.

By the way, NEW.* and OLD.* are not "commands". They are context
variables, i.e. containers for values which can be read. In the case
of the NEW.* variables, you can assign to them, too, under the right
conditions.

You don't say what you're doing with them but, if you are testing a
variable and there's a chance it is null, then you explicitly have to
test for null using

IF (
(NEW.BLAH IS NULL AND OLD.BLAH IS NOT NULL)
OR
(OLD.BLAH IS NULL AND NEW.BLAH IS NOT NULL)
) THEN

So, assuming you want to make some decision according to whether the
NEW and the OLD are the same, and the column is nullable, you'll have
to do three tests:

IF (
(NEW.BLAH IS NULL AND OLD.BLAH IS NOT NULL)
OR
(OLD.BLAH IS NULL AND NEW.BLAH IS NOT NULL)
OR
(NEW.BLAH <> OLD.BLAH)
) THEN

./hb



++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://firebird.sourceforge.net and click the Resources item
on the main (top) menu. Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++





SPONSORED LINKS


Technical
<http://groups.yahoo.com/gads?t=ms&k=Technical+support&w1=Technical+support&
w2=Computer+technical+support&w3=Compaq+computer+technical+support&w4=Compaq
+technical+support&w5=Hewlett+packard+technical+support&w6=Microsoft+technic
al+support&c=6&s=196&.sig=-XIO8GxY6hqd3NaD5WSEyw> support

Computer
<http://groups.yahoo.com/gads?t=ms&k=Computer+technical+support&w1=Technical
+support&w2=Computer+technical+support&w3=Compaq+computer+technical+support&
w4=Compaq+technical+support&w5=Hewlett+packard+technical+support&w6=Microsof
t+technical+support&c=6&s=196&.sig=B29J78SYXnNTjjMFBMznqA> technical
support

Compaq
<http://groups.yahoo.com/gads?t=ms&k=Compaq+computer+technical+support&w1=Te
chnical+support&w2=Computer+technical+support&w3=Compaq+computer+technical+s
upport&w4=Compaq+technical+support&w5=Hewlett+packard+technical+support&w6=M
icrosoft+technical+support&c=6&s=196&.sig=7_je1A94xs82CFXUjEqA6g> computer
technical support


Compaq
<http://groups.yahoo.com/gads?t=ms&k=Compaq+technical+support&w1=Technical+s
upport&w2=Computer+technical+support&w3=Compaq+computer+technical+support&w4
=Compaq+technical+support&w5=Hewlett+packard+technical+support&w6=Microsoft+
technical+support&c=6&s=196&.sig=2zMAuRCo5cJrVBr1Bxa3_w> technical support

Hewlett
<http://groups.yahoo.com/gads?t=ms&k=Hewlett+packard+technical+support&w1=Te
chnical+support&w2=Computer+technical+support&w3=Compaq+computer+technical+s
upport&w4=Compaq+technical+support&w5=Hewlett+packard+technical+support&w6=M
icrosoft+technical+support&c=6&s=196&.sig=_ytYU7aXb57AVaeUfmvLcA> packard
technical support

Microsoft
<http://groups.yahoo.com/gads?t=ms&k=Microsoft+technical+support&w1=Technica
l+support&w2=Computer+technical+support&w3=Compaq+computer+technical+support
&w4=Compaq+technical+support&w5=Hewlett+packard+technical+support&w6=Microso
ft+technical+support&c=6&s=196&.sig=4hRo6NXYavRAbTkaYec5Lw> technical
support



_____

YAHOO! GROUPS LINKS



* Visit your group "firebird-support
<http://groups.yahoo.com/group/firebird-support> " on the web.

* To unsubscribe from this group, send an email to:
firebird-support-unsubscribe@yahoogroups.com
<mailto:firebird-support-unsubscribe@yahoogroups.com?subject=Unsubscribe>

* Your use of Yahoo! Groups is subject to the Yahoo!
<http://docs.yahoo.com/info/terms/> Terms of Service.



_____



[Non-text portions of this message have been removed]