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