Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
1  
Are you updating your data @ your destination, or adding new data via your SSIS package? What do the triggers do? Rt click on one and script as create, scrub any sensitive data, and post one in your question. –  Eric Higgins Sep 16 '13 at 20:35
    
@EricHiggins Thanks for looking into it. I've updated my question with all the details you asked for, can you tell me whether this information is sufficient? –  JPReddy Sep 16 '13 at 20:53
1  
Yes that helps. –  Eric Higgins Sep 16 '13 at 20:58
1  
Just a word of caution on your trigger, this assumes only one record is ever changed at a time. If two records get updated in the same batch, your precious logic falls apart –  billinkc Sep 17 '13 at 4:14

2 Answers 2

up vote 5 down vote accepted

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 :

    enter image description here

    enter image description here

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

share|improve this answer
1  
+1 good point on fast load –  Eric Higgins Sep 17 '13 at 0:52

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.

share|improve this answer
1  
Unless the triggers are disabled which the destination components can set (see Kin's answer below) –  billinkc Sep 17 '13 at 4:12
    
I appended a note to my answer re: fast load. –  Eric Higgins Sep 17 '13 at 17:29

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.