CORRECTION

My mistake.

Some days I just struggle to tie my own shoes.

In my last blog post, I talked about how to create an audit trigger. And I made a mistake that I found badly when I tried to run an update.

Here's the issue.

SET @OldValue = (SELECT B FROM deleted);

SET @NewValue = (SELECT B FROM inserted);

this is great if there's exactly one row in the tables. This means that if you're updating only one row at a time, everything's dandy. But if you do an update that will impact more than one row, this will fail and crash hard.

How do you get around it?

Basically, you need to open a cursor for each row in the update tables, then step through the cursor one row at a time.

So in this case, first you would

build out a cursor

DECLARE cur_inserted CURSOR FOR

SELECT B, A FROM inserted

OPEN cur_inserted

FETCH NEXT FROM cur_inserted INTO @B, @A

WHILE @@FETCH_STATUS = 0

BEGIN

SET @B= (SELECT B FROM deleted WHERE A = @A);

INSERT INTO MyAudit (Value, AuditType)

Select B, 'OLD' from deleted WHERE A=@A; (assuming A is a unique key).

Basically, it does the same thing, but uses a cursor to walk through the modified rows, then pulls the corresponding values from deleted that correspond to the cursor row. In this case, it will pull the "old" values into the log table while walking through the "new" ones. Of course, you can still add conditional statements on this as before.

but this will actually work for mass updates.

Sorry for the confusion.