Mark
asked on
rigger does not appear to work on multi-row updates
memberEarnings-Update.txtI have several triggers (see attachment for example) that insert a row into a change log table when updates are made to the triggered table. This seems to work quite well for single row updates, e.g. when a use changes a column value via a screen form. However, if a program updates several rows at once, either via a single query that updates multiple rows, or via individual queries executed in a programatic 'for' loop, it seems that only one row gets inserted into the log table, not one for each update.
What's up with this? Do I have to put some kind of seeing on the table or database or is this not doable?
What's up with this? Do I have to put some kind of seeing on the table or database or is this not doable?
You must change your triggers to deal with batch operations and you should use INSERTED and DELETED tables for this matter than for multiple rows in any of these two internal tables write a batch operation - I recommend do NOT use cursors in triggers as they can have pretty big negative performance hit.
In the trigger you can use following code to see if there are multiple or single rows affected:
IF (SELECT COUNT(*) FROM INSERTED) > 1
--then you must write trigger action for batch operation
ELSE
--single row affected
Same thing for DELETED table on DELETE trigger action.
IF (SELECT COUNT(*) FROM INSERTED) > 1
--then you must write trigger action for batch operation
ELSE
--single row affected
Same thing for DELETED table on DELETE trigger action.
(Not for points --) In case it's not clear, there will be a temporary table named "INSERTED" when an INSERT trigger fires. The trigger can reference the rows in the table named INSERTED if the trigger needs to know that multiple rows were inserted. There will be one row in the INSERTED table for each row that was inserted. The format of the INSERTED table will always match whatever table the trigger is defined over.
For a DELETE trigger, there will be a temporary table named DELETED. It can be used pretty much the same way an INSERTED table would be used.
Basically, the trigger would put a row into the change log for every row in the INSERTED (or the DELETED) table.
Tom
For a DELETE trigger, there will be a temporary table named DELETED. It can be used pretty much the same way an INSERTED table would be used.
Basically, the trigger would put a row into the change log for every row in the INSERTED (or the DELETED) table.
Tom
ASKER
Here's a fragment of the code I have now:
This code assumes 1 change, as you all have mentioned. How would I transform this to loop through updates? What's the syntax for looping through updates, FETCH NEXT FROM INSERTED ... ? How would I retrieve the corresponding DELETED row?
if UPDATE(earningCode) begin
select @memberId = memberId from DELETED
select @oldVal = case when earningCode is null then '' else earningCode end from DELETED
select @recId = recId from DELETED
select @newVal = case when earningCode is null then '' else earningCode end from INSERTED
if @oldVal <> @newVal
begin
INSERT INTO changeLog (tableName,columnName,userName,changeTime,memberId,keyField,oldValue,newValue)
values ('memberEarnings','earningCode',@uname,getdate(),@memberId,@recId,@oldVal,@newVal)
end
end
This code assumes 1 change, as you all have mentioned. How would I transform this to loop through updates? What's the syntax for looping through updates, FETCH NEXT FROM INSERTED ... ? How would I retrieve the corresponding DELETED row?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
OOOPs..Missed the delete so it would be a endless loop - please add followinfg line just before the end -- loop:
delete from @loop_table where recid = @recid -- this is to removed processed row and skip to next
delete from @loop_table where recid = @recid -- this is to removed processed row and skip to next
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hmmm, these already messy solutions can get even messier with tables having more columns. I think I'll stick with the single row update in the trigger. If I want to log multiple updates, I'll change my program to loop through the table records.
ASKER
Thanks!