Solved

MS SQL Trigger produces to many records in audit table

Posted on 2011-03-18
6
340 Views
Last Modified: 2012-05-11
I have a trigger that is supposed to copy a single record into an audit table any time a field in a record is changed. The application maintains a monthly report for 28 regions. Each report consists of 15 questions and thus 15 records per report per month. When a user makes a change to a single question and saves their change, I dont get the single audit table record I expect. Instead I get 420 records. That is one record for every question and every region for the month.  What is wrong with my trigger?
USE [Appointment]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER trigger [dbo].[Audit_Trigger_ChangesBy]  on [dbo].[tbl_stmt] for update
as

IF(UPDATE (e_date_lastupdated))
Begin
insert into Audit_Log
select e_id, e_month, e_lastupdated_by, e_date_lastupdated, e_submitted_by, e_date_submitted, getdate() from inserted
End

SET ANSI_PADDING OFF

Open in new window

0
Comment
Question by:TCIG
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
6 Comments
 
LVL 56

Assisted Solution

by:HainKurt
HainKurt earned 500 total points
ID: 35166712
try

IF(UPDATE (e_date_lastupdated))
Begin
insert into Audit_Log
select e_id, e_month, e_lastupdated_by, e_date_lastupdated, e_submitted_by, e_date_submitted, getdate() from inserted
End

-->

insert into Audit_Log
select e_id, e_month, e_lastupdated_by, e_date_lastupdated, e_submitted_by, e_date_submitted, getdate() from inserted i, deleted d where i.e_id=d.e_id and i.e_date_lastupdated != d.e_date_lastupdated

0
 
LVL 56

Expert Comment

by:HainKurt
ID: 35166734
how do you update the single question?
like this?

update report set a=@a;
update report set b=@b;
...
update report set x=@x;

multiple updates for one record?
0
 

Accepted Solution

by:
TCIG earned 0 total points
ID: 35183415
Using what you provided and including a join clause is what I am using to get a single record entered into the Audit table. See code below:

 IF(UPDATE (e_date_lastupdated) )
Begin

insert into Audit_Log
select i.e_id, i.e_month, i.e_lastupdated_by, i.e_date_lastupdated, i.e_submitted_by, i.e_date_submitted, getdate()
from inserted i inner join deleted d
on i.e_id = d.e_id and i.e_month=d.e_month
where i.e_date_lastupdated != d.e_date_lastupdated
End

I still need to work on this SQL as my e_id is really a region id and not a record id. Thus I am going to have to modify my Audit table to include a primary key so that the 'on' portion of the SQL statement will only need to equate the ID for the join to work (ie ... on i.ID=d.ID where ...).

MAC
0
 

Author Comment

by:TCIG
ID: 35199470
I need to change select solution.  My solution was a complete solution and thus should be listed as the solution with an assisted solution by HainKurt.  As I could not have arrived at the final solution without HainKurt help, I would like to award him 500 points.  So I would like to do the following:
1.   #35183415 listed as solution
2.  #35166712 as assisted solution
3.  Comment as follows:
'I selected my own as the best solution as it included the join that is necessary for this SQL to work.  Without the join I was getting over 5000 records.  Nonetheless, you put me on the right path and saved me a lot of time.

Thanks!

MAC'
0
 

Expert Comment

by:Mod_MarlEE
ID: 35239141
Starting the auto-close procedure on behalf of the question author.


-----
Everyt'ing is everyt'ing,

Mod_MarlEE
Community Support Moderator
0

Featured Post

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

630 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question