SSMS Scripting disable trigger?

Question

Post reply

SSMS Scripting disable trigger?

Deki

SSC Veteran

Points: 275
More actions
October 25, 2013 at 4:00 am

#281704

You can script a trigger, but is there a way to script disable trigger from ssms if trigger IS disabled?

Viewing 7 posts - 1 through 6 (of 6 total)

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

Jason Selburg SSC-Insane Points: 24641 More actions · Answer 1

Search BOL for DISABLE TRIGGER... 🙂

and to check if enabled or disabled see ...

SELECT * FROM sys.triggers

______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg

Deki SSC Veteran Points: 275 More actions · Answer 2

Ok, I know about that, but the question was: can you script DISABLE TRIGGER from SSMS?

Sean Pearce SSCoach Points: 15999 More actions · Answer 3

Deki (10/26/2013)
Ok, I know about that, but the question was: can you script DISABLE TRIGGER from SSMS?

Yes you can

Jason
Search BOL for DISABLE TRIGGER... Smile

BOL will give you the syntax to use

Jason
and to check if enabled or disabled see ...
SELECT * FROM sys.triggers

sys.triggers willl give you the content to use

The SQL Guy @ blogspot[/url]

@SeanPearceSQL

About Me[/url]

Venkataraman R SSChasing Mays Points: 634 More actions · Answer 4

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.

Sean Pearce SSCoach Points: 15999 More actions · Answer 5

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

The SQL Guy @ blogspot[/url]

@SeanPearceSQL

About Me[/url]

Deki SSC Veteran Points: 275 More actions · Answer 6

Yup Journeyman, this is the answer to my question.

Sorry guys if the question was ambigous...I know how to write sql to "script" disable trigger (that's why I didn't post it in tsql section), but the question was (again sorry if I was ambigous) can SSMS with its scripting options do this....My opinion is that if the option in "Tools > Options > SQL Server Object Explorer > Scripting options > Script triggers" is set to True, it should also script disable trigger if the trigger in question is disabled.