SQL Server 2008 trigger inserting multiple rows

609 views Asked by At

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.

0

There are 0 answers