[dba-SQLServer] SQL 7 - Trigger Question

Hello,

I created a trigger on a table that is designed to monitor the change in
value for a few fields. An audit log trigger if you will.
Whenever there is an update event on the table being monitored, the
trigger compares the before and after value and if they are different,
the trigger will insert a row into the audit trail table.

The issue I'm having is if more than one field was changed, only the
first field that is monitor has a record in the audit table. For
example, I am monitoring an employee's vacation time and personal time
balance. If there is an update that changes the value of both the
vacation time and the personal time, only the vacation time update has a
record in the audit trail table and the desired goal is to have a record
for each field change.

I suspect that the problem lies in my trigger, but I can seem to figure
it out.
I have included the trigger below and I hope someone can point out my
issue.

Thanks in advance!!!

CREATE TRIGGER dbo.tr_AuditEmpData ON dbo.tblEmpData
FOR UPDATE
AS

BEGIN
SET NOCOUNT ON

DECLARE @ADPFileNum int
DECLARE @VTimeNew decimal(10,2)
DECLARE @VTimeOld decimal(10,2)
DECLARE @PTimeNew decimal(10,2)
DECLARE @PTimeOld decimal(10,2)
DECLARE @NewRate money
DECLARE @OldRate money
DECLARE @NewAccrual decimal(10,2)
DECLARE @OldAccrual decimal(10,2)

SELECT @ADPFileNum = ADPFileNum, @VTimeNew = VacationTime, @PTimeNew =
PersonalTime, @NewRate = PayRate, @NewAccrual = AccrualAmount
FROM inserted

SELECT @VTimeOld = VacationTime, @PTimeOld = PersonalTime, @OldRate =
PayRate, @OldAccrual = AccrualAmount
FROM deleted

--Check Vacation Time
IF (@VTimeNew <> @VTimeOld)
BEGIN
	INSERT INTO dbo.tblAuditEmpData (ADPFileNum, FieldName,
OldValue, NewValue)
	VALUES (@ADPFileNum, 'VacationTime', @VTimeOld, @VTimeNew)
END

--Check Personal Time
IF (@PTimeNew <> @PTimeOld)
BEGIN
	INSERT INTO dbo.tblAuditEmpData (ADPFileNum, FieldName,
OldValue, NewValue)
	VALUES (@ADPFileNum, 'PersonalTime', @PTimeOld, @PTimeNew)
END

--Check Pay Rate
IF (@NewRate <> @OldRate)
BEGIN
	INSERT INTO dbo.tblAuditEmpData (ADPFileNum, FieldName,
OldValue, NewValue)
	VALUES (@ADPFileNum, 'PayRate', CONVERT(varchar(50), @OldRate),
CONVERT(varchar(50), at NewRate))
END

--Check Accrual Amount
IF (@NewAccrual <> @OldAccrual)
BEGIN
	INSERT INTO dbo.tblAuditEmpData (ADPFileNum, FieldName,
OldValue, NewValue)
	VALUES (@ADPFileNum, 'AccrualAmount', CONVERT(varchar(50),
@OldAccrual), CONVERT(varchar(50), at NewAccrual))
END

SET NOCOUNT OFF
END


Joe Rojas
Information Technology Manager
Symmetry Medical TNCO
15 Colebrook Blvd
Whitman MA 02382
781.447.6661 x7506