[dba-SQLServer] SQL 7 - Trigger Question

Joe Rojas JRojas at tnco-inc.com
Fri Jan 4 13:43:30 CST 2008


Hi Francisco,

I will try this out but I have a follow up question.

My application only updates/inserts one row at a time even if there are
more than one updates or inserts required. I iterate though the rows
that need to be updated/inserted and call the SP to perform the task
each time. By doing so, shouldn't INSERTED and DELETED only contain one
row for each call?

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
Francisco Tapia
Sent: Friday, January 04, 2008 2:30 PM
To: dba-sqlserver at databaseadvisors.com
Subject: Re: [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...
_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com





More information about the dba-SQLServer mailing list