[dba-SQLServer] SQL 7 - Trigger Question

http://www.devx.com/dbzone/Article/7939/1954?pf=true

Joe, I didn't review your code and this article might not help you a bit. 

Susan H. 

> 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
> 
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>