check cells for changes and recalculate

[philrm]

I am trying to set up a trigger on a table so it will update a single field with the sum of 4 other fields, if any of the 4 fields change. The system is Microsoft SQL 2000 with SP3 and Win 2000 with SP2. All the fields are beyond the 8th column. they are in column 26,28,30 and 32 and are called EXCA,EXMG,EXNA and EXK respectively. The following code makes the trigger but does not seeem to reference all the correct fields

CREATE TRIGGER fourcat
ON T_LAB_RESULT_STANDARD
for update AS
IF ((SUBSTRING(COLUMNS_UPDATED(),4,1)=power(2,(2-1)) + power(2,(4-1))
+ power(2,(6-1)) +power(2,(8-1))))
UPDATE T_LAB_RESULT_STANDARD
SET fourcat = (exk + exca + exmg + exna)

When it does find an update I get an error that the record is has been changed and then the log file fills up. I think the latter is due to the lack of a where clause but when I tried to use the inserted table as a reference for the updated field it says it is not a valid object name. I have used it in the past with no problems. What am I doing wrong?

Maybe there is an easyer way of doing this? If anyone can help I would be very happy!

 

[moonshadow]

See the CREATE TABLE page in BOL for computed columns. You will be able to create your table defining your fourcat column as being the sum of the other four. Alternatively you could define a view.