June 21, 2012 at 2:18 pm
We are working on a project where we are basically putting together a change log. All of our triggers are INSTEAD OF INSERT, UPDATE, DELETE for this. The problem I just realized is this: If a user updates the primary key of a table, I cannot use the update statement in the trigger because I cannot join the current value to the new one because they are different, and I cannot join the deleted to the inserted table.
CREATE TABLE triggerTest (colA int primary key, colB varchar(10), colC datetime)
CREATE TABLE triggerLog (op CHAR(1), colA int, colB VARCHAR(10), colC datetime, changedTime datetime)
GO
INSERT INTO triggerTest
SELECT 2, 'jaredTest', GETDATE()
CREATE TRIGGER tgr_testingUpdate
ON triggerTest
INSTEAD OF UPDATE
AS
BEGIN
SET NOCOUNT ON;
DECLARE @change_time DATETIME = GETDATE();
INSERT INTO triggerLog(op, colA, colB, colC, changedTime)
SELECT 'U', i.colA, i.colB, i.colC, @change_time
FROM inserted i;
UPDATE tt
SET colA = i.ColA,
colB = i.colB,
colC = i.colC
FROM triggerTest tt
INNER JOIN inserted i
ON tt.colA = i.colA --note this will fail when primary key is updated
END
UPDATE triggerTest --will not update parent table
SET colA = 4
WHERE colA = 2
Anyone have any thoughts on this?
Jared
CE - Microsoft
June 21, 2012 at 2:23 pm
This is why I like SIDs, you can have a unique key that never changes that can be used for just such cases.
Does the table have any other candidate keys that could be used in this case?
June 21, 2012 at 2:26 pm
Lynn Pettis (6/21/2012)
This is why I like SIDs, you can have a unique key that never changes that can be used for just such cases.Does the table have any other candidate keys that could be used in this case?
Nope. 🙁 Although, I'm starting to wonder why we are doing this as INSTEAD OF versus AFTER. Then we don't have to use the "inserted" data to update the main table. There "was" a reason, but I forget now...
Jared
CE - Microsoft
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply