October 10, 2001 at 8:10 pm
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 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?
October 11, 2001 at 6:58 am
The first thing I see is that you're updating ALL the records, you need to join/reference the inserted table to qualify the update. Something like this:
update table t set t.a=(b+c+d+e) from table t inner join inserted i on t.pkey=i.pkey
or
update table t set t.a=(b+c+d+e) from table t where t.pkey in (select pkey from inserted)
Both do the same thing, query plan should be pretty much the same as well. I prefer the latter syntax for deletes.
Im not a big fan of the updated() syntax (though yes, I did write an article posted here about it!). Why not just update your total each time the row is modified? It's not a very expensive operation.
Andy
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply