Trigger Problem

Question

Post reply

Trigger Problem

Graeme100

SSCrazy Eights

Points: 8054
More actions
August 23, 2007 at 3:14 am

#178144

Hi Guys,
I have triggers that are populating a table with an identity column which has a current identity value of say 100000....that's fine.
However, when the triggers fire, the inserts into this table are inserting duplicate identities the value of which is about 24000000...so all inserts into this table via the triggers have the same identity....and there is no reference to the identity column in the trigger.
The triggers do not attempt to update the identity so how can this happen??
Anyone had and experience of this?
Any thoughts on this would be gratefully received.
Thanks
Graeme

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Graeme

Please will you post DDL for your table and your trigger.

Thanks

John

Graeme100 SSCrazy Eights Points: 8054 More actions · Answer 2

Hi,

Here is the table that is being populated from the trigger

CREATE TABLE [dbo].[tblDailyDataChanges] (

[DailyDataChangeID] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,

[UniqueID] [int] NOT NULL ,

[DataEntityTypeID] [tinyint] NOT NULL ,

[VerifyDate] [datetime] NOT NULL ,

[Processed] [bit] NOT NULL

) ON [PRIMARY]

GO

This is a typical Update trigger that we use :

CREATE TRIGGER TR_VerificationUpdate_tblClubsOther

ON dbo.tblClubsOther

FOR UPDATE

AS

SET NOCOUNT ON

IF UPDATE(VerifyDate) AND EXISTS (SELECT TOP 1 1 FROM inserted WHERE inserted.VerifyDate IS NOT NULL)

BEGIN

IF EXISTS (SELECT TOP 1 1

FROM BexAlpha_Stage.dbo.tblClubsOther destination

INNER JOIN inserted ON destination.ClubsOtherID=inserted.ClubsOtherID

&nbsp

BEGIN

UPDATE destination

SET ClubsOtherID=inserted.ClubsOtherID

, DirectorID=inserted.DirectorID

, CompanyID=inserted.CompanyID

, ActionDate=inserted.ActionDate

FROM BexAlpha_Stage.dbo.tblClubsOther destination

INNER JOIN inserted ON destination.ClubsOtherID=inserted.ClubsOtherID

END

ELSE

BEGIN

INSERT INTO BexAlpha_Stage.dbo.tblClubsOther(

ClubsOtherID

, DirectorID

, CompanyID

, ActionDate

&nbsp

SELECT ClubsOtherID

, DirectorID

, CompanyID

, ActionDate

FROM inserted

END

INSERT INTO dbo.tblDailyDataChanges (UniqueID, DataEntityTypeID, VerifyDate, Processed)

SELECT DISTINCT DirectorID AS UniqueID, 1 AS DataEntityTypeID, CAST(CONVERT(nvarchar(8),getdate(),112) AS Datetime) AS VerifyDate, 0 AS Processed

FROM inserted

WHERE NOT EXISTS (SELECT TOP 1 1

FROM dbo.tblDailyDataChanges ddc

WHERE inserted.DirectorID = ddc.UniqueID

AND ddc.DataEntityTypeID = 1

AND ddc.VerifyDate = CAST(CONVERT(nvarchar(8),getdate(),112) AS Datetime))

INSERT INTO dbo.tblDailyDataChanges (UniqueID, DataEntityTypeID, VerifyDate, Processed)

SELECT DISTINCT CompanyID AS UniqueID, 2 AS DataEntityTypeID, CAST(CONVERT(nvarchar(8),getdate(),112) AS Datetime) AS VerifyDate, 0 AS Processed

FROM inserted

WHERE NOT EXISTS (SELECT TOP 1 1

FROM dbo.tblDailyDataChanges ddc

WHERE inserted.CompanyID = ddc.UniqueID

AND ddc.DataEntityTypeID = 2

AND ddc.VerifyDate = CAST(CONVERT(nvarchar(8),getdate(),112) AS Datetime))

END

Many Thanks

Graeme

Steve Jones - SSC Editor SSC Guru Points: 736458 More actions · Answer 3

Can you give an example of what you mean by the identity ranges not matching? It's not clear from your first post.

Graeme100 SSCrazy Eights Points: 8054 More actions · Answer 4

Ok sure,

On the table DailyDataChanges the current identity is 1790309, so the next record that is entered by the triggers should have an Identity of 1790310....

But then as the triggers fire and insert the data in DailyDataChanges as my scripts above detail, the next record and every record after that have an identity of 292404311...

I just can't see how this is possible without each trigger containing the Identity_insert set command and for the ID of 292404311 being explicity set which as you can see from my scripts is not.

In the table the IDs now look like the following

1790307

1790308

1790309

292404311

292404311...and so-on.

There are many tables with these insert/update triggers that all write to DailyDataChanges...

Hope this is clearer

Graeme

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 5

Graeme

You say you have many such triggers. Do any of them have DELETE statements in? It looks as if one of them might be inserting a large number of rows and then it (or something else) is deleting them.

Of course, this wouldn't explain why you're getting duplicates in your identity colmun. What happens if you execute an ad hoc INSERT statement against the table - what new values appear in the identity column then?

John

Graeme100 SSCrazy Eights Points: 8054 More actions · Answer 6

Hello John,

All the triggers are performing inserts only...it's like an audit log but just capturing specific changes.

If I perform a manual insert into the table, the Identity is created as per normal..no problem.

It is a little frustrating

Thanks

Graeme

Graeme100 SSCrazy Eights Points: 8054 More actions · Answer 7

Just thought I would close this one as I have resolved the issue.

The large number being entered was actually the ID of the table from sysobjects.

This was because the Identity field of the daily changes table was set to Not For Replication. All the triggers on the database are fired only by replication ....something I missed there.

Some unusual behaviour neverthe less.. something to watch out for in the future.

Thanks for your input.

Regards

Graeme