Wednesday, March 25, 2009

T-Sql trigger doesn't always work.

I'm having a rather odd problem.  A trigger I wrote for database Inserts seems to work about 99 percent of the time, but periodically some records will get inserted that the trigger never fires for.  Or rather that the data the trigger is suppose to create is never created.

It's a rather odd delima, if I can figure it out I will try and put the answer here.  In the meantime, if anyone else has experienced this I wouldn't mind hearing about it.

Ok I figured out the answer with a little help from Google.
turns out that in SQL Server a trigger fires once per set of operation as opposed to once per affected row.
so for a standard insert statement the trigger works fine, but if the insert statement has more than one record it is inserting then the trigger will only be fired once.  Good news is that the "inserted" table contains all the records that were inserted, not just one; so you still have access to all the records, you just get them in batch form instead of individually.

No comments: