UPDATE TRIGGER

Question

Post reply

UPDATE TRIGGER

sackerdj

SSC Eights!

Points: 815
More actions
March 5, 2002 at 11:29 am

#78476

I have written an update trigger and using the UPDATE() function to check on approximately 30 fields of the table. the problem i am facing is that on updating one field in the table the UPDATE() for the other fields are also fired. This causes the input old value set to null and the new value set to the current value of the field even though there is no change. Please advice

Viewing 4 posts - 1 through 3 (of 3 total)

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

Steve Jones - SSC Editor SSC Guru Points: 728164 More actions · Answer 1

can you post your code? I am not sure what you are describing?

Try this:

create table MyTable

(MyPK int

, MyID int

, MyChar char(4)

)

go

create trigger MyTable_Update on MyTable for Update

as

if Update(MyID)

select 'My ID Updated'

, i.MyID 'New'

, d.MyID 'Old'

from inserted i

inner join deleted d

on i.MyPK = d.MyPK

if Update(MyChar)

select 'My ID Updated'

, i.Mychar 'New'

, d.Mychar 'Old'

from inserted i

inner join deleted d

on i.MyPK = d.MyPK

return

go

insert MyTable select 1, 1, 'A'

insert MyTable select 2, 2, 'b'

select * from MyTable

update MyTable

set MyID = 10

where MyPK = 1

update MyTable

set MyChar = 'Z'

where MyPK = 2

select * from MyTable

update MyTable

set MyChar = 'Q'

, MyID = 11

where MyPK = 1

select * from MyTable

go

drop table MyTable

Steve Jones

steve@dkranch.net

Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com

Antares686 SSC Guru Points: 125444 More actions · Answer 2

Make sure you are seperating your logic for the fields to insure your not just firing on any update if you are checking each. Also make sure you tie to the inserted table and have a where clause. Otherwise please post your code so we can specifically help you.

"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

don1941 SSCarpal Tunnel Points: 4183 More actions · Answer 3

try something like this in your trigger:

set/select @MyColumn = IsNull(inserted.Column, deleted.Column)

So you get a not-null value. You can also use The IF UPDATE (column_name) clause and/or the IF COLUMNS_UPDATED() clause to determine which column(s) have been updated.