How to setup trigger based audit?

Question

I have a table Employee:

CREATE TABLE [dbo].[Employee]
(
    [EmployeeCode] [int] NOT NULL,
    [FirstName] [varchar](50) NULL,
    [LastName] [varchar](50) NULL,
    [Email] [varchar](50) NULL,
    [Position] [varchar](30) NULL
)

I want to log changes in Employee_Audit table every time there's a change in Position (along with the Old Position, New Position and Timestamp) in Employee table.

CREATE TABLE [dbo].[Employee_Audit]
(
    [EmployeeCode] [int] NOT NULL,
    [FirstName] [varchar](50) NULL,
    [LastName] [varchar](50) NULL,
    [Email] [varchar](50) NULL,
    [PositionOld] [varchar](30) NULL,
    [PositionNew] [varchar](30) NULL,
    [Date] [datetime] NULL
 )

How do I achieve this?

Answer 1

In addition to trigger option @marc_s mentioned, If you want to not just consider position, and considering all column changes auditing, below options provide you to do auditing, without any specific programming need. You can see whether it fits for your needs.

• you can think of SQL Server Temporal Tables. Read on SQL Server Temporal Tables on MSDN. They provide transparent way to auditing the row changes.

• You also have Change Data Capture option to track the historical changes for columns. Change Data Capture on MSDN

Answer 2

You basically need an UPDATE trigger that checks if the Position value has changed - and if so, records the details into Employee_Audit:

CREATE OR REPLACE trgEmployeeUpdate
ON dbo.Employee 
AFTER UPDATE 
AS
BEGIN
    -- based on the Inserted and Deleted pseudo tables, join the rows
    -- that were updated and look for changes in "Position"
    INSERT INTO dbo.Employee_Audit (EmployeeCode, FirstName, LastName, Email, 
                                    PositionOld, PositionNew, [Date])
        SELECT
            i.EmployeeCode, i.FirstName, i.LastName, i.Email,
            d.Position, i.Position, SYSDATETIME()
        FROM
            Inserted i
        INNER JOIN
            Deleted d ON i.EmployeeCode = d.EmployeeCode
        WHERE
            i.Position <> d.Position    -- Position has changed
END