Determine Old primary key in a SQL Trigger

Question

I've done this before somewhere I'm sure of it!

I have a SQL Server 2000 table that I need to log changes to fields on updates and inserts into a second Logging table. A simplified version of the structure I'm using is below:

MainTable
ID varchar(10) PRIMARY KEY
DESCRIPTION varchar(50)

LogTable
OLDID varchar(10)
NEWID varchar(10)

For any other field something like this would work great:

Select i.DESCRIPTION As New, d.DESCRIPTION As Old 
From Inserted i
LEFT JOIN Deleted d On i.ID=d.ID

...But obviously the join would fail if ID was changed.

I cannot modify the Tables in way, the only power I have in this database is to create a trigger.

Alternatively is there someone who can teach me time travelling and I'll go back into the past and ask myself back then how I did this? Cheers :)

---

Edit:

I think I need to clarify a few things here. This is not actually my database, it is a pre-existing system that I have almost no control of, other than writing this trigger.

My question is how can I retrieve the old primary key if said primary key was changed. I don't need to be told that I shouldn't change the primary key or about chasing up foreign keys etc. That's not my problem :)

Answer 1

DECLARE @OldKey int, @NewKey int;

SELECT @Oldkey = [ID] FROM DELETED;
SELECT @NewKey = [ID] FROM INSERTED;

This only works if you have a single row. Otherwise you have no "anchor" to link old and new rows. So check in your trigger for > 1 in INSERTED.

Answer 2

----new---- add an identity column to the table that the application can not change, you can then use that new column to join the inserted to the deleted tables within the trigger:

ALTER TABLE YourTableName ADD
    PrivateID int NOT NULL IDENTITY (1, 1)
GO

----old---- Don't ever update/change key values. How can you do this and fix all of your foreign keys?

I wouldn't recommend ever using a trigger that can't handle a set of rows.

If you must change the key, insert a new row with the proper new key and values, use SCOPE_IDENTITY() if that is what your are doing. Delete the old row. Log for the old row that it was changed to the new row's key, which you should now have. I hope there is no foreign key on the changed key in your log...

Answer 3

I don't think it's possible. Imagine if you have 4 rows in the table:

1  Val1
2  Val2
3  Val3
4  Val4

Now issue the following update:

UPDATE MainTable SET
ID = CASE ID WHEN 1 THEN 2 WHEN 2 THEN 1 ELSE ID END
Description = CASE ID WHEN 3 THEN 'Val4' WHEN 4 THEN 'Val3' ELSE Description END

Now, how are you going to distinguish between what happened to rows 1 & 2 and what happened to rows 3 & 4. And more importantly, can you describe what's different between them? All of the stuff that tells you which columns have been updated won't help you.

If it's possible in this case that there's an additional key on the table (e.g. Description is UNIQUE), and your update rules allow it, you could write the trigger to prevent simultaneous updates to both keys, and then you can use whichever key hasn't been updated to correlate the two tables.

Answer 4

If you must handle multiple-row inserts/updates, and there's no alternate key that's guaranteed not to change, the only way I can see to do this is to use an INSTEAD OF trigger. For example, in the trigger you could break the original insert/update command into one command per row, grabbing each old id before you insert/update.

Answer 5

Within triggers in SQL Server you have access to two tables: deleted and inserted. Both of these have already been mentioned. Here's how they function depending on what action the trigger is firing on:

INSERT OPERATION

• deleted - not used
• inserted - contains the new rows being added to the table

DELETE OPERATION

• deleted - contains the rows being removed from the table
• inserted - not used

UPDATE OPERATION

• deleted - contains the rows as they would exist before the UPDATE operation
• inserted - contains the rows as they would exist after the UPDATE operation

These function in every way like tables. Therefore, it is entirely possible to use a row based operation such as something like the following (Operation exists only on the audit table, as does DateChanged):

INSERT INTO MyAuditTable
(ID, FirstColumn, SecondColumn, ThirdColumn, Operation, DateChanged)
VALUES
SELECT ID, FirstColumn, SecondColumn, ThirdColumn, 'Update-Before', GETDATE()
FROM deleted
UNION ALL
SELECT ID, FirstColumn, SecondColumn, ThirdColumn, 'Update-After', GETDATE()
FROM inserted

Answer 6

Is it possible to assume that the INSERTED and DELETED tables presented to you in a trigger are guaranteed to be in the same order?

Answer 7

You can create a new identity column on table MainTable (named for example correlationid) and correlate inserted and deleted tables using this column. This new column should be transparent for existing code.

INSERT INTO LOG(OLDID, NEWID) SELECT deleted.id AS OLDID, inserted.id AS NEWID FROM inserted INNER JOIN deleted ON inserted.correlationid = deleted.correlationid

Pay attention, you could insert duplicate records in the log table.