March 8, 2011 at 10:48 am
If I have a trigger on table, and I use OLE DB Destination (regular, not fast) to dump data into that table. Will the trigger fire once for every row or fire once for the whole dump?
March 8, 2011 at 12:28 pm
Check out this thread: http://qa.sqlservercentral.com/Forums/Topic1011963-149-1.aspx
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 8, 2011 at 12:45 pm
That thread is talking about why the trigger wasn't firing at all. I know with fast load it doesn't trigger, which is why I specified the regular load.
What I want to know is that an Insert of trigger, or an after trigger, do they fire on a per row basis, or will they fire for the entire dump. And if they fire for the entire dump, will all the information be in the magic tables? or just the last row?
March 8, 2011 at 1:07 pm
Khades (3/8/2011)
What I want to know is that an Insert of trigger, or an after trigger, do they fire on a per row basis, or will they fire for the entire dump. And if they fire for the entire dump, will all the information be in the magic tables? or just the last row?
Triggers fire on a per batch basis. You can set FastLoadInsertCommitSize when fast load is on in the properties of the OLE DB Destination. The default is 0 which says to commit all data in one batch. All rows are in the magic (a.k.a. virtual) tables for all batches regardless of batch size.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 8, 2011 at 1:11 pm
PS When using regular load it looks like the commit size is 1 and I can't see a way to change it. This means a trigger on the table will fire once for each row being imported and the virtual table will contain just the one row in the batch each time the trigger executes.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 8, 2011 at 2:18 pm
Thank you very much sir!
March 8, 2011 at 2:28 pm
You're very welcome :Whistling:
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply