MS SQL Trigger produces to many records in audit table

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

TCIGAsked:
Who is Participating?
 
TCIGAuthor Commented:
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
 
HainKurtSr. System AnalystCommented:
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
 
HainKurtSr. System AnalystCommented:
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
 
TCIGAuthor Commented:
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
 
Robert MarleySinger / SongwriterCommented:
Starting the auto-close procedure on behalf of the question author.


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

Mod_MarlEE
Community Support Moderator
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.