Discussion Locked

A trigger that affects only one field

0 helpful
1 reply
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Hello everyone, well i have a little problem, and goes like this.
There is a table which contains numeric values, those need to be calculated and modified from an external program, i cant modify that program since the sourcecode is lost, however i still have the problem of modifying it.

The value is the result of a mathematical operation. My question is, can a trigger do that job?

I mean, ive analized the situation, and being unable to modify the source code, i guess a trigger that takes place after insert for example, could do that job.

My idea or at least what my brain thinks could happen, is that right after the insert of a new record, the trigger will go to the specific row, and affect a specific column writting a new value, that value will be based on the values already present on the other columns.

But here the question is, can a trigger be that precise and affect only one field in a row?

Or maybe im hoping more from a trigger than it can actually do?

Thanks for any advice.

1 reply

by tony hopkinson

Create Table TempTable(
KeyValue int,
keyData int)

create Trigger Fred on TempTable for insert
as
declare @keyvalue int
select @keyvalue = (select keyvalue from inserted)
update Temptable set KeyData = KeyData * 2 Where KeyValue = @keyvalue

then to test
insert Temptable Values(1,1)
select * from TempTable

If you also wish to recalculate on update, you'll have to get a bit cleverer, and test to see if you need to recalculate, ie do the calculation and check against the value in teh inserted table, otherwise it would would go into infinite loop, sql server trys to detect that and throws an exception.

make the above for insert,update to test,
insert triggers update, update triggers update etc.

HtHs

This discussion is closed to new replies.