Hi Guys !

It's far discussed about "null" and comparasion with other values (I know,
null is a state and not a value...)...

but in a lot of triggers I need to do something like this (just pseudocode
and probably does not compile !)

create trigger tu_PurchaseItem for PurchaseItem after update as
begin
if (New.Quantity <> Old.Quantity) or
((New.Quantity is null) and (Old.Quantity is not null)) or
((New.Quantity is not null) and (Old.Quantity is null)) or
then
execute procedure PurchaseItemCalc(New.ProductID)

if (New.ProductID <> Old.ProductID) then begin
execute procedure PurchaseItemCalc(New.ProductID)
execute procedure PurchaseItemCalc(Old.ProductID)
end

end

I think a built in function "updated" will be handy in situations like
that, the above trigger will be:
create trigger tu_PurchaseItem for PurchaseItem after update as
begin
if Updated(Quantity) then
execute procedure PurchaseItemCalc(New.ProductID)

if Updated(ProductID) then begin
execute procedure PurchaseItemCalc(New.ProductID)
execute procedure PurchaseItemCalc(Old.ProductID)
end

end

I think this is more clean and error prone since I had more than once
forget about the null values.

if value from "new" and "old" are different then the column was updated, if
"old" or "new" is null and the other is not null than the values changes.

in delete or insert triggers this function can always return true.

Sorry if this has been already discussed, but I cannot remember about it
and I did a search in the history and a lot of messages was returned about
triggers, inserts, etc... I could not find anything like that...

What do you think about that ?



Alexandre Benson Smith
Development
THOR Software e Comercial Ltda.
Santo Andre - Sao Paulo - Brazil
www.thorsoftware.com.br

----------


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.514 / Virus Database: 312 - Release Date: 28/08/2003


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