Menu
I am new to SQL Server Express and I want to create a new trigger on my table.
But I found that the 'New trigger' button in my table's trigger folder shows Disabled. When I right click on trigger folder it shows 'New trigger' button disabled and in gray color.
I am using SQL Server 2014 Express and Management Studio 2014.
Does anybody have any idea why it is happening?
Please suggest me some way out.
439
Triggers are enabled by default when they are created. Disabling a trigger does not drop it. The trigger still exists as an object in the current database.
Enabling and disabling DML triggers on a table. Navigate to triggers folder at the table level, select the trigger, Right click on trigger and Click on Enable/Disable to Enable or disable the trigger using SSMS. Disabling specific SQL Server trigger on a table using T-SQL.
To enable a trigger, causes it to fire when any Transact-SQL statements on which it was originally programmed are run. Triggers are disabled by using DISABLE TRIGGER. DML triggers defined on tables can also be disabled or enabled by using ALTER TABLE.
You can use the is_disabled column in sys. triggers table to filter the records according to your requirement.
This a known issue (bug) with SSMS 2014.
Reference : https://connect.microsoft.com/SQLServer/feedback/details/873249/ssms-2014-new-trigger-is-disabled-for-older-2012-2008r2-versions-of-sql-server
Alternately, you can use something like :
CREATE TRIGGER TriggerName
ON [dbo].[TableName]
FOR DELETE, INSERT, UPDATE
AS
BEGIN
SET NOCOUNT ON
END
I know this post is older but I came across it when I was trying to use SQL Server Management Studio (SSMS) 2014 (12.0.5000.0) and found the New Trigger option grayed out when I was connecting to a SQL Server 2008 R2 Enterprise Database. After further investigation Microsoft states that SSMS versions after 2014 SP1 (Not including SP1) have a fix for this. Unfortunately, I did not find an update for SSMS 2014 that fixes this but posted by Ayo Olubeko on 4/8/2016, instructs people here (comments section) to download the latest version from Microsoft's download site that it now has a standalone installer and can easily be updated to resolve this specific issue. Previous versions such as the June 2016 release should have this fixed and can be found (https://learn.microsoft.com/en-us/sql/ssms/previous-sql-server-management-studio-releases) but I decided to download the latest.
After downloading the 898MB installer for SSMS 2016 (13.0.16106.4) it appears that this option is no longer grayed out when I am connecting to the same database!
I hope this helps others who just have not made the jump to the latest SSMS yet like me. Note that this does just open the template file that Vishal Gajjar mentioned in the comments of the question, and for me is not auto-populating the Table Name or any fields, but I also don't know if it is supposed to. The interface looks similar so I don't see any reason why I am not going to be able to use this to replace and uninstall the 2014 version to get all of the updates and improvements.
3
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
