Execute the following SQL Server T-SQL script in Query Editor to create a trigger which fires when Salary or SSN is updated.

The trigger creates two (2) audit trail entries: previous data and new data(replacing data).

-- SQL insert audit trail table when sensitive data changes

-- SQL after trigger - SQL trigger - SQL column update trigger

USE Payroll

GO

CREATE TRIGGER trgEmployeeUpdate

ON Employee

AFTER UPDATE

AS

  IF (Update(Salary)

       OR Update(SSN))

    BEGIN

      INSERT INTO CorpSecurityEmployeeHistory

                 (auditlogtype,

                  auditEmployeeDeptID,

                  auditEmployeeID,

                  auditEmployeeSalary,

                  auditEmployeeSSN,

                  auditUpdatedBy)

      SELECT 'PREVIOUSDATA',

             DeptID,

             EmployeeID,

             Salary,

             SSN,

             User_name()

      FROM   DELETED

      INSERT INTO CorpSecurityEmployeeHistory

                 (auditlogtype,

                  auditEmployeeDeptID,

                  auditEmployeeID,

                  auditEmployeeSalary,

                  auditEmployeeSSN,

                  auditUpdatedBy)

      SELECT 'NEWDATA',

             DeptID,

             EmployeeID,

             Salary,

             SSN,

             User_name()

      FROM   INSERTED

    END;

GO