[dba-SQLServer] SQL 7 - Trigger Question

Hi Susan,

Thanks for the link!
It was insightful but not directly applicable to my issue...as far as I
can tell. :)

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


-----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