I am new to triggers and what I do know is self taught, so please bear with me and keep it simple!
I have created a trigger to capture data changes (I'm using SQL Server 2008 R2 Express) but I can't understand why I'm getting two rows inserted into the new table.
This is what I have so far:
CREATE TRIGGER trg_Audit
ON EmployeeTable
FOR UPDATE
AS
INSERT INTO NewTable (DBUser, executiontime, oldvalue, newvalue, employeeid, type, entrydate, ID)
SELECT
SUSER_SNAME(), GETDATE(),
deleted.value, inserted.value, inserted.employeeid, inserted.type,
inserted.entrydate, inserted.ID
FROM
inserted
INNER JOIN
deleted ON inserted.ID = deleted.ID
The second row is doing what I want, i.e. inserting one new record into the new table capturing the old and new data for the modified column, however I also get a row inserted without the old value.
Can anyone explain why this is please?
Many thanks.
I have now discovered the problem. There was a hidden trigger running alongside mine. Apologies.