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