[dba-SQLServer] SQL 7 - Trigger Question

LOL, real positive isn't she? 


John W. Colby
Colby Consulting
www.ColbyConsulting.com 
-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Susan
Harkins
Sent: Friday, January 04, 2008 1:24 PM
To: dba-sqlserver at databaseadvisors.com
Subject: Re: [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
>
_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com