29

I'm trying to run this command in SQL Server:

disable trigger all on MYDB

This is failing for me. The account I'm logged into has access to MYDB and I've pretty much giving it every single permission available (it's a local DB and my account only, so this is OK). I don't understand why it's telling me it can't find MYDB for this? I've done this before. Also note: I can select from the database, update, and run a grant statement (such as granting execution of a proc). I can also disable triggers manually...

So why does this fail? I was able to do it before...

Thanks.

Brian Mains
  • 50,520
  • 35
  • 148
  • 257

3 Answers3

92
sp_msforeachtable 'ALTER TABLE ? DISABLE TRIGGER all'

To enable all triggers, you can use following statement

sp_msforeachtable 'ALTER TABLE ? ENABLE TRIGGER all'
Knaģis
  • 20,827
  • 7
  • 66
  • 80
Derek
  • 21,828
  • 7
  • 53
  • 61
  • 3
    Note - if your database is case sensitive then you'll have to use sp_MSforeachtable – Crab Bucket Oct 10 '12 at 14:38
  • 1
    I had to use `exec` in front of the stored proc, for example `exec sp_msforeachtable 'ALTER TABLE ? DISABLE TRIGGER all';` – styfle Sep 28 '16 at 19:24
  • Worked! One note though. In Azure SQL there is no sp_msforeachtable, so you'll have to create it. Look into https://gist.githubusercontent.com/metaskills/893599/raw/003b0d5ffa8e9aa6d1f600a89ff5f60438044c5c/gistfile1.sql – Alfredo A. Mar 28 '17 at 20:16
0
use MYDB;
disable trigger all on DATABASE;
sloth
  • 99,095
  • 21
  • 171
  • 219
Badiboy
  • 1,519
  • 1
  • 18
  • 31
  • 1
    Yes, that makes sense, but for some reason, all triggers are enabled.... strange. – Brian Mains Aug 24 '11 at 15:19
  • 1
    Strange... This usage is declared by Microsoft: http://msdn.microsoft.com/en-us/library/ms189748.aspx , I just correct your syntax. – Badiboy Aug 26 '11 at 08:57
  • I know, I thought so to. It does work, but refresh and the triggers are still enabled... not sure why... – Brian Mains Aug 26 '11 at 12:18
  • 11
    Looking at those docs, I'm guessing this will only work for triggers that are declared on a database scope, and not those which are declared on table events. – StriplingWarrior Mar 06 '14 at 19:59
0

Not sure why Yuck deleted their answer. From DISABLE TRIGGER:

object_name Is the name of the table or view on which the DML trigger trigger_name was created to execute.

That is, you can't provide a database name to this statement. Whilst the MYDB database exists, there isn't an object within it called MYDB.

Damien_The_Unbeliever
  • 234,701
  • 27
  • 340
  • 448
  • Yuck removed his answer because of my comment. DISABLE TRIGGER ALL on DATABASE doesn't do anything for some reason... says success, but all triggers are enabled. But yes structurally that makes sense with DISABLE TRIGGER. – Brian Mains Aug 24 '11 at 15:18