SQL Server Express 'NEW TRIGGER' BUTTON IS

Question

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.

Answer 1

SSMS packaged in the Express versions are a subset of the full feature set of SSMS.

There are several other options that don't exists, including the GUI to enable and disable triggers. I believe control over the SQL agent is another one of them. If you connect to the Express DB engine with full version of SSMS the option would be present.

If you need that option try downloading the full trial version or developer version of SQL and install the SSMS that comes packaged with the full version. Brad

Answer 2

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://docs.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.

Answer 3

It is worth noting that you can generate the skeleton script for creating the trigger through Visual Studio.

Simply make a connection to the database using server explorer, expand the tables folder, right-click on any table and hit New Trigger.

Answer 4

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