Post reply

Disable update trigger while running package?

  • October 9, 2007 at 2:17 pm

    #121856

    Hello!

    I´m having some issues with a new package I built.

    The packages does upserts to a table in my database, and it´s all working fine.

    However, I have an update trigger on my table, which I don´t want to fire when running my package.

    So I guess I´ll have to disable the trigger when running the package...

    Problem is, I still want the trigger to be fired for any updates coming from other sources

    than my package.... How can I accomplish this? Is it possible?

  • October 9, 2007 at 2:36 pm

    #740534

    Use and execute SQL task in your package and disable the trigger prior to your data flow task.

    USE pubs

    go

    ALTER TABLE dbo.employee DISABLE TRIGGER employee_insupd

    go

    Tommy

    Follow @sqlscribe

  • October 9, 2007 at 11:18 pm

    #740638

    Make sure that you enable it by using a T-SQL task after the execution of your task




    My Blog: http://dineshasanka.spaces.live.com/

  • October 9, 2007 at 11:31 pm

    #740640

    What you guys describe is exactly what I´ve already done.

    Problem is, what happens if an external application makes an update while my package is running and the trigger is disabled? I still want my trigger to fire for any updates coming from any source other than my package!

    I guess it would be possible to somehow lock the table while running my package,

    but how would I do that?

  • October 11, 2007 at 6:40 pm

    #741416

    How about this? In your trigger make sure that it exits immediately if the update is a multi-row update. Then in the package import into a staging table first (a clone of the ultimate destination table but with no tirgger). Then write t-sql to do the insert and/or joined update.

  • October 11, 2007 at 11:55 pm

    #741441

    Finally solved it by setting an "Application name" for my destination table connection manager.

    In the trigger, I then check which application fired the trigger, like so:

    IF (SELECT PROGRAM_NAME FROM MASTER.DBO.SYSPROCESSES WHERE SPID=@@SPID) <> 'MYAPPNAME'

    BEGIN

    ....DO WORK

    END

    This is exactly what I needed!

  • October 12, 2007 at 5:32 pm

    #741755

    You could shorten that to "IF APP_NAME() != 'MyAppName'", which would probably be better than directly referencing the sysprocesses compatability view.

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

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