Enable/Disable Trigger

  • 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.

  • Sorted, thanks.

    Database name can be prefixed on the table name.

  • 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.

  • 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
  • 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

  • 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.

  • 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'!


  • 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
  • Cool, I think I understand the logic now. Thanks again for your assistance, keep up the good spirit of extendin your hands.

  • 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