Solved

MS SQL Trigger produces to many records in audit table

Posted on 2011-03-18
6
333 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
  • 2
  • 2
6 Comments
 
LVL 51

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 51

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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

772 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