How to stop events from firing for the same thing twice...   

Hi,

I have a trigger that fires events to SSNS on INSERT and UPDATE to my table. 
It all works fine, execpt that in my application that uses this table, there 
is code in there inserts to the table and then immediately fires an update. 
This results in 2 events being fired almost at the same time and so 2 notifications 
are generated.

What I want to do is to check that if the record is already in the "pending" 
events list before my trigger goes and fires an event for it - but I can't 
figure out how to do it. Heres my attempt, which doesn't work:

CREATE TRIGGER NewRecordTrigger
ON Table1
FOR INSERT, UPDATE
AS
DECLARE @eventBatchId BIGINT
DECLARE @providerName NVARCHAR(255)
DECLARE @ID INT
DECLARE @NSINstalled char(1)
Select @NSInstalled = dbo.IsSSNSInstalled()
IF (@NSInstalled = 'Y')
BEGIN
	SELECT @providerName = N'NewRecordAddedSPProvider'
	SELECT @ID = ID from inserted
             -- This is the line I would have expetced to not add the same 
event more than once!
	If NOT Exists (Select * from EntropyAlerts.NSCurrentNewRecordAddedEvents 
where ModuleCode = 'SITE_INC' and RecordID = @IncidentID)
	BEGIN
		EXEC [EntropyAlerts].[NSEventBeginBatchNewRecordAdded] @providerName, @eventBatchId 
OUTPUT
		EXEC [EntropyAlerts].[NSEventWriteNewRecordAdded] @eventBatchId, @ID
		EXEC [EntropyAlerts].[NSEventFlushBatchNewRecordAdded] @eventBatchId, 1
	END
END
GO


Can anyone explain why this doesn't work? I'm thinking its related to the 
fact that the triggers fire almost at the same time, so they both execute 
the check at the same, and then both add the event at the same time. I tried 
wrapping some transactions in there, but to no avail.


Thanks everyone.

Matt

date: Fri, 19 May 2006 07:07:54 +0000 (UTC)   author:   matt roberts