Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SSMS Scripting disable trigger? Expand / Collapse
Author
Message
Posted Friday, October 25, 2013 4:00 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 4:24 AM
Points: 9, Visits: 215
You can script a trigger, but is there a way to script disable trigger from ssms if trigger IS disabled?
Post #1508389
Posted Friday, October 25, 2013 12:01 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, September 19, 2014 7:50 PM
Points: 2,717, Visits: 3,854
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 you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg
Post #1508559
Posted Saturday, October 26, 2013 10:48 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 4:24 AM
Points: 9, Visits: 215
Ok, I know about that, but the question was: can you script DISABLE TRIGGER from SSMS?
Post #1508666
Posted Monday, October 28, 2013 4:21 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 7:46 AM
Points: 914, Visits: 2,876
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

@SeanPearceSQL

About Me
Post #1508789
Posted Monday, October 28, 2013 6:50 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 6:24 AM
Points: 131, Visits: 165
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.
Post #1508848
Posted Monday, October 28, 2013 7:11 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 7:46 AM
Points: 914, Visits: 2,876
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

@SeanPearceSQL

About Me
Post #1508856
Posted Monday, October 28, 2013 7:48 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 4:24 AM
Points: 9, Visits: 215
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.
Post #1508887
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse