asked on

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

SOLUTION

HainKurt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

HainKurt

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?

ASKER CERTIFIED SOLUTION

TCIG

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

TCIG

ASKER

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'

Robert Marley

Starting the auto-close procedure on behalf of the question author.

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

Mod_MarlEE
Community Support Moderator