So I'm enough of a manager now that I don't get to code often. Blah blah blah. I don't like it but that's the way it works for now. When I do get a chance to write some code I'd like to make the most of it. So I've got a problem where I'm parsing XML reports and then inserting a subset of the data into a SQL table for record keeping and the like. The insert appears to be approximately 1223 records of which there are only 372 distinct pages. Rather than have non-unique URLs in the table I figured I'd get rid of the dupes on insert with a trigger. That works great. I don't know about the efficiency, but at least I can still write a trigger.

Now what I'd like to do is have the trigger update a count field in the table in addition to preventing the duplicate item from inserting. This is where my trigger fails. The trigger is syntactically valid and when inserting the 1223 records, only the 372 unique URLs are actually inserted. However, the count column is not being incremented. Has anyone ever tried to do this or know for a fact that it's not allowed?

Here's the SQL:
CREATE TRIGGER DistinctURL_Insert ON dbo.T_PolicyURL
FOR INSERT
AS

DECLARE @TheURL VARCHAR(255)

IF (SELECT COUNT(*)  FROM T_PolicyURL p, INSERTED i WHERE p.URL = i.URL) > 1

BEGIN
 SELECT @TheURL = URL FROM INSERTED
 ROLLBACK TRANSACTION
 UPDATE T_PolicyURL SET FormCount = FormCount + 1 WHERE URL = @TheURL
END

SQL Books Online says:

If a ROLLBACK TRANSACTION is issued in a trigger:

• All data modifications made to that point in the current transaction are rolled back, including any made by the trigger.
• The trigger continues executing any remaining statements after the ROLLBACK statement. If any of these statements modify data, the modifications are not rolled back. No nested triggers are fired by the execution of these remaining statements.
• The statements in the batch after the statement that fired the trigger are not executed.

So could it simply be that your "SELECT @URL=URL ..." statement is being rolled back, too?  If you step through a debugger, you should be able to answer that.  If that's the case, maybe "DELETE FROM INSERTED" would be better than "ROLLBACK TRANSACTION."  Other than that ROLLBACK statement, what you're doing seems like it should work.  You could also put that UPDATE statement first & do a "DELETE FROM T_PolicyUrl WHERE FormCount <=1 AND EXISTS (SELECT * FROM T_PolicyUrl WHERE FormCount > 1)".  It's been a while since I've used T-SQL triggers, so I don't recall whether each one is atomic (is that even the right word?). 

Name

E-mail (optional)

Website (optional)