0

Consider the following example:

Create Table Test ([id] int, [value] varchar(max)) Go Create Table Test_Log ([row_id] int, [action] varchar(11), [timestamp] DateTime default GetDate(), [id] int, [value] varchar(max)) Go Create Trigger Tr_Update_Test on Test after Update AS Begin insert into Test_Log([row_id], [action], [id], [value]) select * from( (Select ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS [row_id], 'UPDATEFROM' as [action], [id], [value] From deleted) UNION (Select ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS [row_id], 'UPDATETO' as [action], [id], [value] From inserted) )AS [temp_table] ORDER BY [temp_table].[row_id], [temp_table].[action] End Go

I want to add row numbers to the log table to match the before and after updates. I don't want an expensive sort, I just want to order both inserted and deleted in the same order.

If I ran a multiple row update like update test set [value]='', I want the id pair to be consecutive in the log table, and if I run update test set [id]=0, I want the value pairs to be consecutive.

I want it to be in a generic way, to add auditing to a random table, and without sorting, or using some cursor loops, that would slow down the DB unnecessarily, just to keep the original order on both deleted and inserted lists.

The above example is working (usually?), but I have been told in this post that it is not guaranteed.

Is there any other recommended way to do it?

Improve this question

1 Answer 1

Reset to default
4

You need to order your row-number by the id, and use DENSE_RANK instead of ROW_NUMBER to get identical numbering, and you need to add the row-number after unioning, not before. Use UNION ALL as that is more efficient.

Note that inserted and deleted need to be correlated manually by some column. Therefore there is no way to avoid a sort, as the order of those pseudo-tables are not guaranteed.

Normally you would do this by id which would be the primary key. If you are updating the id column itself (a rather strange thing to do), then there is no way to correlate them, it's essentially a completely separate delete and insert.

CREATE OR ALTER TRIGGER Tr_Update_Test ON Test AFTER UPDATE AS SET NOCOUNT ON; INSERT INTO Test_Log (row_id, action, id, value) SELECT DENSE_RANK() OVER (ORDER BY t.id), t.[action], t.id, t.value FROM ( SELECT d.id, 'UPDATEFROM' as [action], d.value FROM deleted d UNION ALL SELECT d.id, 'UPDATETO' as [action], d.value FROM inserted i ) AS t;
Improve this answer
4
  • 1. I want to identify a row before and after by the same row_id. That is why I'm enumerating both deleted and inserted. 2. I understood that the answer is: it can't be done without sorting by the key column. 3. What about a change of a key column case? It won't work if sorted by it. Commented May 6 at 14:22
  • It's impossible unless you have some way of uniquely identifying a row. Either the primary key or a secondary unique key. Otherwise you can't correlate them. To be clear: do you want corresponding inserted and deleted rows to have identical row-numbers, or sequential? I only gave sequential. Commented May 6 at 15:02
  • I wanted it to be identical, to match the two rows Commented May 6 at 16:07
  • OK you need DENSE_RANK() OVER (ORDER BY t.id) instead Commented May 6 at 18:21

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.