Trigger on many rows does not end

[fvlo]

Hello,
Facts:
200.000 records are inserted in a table.
This table has a trigger on it which modifies a datacolumn if a condition is TRUE.
The trigger works fine with 1.000 records and even with 10.000 records (though slowing down).

Problem:
If 200.000 records are supplied to the trigger, it does not seem to stop ever.
I have cancelled the procedure that made the trigger fire after a weekend, reading more than 5 TeraBytes of data!

I found a solution for it, but the solution does not seem to be the best solution to me. (see the second trigger below)

This first trigger is the one with the problem, it keeps on running:

CREATE TRIGGER TriggerOnMyTable
ON MyTable AFTER INSERT,UPDATE AS
BEGIN
SET NOCOUNT ON

UPDATE MyTable
SET AnyDataColumn = 'T'
FROM INSERTED AS INS
JOIN MyTable AS MINE
ON INS.AnyKeyCol = MINE.AnyKeyCol

DROP TABLE #temp
END

The next trigger works, but it does not seem to be the best solution...

CREATE TRIGGER TriggerOnMyTable
ON MyTable AFTER INSERT,UPDATE AS
BEGIN --About 200.000 records will be triggered
SET NOCOUNT ON

SELECT AnyKeyCol
INTO #Temp
FROM INSERTED

CREATE INDEX AnyIndexName ON #Temp (AnyKeyCol)

UPDATE MyTable
SET AnyDataColumn = 'T'
FROM #temp AS INS
JOIN MyTable AS MINE
ON INS.AnyKeyCol = MINE.AnyKeyCol

DROP TABLE #temp
END

 

[snag56]

You may want to check whether you are allowing nested triggers, using:

EXECUTE sp_dboption 'myDB', 'recursive triggers'

As described in BOL, you can add 'TRUE' or 'FALSE' to the above statement, to alter your current setting.
This may not be the issue here, but it's worth testing.

 

[fvlo]

Thanks for the suggestion, but I have recursive triggers set off.