trigger for calculated field

  • philrmiles

    SSC Veteran

    Points: 229

    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?

  • Andy Warren

    SSC Guru

    Points: 119684

    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 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply