Auditing Data Changes on SQL Server Tables with Triggers

First we need a table:

CREATE TABLE dbo.AuditEmployee
(
EmployeeID INT,
UserName VARCHAR(255),
Operation CHAR(1)
CHECK Operation IN ('U', 'I', 'D', 'S'),
dt SMALLDATETIME NOT NULL
DEFAULT GETDATE()
)

Keeping the original tables in place, you can have the following:

CREATE TRIGGER Audit
ON dbo.Employee
FOR INSERT, UPDATE, DELETE
AS
IF (SELECT COUNT(*) FROM inserted) > 0
BEGIN
IF (SELECT COUNT(*) FROM deleted) > 0
BEGIN
-- update!
INSERT AuditEmployee
(EmployeeID, UserName, Operation)
SELECT EmployeeID, SUSER_SNAME(), 'U'
FROM inserted
END
ELSE
BEGIN
-- insert!
INSERT AuditEmployee
(EmployeeID, UserName, Operation)
SELECT EmployeeID, SUSER_SNAME(), 'I'
FROM inserted
END
END
ELSE
BEGIN
-- delete!
INSERT AuditEmployee
(EmployeeID, UserName, Operation)
SELECT EmployeeID, SUSER_SNAME(), 'D'
FROM deleted
END
GO

So, try out these examples, and you can see how the trigger captured all of your auditing:

INSERT Employee(FullName, Salary)
VALUES('Aaron Bertrand', 10000)
INSERT Employee(FullName, Salary)
VALUES('Joe Schmoe', 15000)
UPDATE Employee SET salary = 30000
WHERE EmployeeID = 1
-- handles multiple rows as well!
-- let's give everyone a raise to demonstrate
UPDATE Employee SET Salary = Salary + 2000
DELETE Employee WHERE EmployeeID = 2
SELECT * FROM Employee
SELECT * FROM AuditEmployee