Need help deciding if this kind of trigger is possible 1

[cbruch]

I have never created a trigger, but I have been asked to research if this is possible. I have a table (which I cannot change) containing records with the following fields:Item ID, Field ID, Field Value

Each Item ID can have multiple records. There will always be a record for Field ID 59, for Field ID 63, for Field ID 64, for Field ID 72, for Field ID 99, and there might be additional records with other Field IDs that I don't care about and that I wouldn't want to change.
If someone updates the Field Value on one or more of the 59, 63, 64, or 72 records for a specific Item ID, I need to recalculate the Field Value for its 99 record based on the values in the 59, 63, 64, and 72 records. Can I do this with a trigger? Or maybe the question is - Should I do this with a trigger?

 

[rdroske]

I assume you mean you just can't change the layout of the table because if you can't change it at all you can't add a trigger.

But assuming that and assuming you can't change whatever program users use to update these fields then a trigger is the only way you could do this.

It would be an UPDATE trigger created like this

CREATE TRIGGER TriggerName ON [TableName]
FOR UPDATE
AS

Your Logic goes here. Use the special table name "inserted" to access the new values going in (ie select * from inserted)

You'll have to be a little careful because it sounds like the action you are going to perform in the trigger will cause the trigger to fire again but it looks like you can avoid problems by enclosing all of the code in a IF statement testing the field ID.

 

[nikhilparchure]

Nice Solution!!!

But be careful of the recursive action thats gonna take place!!!

 

[cbruch]

rdroske-
Thanks for your help. I got it to work.