Post reply

SQL on insert trigger won't react if data is updated from SSIS package

  • April 13, 2011 at 2:11 am

    #148807

    Hi to all of you 🙂

    I have a problem concerning an SQL trigger I've created. It should transfer data from a temporary table (on DB #1) to the production table (on DB #2) after a new row is being inserted.

    The temp table gets its data from a SSIS package (this step works 😉 ).

    But the trigger won't react on the newly created data row.

    If I insert a row manually to the temp table by using SQLSMS the trigger reacts and transfers the data to the prod table...

    You know what might be wrong ??

    best regards

    Benjamin

  • April 13, 2011 at 2:25 am

    #1310848

    Bulk operations do not by default fire triggers (see Books Online for documentation on that point)

    You need to add the option FIRE_TRIGGERS to the OLEDB destination. It may degrade performance.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • April 13, 2011 at 2:28 am

    #1310851

    Weehy 🙂

    It works - thanks.

    BR

    Benjamin

Viewing 3 posts - 1 through 3 (of 3 total)

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