INSERT TRIGGER TRACKS WHO AND WHEN

Create Trigger trgChangedDataInsert

ON dbo.employee

for Insert

AS

Declare @EmpID int

Declare @OldData varchar(25)

Declare @NewData Varchar(25)

Declare @UserID varchar(25)

Set @EmpID =

(Select E.EmpID from dbo.employee E

JOIN  Inserted I On E.EmpID = I.EmpID)

SET @oldData = 'no old data'

Set @NewData = (Select E.CurrentTitle from dbo.employee E

JOIN  Inserted I On E.EmpID = I.EmpID)

SET @UserID = (Select User_Name())

INSERT INTO dbo.ChangeHistory

(EmpID, OldCurrentTitle, NewCurrentTitle, ActiveUser,

TimeChanged)

VALUES(@EmpID, @OldData, @NewData,

@UserID, GETDATE())

Similar code can be used to track updates, and deletes using the inserted and deleted tables.