June 10, 2003 at 5:05 am
I have an UPDATE TRIGGER
CREATE TRIGGER trg_update ON [dbo].[myTable]
FOR UPDATE
I use If Update(column_name) to determine if the column that I am interested in has changed.
This works fine for a single row update, but if someone issues an
UPDATE myTable set column_name = value where primary_key in (##, ##, ##)
The trigger still fires, but I only get access to the last row affected from INSERTED and DELETED.
What I have done is to create a temporary table #TEMP_TRG and have selected the rows from INSERTED AND DELETED into this table and then use a CURSOR to iterate through the rows affected. I can no longer use the
IF Update(column_name) with this approach and have to determine the changed data on my own.
I can not control the other developer with the UPDATE statement. I have used Oracle before which does support row level triggers.
Is there an easier way to solve this problem? I have searched a significant number of user groups looking for what I believe is a common occurance.
Thanks.
June 10, 2003 at 5:12 am
Not really. Set based triggers are nice for performance, until you need to do something like you have here.
Andy
June 10, 2003 at 7:07 am
I'm confused, or more likely misreading the problem. Won't "IF Update(column_name)" be either true for all rows affected in a set-based update, or false for all rows?
Cheers,
- Mark
June 10, 2003 at 8:47 am
quote:
I'm confused, or more likely misreading the problem. Won't "IF Update(column_name)" be either true for all rows affected in a set-based update, or false for all rows?
I think you are misreading it.
The Inserted table and Deleted table contains ALL rows affected by the update.
When you use IF update(column_name) it does return a status that the column was updated, but if there are 20 rows affected, how do you iterate through the Inserted and Deleted tables efficiently?
My example above is how I solved that and was looking to see if there was an alternative way to accomplish this without using a temporary table (using Inserted and Deleted).
June 11, 2003 at 1:43 am
We had exactly that problem. My way round was to create an index field and a "done" field in the table, and use a variable @index. When the trigger fires, get it to select @index as the MIN index field where "done" is 'N', use a WHILE @index is not null, and at the end of your sql add a script to update the "done" field to 'Y' and re-define @index. This will loop until there are no rows left where "done" is 'N' and then @index will be null.
June 11, 2003 at 8:11 am
I believe you could simply do something like
IF NOT UPDATE(col_one) RETURN
--if one of the rows was updated then use something like...
SELECT *
FROM inserted i, deleted d
WHERE i.id = d.id
AND IsNull(i.col_one, 'blah') != IsNull(d.col_one, 'blah')
I'm not certain what you are doing with rows that change but if you can't do set based code you could use the above in a cursor....
Jeff
June 11, 2003 at 8:51 am
Thanks everyone.
I will work with what we have. I appreciate everybodys input.
--Joe
June 12, 2003 at 1:50 am
In SQL Server you have to switch thinking from cursor to set oriented actions. Virtual table [inserted] contains what is in the table after event, [deleted] contains data that was there before.
If a record exists in [inserted] but not [deleted] it was inserted.
If it exists in [deleted] but not [inserted], it was deleted.
If it exists in both, it was updated. So you can easily compare the values of a particular column when you INNER or OUTER JOIN [inserted] and [deleted].
If your concern is to selectively prevent certain columns from being updated, I have an elegant soluton for that too: BINARY_CHECKSUM
Using cursors in trigger is really bad practice.
Just start thinking "set oriented". 😎
One morething: use @table instead of #table in trigger if you have to, there is less overhead.
quote:
I have an UPDATE TRIGGERCREATE TRIGGER trg_update ON [dbo].[myTable]
FOR UPDATE
I use If Update(column_name) to determine if the column that I am interested in has changed.
This works fine for a single row update, but if someone issues an
UPDATE myTable set column_name = value where primary_key in (##, ##, ##)
The trigger still fires, but I only get access to the last row affected from INSERTED and DELETED.
What I have done is to create a temporary table #TEMP_TRG and have selected the rows from INSERTED AND DELETED into this table and then use a CURSOR to iterate through the rows affected. I can no longer use the
IF Update(column_name) with this approach and have to determine the changed data on my own.
I can not control the other developer with the UPDATE statement. I have used Oracle before which does support row level triggers.
Is there an easier way to solve this problem? I have searched a significant number of user groups looking for what I believe is a common occurance.
Thanks.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply