Post reply

Enable/Disable Trigger

  • October 21, 2011 at 7:34 am

    #390671

    An existing trigger prevents bulk data insert into a database. In a stored procedure that pushes data from one database to another, I want to disable this trigger. The databases are the same which means the same trigger exists in both databases.

    The problem is: I want to disable trigger on the destination database but by design you cannot prefix trigger name with database name, thus the error: 'ENABLE/DISABLE TRIGGER' does not allow specifying the database name as a prefix to the object name.

    When I put using database to force the disable statement to execute on the destination database, I get another error: Incorrect syntax near 'USING'. I tried semicolons to no avail.

    Please help.

  • October 21, 2011 at 7:48 am

    #1397973

    Sorted, thanks.

    Database name can be prefixed on the table name.

  • October 21, 2011 at 8:56 am

    #1398030

    The problem still stands, musch as there's no error on ssms, there is a runtime error: Cannot disable trigger on database.dbo.tableName as the target is not in the current database.

    Please help.

  • October 21, 2011 at 9:03 am

    #1398037

    Use Database

    disable trigger dbo.triggername on dbo.tablename

    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

  • October 21, 2011 at 9:03 am

    #1398038

    You might try a double exec :

    EXEC('USE whatever;

    EXEC(''disable here'')

    ')

    Edit 1 : I might have misunderstood the using syntax you were talking about!

    Edit 2 : Gail's syntax will work after you add a ; after use db

  • October 24, 2011 at 1:13 am

    #1398521

    Thanks all for the prompt responses. I tried Gails solution but the stored proc does not like "Use" statement. I then used exec('Use database') which also failed with the same error of disabling trigger on the wrong DB. I used the double exec() as above and it did the trick.

    I'm wondering why the single exec('Use database') does not affect the statement immediately after it as I would have thought it is executing on the target database but it doesn't. Is there any way of outputting the actual script that will run when the stored proc is executed?

    The "Using"! I think I was getting the incorrect syntax because I was not using the "USE" statement. I don't know what gotten into me to use the c# directive.

    Anyway, thanks again for your time you took to worry about my problem.

  • October 24, 2011 at 2:06 am

    #1398533

    thulani.moyana 79207 (10/24/2011)

    ...

    I'm wondering why the single exec('Use database') does not affect the statement immediately after it as I would have thought it is executing on the target database but it doesn't. Is there any way of outputting the actual script that will run when the stored proc is executed?

    There's probably a more technical explanation, but the way I think of it is that everything in a single EXEC is run as a batch within its own context - and which therefore does not affect the context of the script which calls it.

    --Edit: 'Context', not 'content'!

  • October 24, 2011 at 3:05 am

    #1398551

    thulani.moyana 79207 (10/24/2011)

    I'm wondering why the single exec('Use database') does not affect the statement immediately after it as I would have thought it is executing on the target database but it doesn't.

    Dynamic SQL runs in a different scope to the rest of the procedure. Hence, if you need to do something like that, you need to do all of it in dynamic SQL. Double exec is not necessary, one will work.

    EXEC ('Use Database; Disable Trigger.... ');

    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

  • October 24, 2011 at 3:24 am

    #1398558

    Cool, I think I understand the logic now. Thanks again for your assistance, keep up the good spirit of extendin your hands.

  • October 24, 2011 at 4:42 am

    #1398591

    GilaMonster (10/24/2011)

    thulani.moyana 79207 (10/24/2011)

    I'm wondering why the single exec('Use database') does not affect the statement immediately after it as I would have thought it is executing on the target database but it doesn't.

    Dynamic SQL runs in a different scope to the rest of the procedure. Hence, if you need to do something like that, you need to do all of it in dynamic SQL. Double exec is not necessary, one will work.

    EXEC ('Use Database; Disable Trigger.... ');

    My bad on the double exec. It is required in some context but not this one.

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

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

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy