Disable trigger - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

Disable trigger

Author

Topic

jhunu
Starting Member

18 Posts

Posted - 2011-12-17 : 00:29:23

Query to Disable a Trigger

pnash
Starting Member

26 Posts

Posted - 2011-12-17 : 00:38:20

You can simply get the answer in BOL.

USE AdventureWorks2008R2;
GO
DISABLE TRIGGER Person.uAddress ON Person.Address;
GO

B. Disabling a DDL trigger

The following example creates a DDL trigger safety with database scope, and then disables it.

SQL
IF EXISTS (SELECT * FROM sys.triggers
WHERE parent_class = 0 AND name = 'safety')
DROP TRIGGER safety ON DATABASE;
GO
CREATE TRIGGER safety
ON DATABASE
FOR DROP_TABLE, ALTER_TABLE
AS
PRINT 'You must disable Trigger "safety" to drop or alter tables!'
ROLLBACK;
GO
DISABLE TRIGGER safety ON DATABASE;
GO

C. Disabling all triggers that were defined with the same scope

The following example disables all DDL triggers that were created at the server scope.

SQL
USE AdventureWorks2008R2;
GO
DISABLE Trigger ALL ON ALL SERVER;
GO

kfluffie
Posting Yak Master

103 Posts

Posted - 2011-12-17 : 01:01:09

Or disable it in SSMS.
1. "Expand" your database
2. Click down to triggers
3. Choose your trigger, Right-Click and choose "Disable"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources