0

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.

Heather Webster
  • 1
  • 2
  • Can you please be a bit more specific about what you expect to get, what you get, and what exact update are you performing? – Yosi Dahari Sep 11 '13 at 15:34
  • Hi, data-wise I expected to get what I am getting in the second inserted row, which is who updated the record, when modificatiopn occurred, the old value (which is a grade from A-E), the new value (where the grade has been modified) employee number, what area of performance the grade relates to, date of entry of original record, ID of original record (primary key, auto-increment). As I say, this works on one of the rows, but in the other I get exactly the same data but without the old grade value. The update is occurring on the grade column only. I hope that clarifies. Thanks. – Heather Webster Sep 11 '13 at 15:42
  • Sorry, I should have stated that I was only expecting this trigger to insert one row, i.e. the one which is currently doing what I want it to. I have no idea why I am getting two rows. – Heather Webster Sep 11 '13 at 15:51

0 Answers0