Insert Trigger on SQL Server for Change/Audit History

Question

I have two tables on my application (I'm trying to create a bug tracker). One that I can add a record/issue to and one that is for the history of the issue.

So, the second (Bugs_Shadow) table will create the new record along with the first one (Bugs) and then, I can update the issue on table 1 when someone has attempted a fix. When update is clicked I want it to add the update row as a whole to the 2nd table but also keep the original record. Thus whenever a change is made it will create a new record in the Bugs_Shadow table. I hope that makes sense?

I have created this trigger:

CREATE TRIGGER [tr_Bugs_Insert]
    ON [dbo].[Bugs]
    AFTER UPDATE, INSERT
    AS
    BEGIN
        INSERT INTO Bugs_Shadow (BugID, [User], Date, Subject, Description, [Source Code], [Current State])
        SELECT BugID, [User], Date, Subject, Description, [Source Code], [Current State]
        FROM Bugs

    END
    GO

It works so far as to create the identical entry in the 2nd table but when I update the entry in the 1st table, it does the same thing in the 2nd one whereas I want it to create a new record that is identical apart from the changes made by the user.

Hope there's some help out there. It's my first attempt at triggers and it's took me a while to get to this stage.

Thanks

Answer 1

You need to triggers here:

• One after the insert in one table to insert it into the other
• One before update in one table to insert it into the other

---

CREATE TRIGGER [tr_Bugs_Insert] 
    ON [dbo].[Bugs]
    AFTER INSERT
    AS
    BEGIN
        INSERT INTO Bugs_Shadow (BugID, [User], Date, Subject, Description, [Source Code], [Current State])
        SELECT BugID, [User], Date, Subject, Description, [Source Code], [Current State]
        FROM Bugs
END
GO

CREATE TRIGGER [tr_Bugs_Update]
    ON [dbo].[Bugs]
    BEFORE UPDATE
    AS
    BEGIN
        INSERT INTO Bugs_Shadow (BugID, [User], Date, Subject, Description, [Source Code], [Current State])
        SELECT BugID, [User], Date, Subject, Description, [Source Code], [Current State]
        FROM Bugs
END

EDIT

MS SQL doesn't support before triggers, so the above is not valid. You could use an INSTEAD trigger. With an INSTEAD trigger, only the trigger gets executed and not the original statement. So what you should do in that trigger is insert the old value in one table and update the other table.

For more info, see this question: How can I do a BEFORE UPDATED trigger with sql server?

There are also some answers in there specifying alternative solutions.