Trigger is causing random number of duplicates

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Trigger is causing random number of duplicates

Author

Topic

TechnoBill
Starting Member

3 Posts

Posted - 2007-10-18 : 22:14:37

Hi,
We are having a problem with a trigger that we use to create records in a history table. It seems to be working fine in our environment here but in the live environment (with up to 100 users) the history table sporadically contains duplicates (sometimes 50 or 60 copies ) all with the same record even the same time stamp .

We have no idea what is happening.. I am hoping it is because we have done something stupid with the trigger...

ALTER TRIGGER [dbo].[UpdateRosterHistory]
ON [dbo].[tblRoster] FOR INSERT,UPDATE AS
BEGIN
INSERT INTO
tblRosterHistory
(
fldRosterRuleIdHistoryID,
fldRosterRuleId,
fldRecurrenceId,
fldPersonId,
.. snip
)
SELECT
newid(),
history.fldRosterRuleId,
history.fldRecurrenceId,
history.fldPersonId,
...snip
FROM
inserted as history
END

Can anyone give us a clue as to why it works most times and other times we have sooo many duplicates of a change in the history table

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-10-18 : 23:06:37

It doesn't look like the trigger is causing it. The trigger fires once per INSERT or UPDATE statement. So if you are receiving duplicates, then the problem must be in the application that is running the INSERT or UPDATE statements.

If, however, your trigger code doesn't match what you have posted (except for the snips in the columns), then it might be a different story. But what you have posted here is not the culprit.

Run SQL Profiler to determine what exactly the culprit is. It needs to be running when the dupes occur, so you might need to run it for a quite a while, depending upon how long it takes to duplicate this issue. Make sure to run SQL Profiler from a client machine and save the results to a file. This is highly recommended for performance reasons, especially since you might need to run it for a lengthy time. Consider restarting the trace if the issue hasn't happened in a day so that you don't have all of this trace data that doesn't contain the issue.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

TechnoBill
Starting Member

3 Posts

Posted - 2007-10-18 : 23:38:53

Hi Tara,
Thanks for the rapid reponse. I passed on your suggestion to the developer. (They still do not believe its the code as they have two totally different versions one that uses ADO to write out the record and another that uses a simple update SQL and the problem is being experienced in both).
I am convinced it must be something in the application at a higher level but now I will need to try and convince them :-).

BTW yes that was the complete trigger code... minus a huge number of fields that I 'snipped' out of the example.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-19 : 01:19:11

I see you also have the trigger for update. That can lead to a number of duplicates for same personid since you create a new record in history table for each update too.

E 12°55'05.25"
N 56°04'39.16"

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-10-19 : 01:28:12

With the same timestamp is what makes me think it's not the trigger, but Peter makes a good point. The inserted table contains the inserted rows as well as the after image of the updated rows. The deleted table would contain the before image of the updated rows.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Kristen
Test

22859 Posts

Posted - 2007-10-19 : 01:54:23

FWIW we don't store the new values in our History table - that's in the real table - we only store the deleted values. So a single record that is only inserted and not updated will never be stored in our history tables

Kristen

TechnoBill
Starting Member

3 Posts

Posted - 2007-10-25 : 00:00:02

Thanks to everyone for your help.
I managed to have an sql trace running at a site and caught the situation that was causing it to create the duplicates. As I suspected it did end up being a program bug. So the problem has been resolved by the development team.
The trigger was NOT creating the duplicates

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources