February 8, 2005 at 12:04 pm
Is there a way to write an update trigger to check if certain value of the column changes from 0 to null? If any other changes on the column, I would not like to track. It's a heavy transactional table.
Thanks for your help in advance.
HT
February 8, 2005 at 12:11 pm
You'd use UDPATE(ColumnName) to first determine if the column changed. Then a correlated query between the inserted & deleted tables to compare curr/prev values
If Update(YourColumn)
Begin
If Exists (
Select *
From inserted as i
Where YourColumn Is Null -- Changed TO Null
And Exists (
Select * From deleted as d
Where d.KeyColumn = i.KeyColumn
And d.YourColumn = 0 -- FROM zero
)
)
Begin
-- Track your changes here
End
End
February 8, 2005 at 1:03 pm
I've come up with a situation like this today and I found out that the first exists statement was using almost as much resource than the update statement that was ran after it (not tested with a lot of data and checked mainly the execution plan so I might be wrong on this (too little data to see any real difference in the profiler)).
Maybe you could try something like this :
if update(YourColumn)
begin
--assuming you want to log changes in another table
Insert into YourLogTable (Col1, col2...)
(Select Col1, Col2 from Inserted I inner join Deleted D on I.KeyCol = D.Keycol and I.YourColumn is null and D.YourColumn = 0)
end
However I must admit that my exists query was much more complexe than this one... I think it's worth testing both my and PW's solutions.
February 8, 2005 at 1:03 pm
Thanks for your help.
sincerely,
Hemant Trivedi
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply