I would need to test this in order to verify (not sure if it would work).
Perhaps having a timestamp on the table? When a SELECT is initiated this would change. Then again I believe this is only when data is changed. With that in mind you could incorporate a change to a field (a meaningless field used only for this purpose) which would fire the trigger. You could also have the trigger fire only for a change to THIS field which means you could have a custom process fire for a query that would not be included in a record update / delete / add.
With this in mind I was going to post, after reading the article (and still will) the concept of TABLE VARIABLE. I use these often, when a set based operation is simply not possible.
Upon entering this forum I see the first post is regarding the very concept that I planned on writing about. I believe I would change the logic just a bit however as the poster only mentions the Identity column. I don't know that this would work as the complete record is not present until the trigger has completed (committed).
That said I would implement the TABLE VARIABLE as follows (keeping the logic in the trigger rather than a trigger calling an external procedure – Eliminates the dynamic portion as well as the parsing of XML, Comma string, ect.).
Lets say we are inserting simple customer data (multiple records / bulk insert). Another assumption is that we are not inserting more than something like 5,000 records at a time.
DECLARE @CustomerID as varchar (20)
,@CustFirstName as varchar (30)
,@CustLastName as varchar (40)
,@CustPhone1 as varchar (15)
,@RecordCount as int
DECLARE @ProcessTable as TABLE
CustomerID varchar (20)
,CustFirstName varchar (30)
,CustLastName varchar (40)
,CustPhone1 varchar (15)
,Processed bit 0
INSERT INTO @ProcessTable
(CustomerID, CustFirstName, CustLastName, CustPhone1, Processed)
SELECT CustomerID, CustFirstName, CustLastName, CustPhone1, Processed
SELECT @RecordCount = count(*)
WHILE @Count > 0
SELECT TOP 1 @CustomerID = CustomerID, @CustFirstName = CustFirstName
,@CustLastName = CustLastName, @CustPhone1 = CustPhone1
WHERE Processed = 0
Perform your logic here such as duplicating the record somewhere, checking the data, ect
SET @Count = (@Count – 1)
SET Processed = 1
WHERE CustomerID = @CustomerID
This is a very basic example however I think you see the point. Now another consideration is that there could be more than one update to a given CustomerID within this bulk insert. With that in mind I would add field to my table variable, lets call it ( InsertedID int PK). I would then update my processed flag based upon this Identity column so that multiple records affecting the same CustomerID could be handled.
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh