Solved

MS SQL Trigger produces to many records in audit table

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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

760 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now