Solved

MS SQL Trigger produces to many records in audit table

Posted on 2011-03-18
6
337 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 53

Assisted Solution

by:Huseyin KAHRAMAN
Huseyin KAHRAMAN 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 53

Expert Comment

by:Huseyin KAHRAMAN
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
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.

738 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