Venkataraman R (10/28/2013)
You can disable trigger using "Disable" FROM Context menu of a trigger in SSMS object explorer. But, you cannot script "DISABLE TRIGGER" from SSMS. You will be able to Script out CREATE, ALTER, DROP etc. but not DISABLE.
Of course you can script "DISABLE TRIGGER" from SSMS.
CREATE TABLE Scratch (ID INT);
GO
CREATE TRIGGER trTest ON Scratch
FOR INSERT
AS
SELECT COUNT(*) FROM Scratch;
GO
SELECT is_disabled FROM sys.triggers WHERE name = 'trTest';
GO
DECLARE @SQL NVARCHAR(MAX);
SELECT @SQL = 'DISABLE TRIGGER ' + QUOTENAME(name) + ' ON ' + QUOTENAME(OBJECT_NAME(parent_id)) FROM sys.triggers WHERE name = 'trTest';
EXEC sp_executesql @SQL;
GO
SELECT is_disabled FROM sys.triggers WHERE name = 'trTest';
GO
DROP TABLE Scratch;
GO
