[dba-SQLServer] SQL 7 - Trigger Question

yes she is :), LOL

I was thinking it's more to do about how you compare your data.  You're only
loading the values into your variables once, thus it only finds the first
changed data as opposed to all the changed data.

I suggest you consider working with the entire select statement instead of
single values such as

INSERT INTO dbo.tblAuditEmpData (ADPFileNum, FieldName, OldValue, NewValue)
SELECT ADPFileNum, 'Field Name',  OldValue, NewValue FROM INSERTED AS I
   INNER JOIN DELETED AS D ON I.PKID = D.PKID AND I.VacationTime<>
D.VacationTime


This will capture all similar records with different vacation time values.
do this for each clause you need.

--
Francisco


On Jan 4, 2008 11:07 AM, jwcolby <jwcolby at colbyconsulting.com> wrote:

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


-- 
-Francisco
http://sqlthis.blogspot.com | Tsql and More...