[dba-SQLServer] SQL 7 - Trigger Question

Francisco Tapia fhtapia at gmail.com
Fri Jan 4 15:04:19 CST 2008


ah, i mis-read it then :).   You'll want to encapsulate your variables with
COALESC(@Variable, 'NewValue') that way all variables have data because you
cannot compare a null with anything even another null.



On Jan 4, 2008 11:43 AM, Joe Rojas <JRojas at tnco-inc.com> wrote:

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



More information about the dba-SQLServer mailing list