Modify or Delete Triggers in SQL Server

Here you will learn how to modify and delete triggers in SQL Server.

The ALTER TRIGGER statement is used to modify the definition of an existing trigger without altering the permissions or dependencies.

ALTER TRIGGER trigger_name   
ON { Table name or view name }   
[ WITH  ]  
{ FOR | AFTER | INSTEAD OF }   
{ [INSERT], [UPDATE] , [DELETE] }   
AS
    sql_statements 

In the above ALTER TRIGGER statement:

• trigger_name is the trigger you wish to alter.
• ON specifies the table or view on which the trigger is created
• FOR indicates when the trigger must fire when an event happens
• [INSERT], [UPDATE], [DELETE] specifies the list of events that will cause the trigger to fire.

The following modifies a DDL trigger.

ALTER TRIGGER trgTablechanges
ON DATABASE
FOR	
    CREATE_TABLE,
    DROP_TABLE
AS
BEGIN
    SET NOCOUNT ON;
    INSERT INTO TableLog
    (
       EventVal,
       DateChanged,
       ChangedBy
     )
    VALUES (
        EVENTDATA(),
        GETDATE(),
        USER
    );
END;

Delete a Trigger

DROP TRIGGER statement drops one or more triggers from the database. You can delete multiple triggers using the DROP TRIGGER statement by specifying the trigger names separated by a comma.

DROP TRIGGER [IF EXISTS] [schema_name.]trigger1, trigger2, ... ];

For example, the below SQL statement drops the DML trigger trgEmployeeUpdate.

DROP TRIGGER dbo.trgEmployeeUpdate

Delete DDL or LOGON Triggers

Use the DROP TRIGGER ON statement to delete DDL or LOGON triggers.

DROP TRIGGER [ IF EXISTS ] trigger_name [ ,...n ]   
ON { DATABASE | ALL SERVER };

For example, the below SQL statement drops the DDL trigger trgTablechanges using ON DATABASE option because it is a database level trigger.

DROP TRIGGER IF EXISTS trgTablechanges ON DATABASE;

The following drops the LOGON trigger trgLoginConnection using ON ALL SERVER option because it is the server level trigger.

DROP TRIGGER IF EXISTS trgLoginConnection ON ALL Server;