Post reply

Problem with triggers

  • June 22, 2009 at 4:59 am

    #95807

    Hello everyone,

    I have a problem running a INSERT INSTEAD OF trigger from my stag server to development server. Let me explain the step

    1.Live server updates the stag server by first truncating all the date in a stag server table and performs BULK insert using DTS Package

    2. This runs as a sql job everyday from the Live server

    During this process, INSTEAD OF trigger that update my development server is not getting fired.

    I assume this is probably because from where the insert statement is executed to insert bulk data in to the stag server.

    could some one share their experience here..

    Ta

  • June 22, 2009 at 6:33 am

    #1013250

    Assuming you are using BULK INSERT or BCP to do the bulk load, then have a look at BOL.

    This is what it has to say about BULK INSERT

    FIRE_TRIGGERS

    Specifies that any insert triggers defined on the destination table execute during the bulk-import operation. If triggers are defined for INSERT operations on the target table, they are fired for every completed batch.

    If FIRE_TRIGGERS is not specified, no insert triggers execute.

  • June 22, 2009 at 6:54 am

    #1013258

    I will look in to BOL then update the post

  • June 22, 2009 at 7:56 am

    #1013296

    Hi yeh,

    This bulk operation is carried out using following statement in DTS package.

    select fields from table.

    This statment performs bulk insertion and Im not sure how to set fire trigger on.

    Can some one help.

    Ta

  • June 22, 2009 at 8:25 am

    #1013318

    I'm not sure it's possible.

    Unless somebody else knows of a way, you may have to resort to bulk inserting the data into a staging table, and then insert the data into the real table as a second task.

  • June 22, 2009 at 8:57 am

    #1013359

    yes that what I figured it out as well. I will need to use BCP or Bulk Insert instead of DTS PACKAGE. According to my knowledge Bulk insert will provide greater performance..

    Appreciate your time and effort.

    ta

  • June 23, 2009 at 5:10 am

    #1013834

    Hi

    If you do loading with SSIS then check out this solution:

    http://www.sqlservercentral.com/Forums/Topic676422-146-1.aspx#bm677555

    Regards,

    René

  • June 23, 2009 at 5:41 am

    #1013847

    Rene. May be in future we might use SSIS for ETL but as of now we will be using BCp or BULK Insert. I will go through the link .

    Thank you

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply