Table data transfer using SSIS and triggers on destination table

Question

I'm a novoice in db operations. SQL Server 2008 R2 is the database I'm using.

I need to move selected rows from a staging database table to production database table. I'm using SSIS package to do that. I've few triggers on destination table. I'm now confused whether these triggers will be fired or not when I transfer the data from source table to destination table.

If these triggers won't be fired while transferring data from source to destination table, is there anything can be done to fire these triggers during this data transfer?

Giving me some information on the above will be greatly appreciated.

Update: Selected rows will be added to the destination table. Triggers will update data in some other table in the destination server based on newly inserted data. For example:

    CREATE TRIGGER [MyTableChange] 
       ON  [dbo].[DestinationTable]
       AFTER INSERT,UPDATE
    AS 
    BEGIN

    declare @newid as int
    select @newid=newid  FROM INSERTED

     Declare @total as int
     declare @lookatme as int
    select @total=total, @lookatme from DestinationTable where newid=@newid
    update anothertable set total=total+@total where lookwhere=@lookatme
    END

GO

Above trigger is kind of example not exactly same logic which I've in original trigger. But it gives info of what exactly I'm trying to do. I'll be accessing newly inserted row details to update another table value. Please do not take this as correct trigger in terms of syntax and way it is written. I just tried to give essence of original trigger.

Answer 1

If these triggers won't be fired while transferring data from source to destination table, is there anything can be done to fire these triggers during this data transfer?

In the OLEDB connection manager there are various options to load data. If

1. table or view using fast-load options is selected, then you have to explicitly specify FIRE_TRIGGERS in the advance editor as shown below :

   

   

2. If table or view data access mode is chosen then your triggers should fire normally because the insert is done row-by-row.

Answer 2

The triggers will fire upon the INSERT of the rows. Triggers aren't aware of the method of INSERT (such as via a T-SQL statement, from inside an SSIS package). Any INSERT or UPDATE will cause them to fire based on the definition you've provided. *Assumption is using regular table access. If you're using fast load, you have to specify trigger behavior as Kin noted.

One caution would be in regard to the number of rows you're moving. The triggers would fire after each row, and if the volume of rows is high this could be an issue. A better method might be to bring the data into a staging area via your SSIS package, make your updates to the primary destination table, then use the staged data to cascade your updates elsewhere, instead of what you would have done with your triggers. It would be more of a set-based approach this way, as opposed to taking row-by-row actions.