Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSMS Scripting disable trigger?


SSMS Scripting disable trigger?

Author
Message
Deki
Deki
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 264
You can script a trigger, but is there a way to script disable trigger from ssms if trigger IS disabled?
Jason Selburg
Jason Selburg
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2803 Visits: 4101
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
Deki
Deki
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 264
Ok, I know about that, but the question was: can you script DISABLE TRIGGER from SSMS?
Sean Pearce
Sean Pearce
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1170 Visits: 3432
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
Venkataraman R
Venkataraman R
SSC-Enthusiastic
SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)

Group: General Forum Members
Points: 133 Visits: 214
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
Sean Pearce
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1170 Visits: 3432
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
Deki
Deki
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 264
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search