March 5, 2002 at 11:29 am
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
March 5, 2002 at 11:42 am
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
March 5, 2002 at 3:16 pm
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)
December 8, 2002 at 8:36 pm
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.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply