ALTER TRIGGER

Modify the definition of a DML or DDL trigger.

Syntax for DML Trigger (INSERT, UPDATE, or DELETE)

      ALTER TRIGGER [ schema.] trigger
         ON { table | view } 
            [WITH trigger_option [ ,...n ] ]
               {FOR | AFTER | INSTEAD OF} 
                  { [INSERT] [, ] [UPDATE] [, ] [DELETE]] } 
                     [NOT FOR REPLICATION]
                        AS { sql_statement  [;] [,...n] | EXTERNAL NAME assembly.class.method [;] }


Syntax for DDL Trigger (CREATE, ALTER, DROP, GRANT, DENY, REVOKE, or UPDATE STATISTICS)

      ALTER TRIGGER trigger 
         ON { ALL SERVER | DATABASE } 
            [WITH trigger_option [,...n] ]
               {FOR | AFTER } { event_type | event_group } [,...n]
                  AS { sql_statement  [;] [,...n] | EXTERNAL NAME assembly.class.method  [;] }

trigger_option:
    [ ENCRYPTION ]
    [ EXECUTE AS Clause ]

Arguments:
table / view - The table or view on which the DML trigger is executed (trigger table.)
DATABASE - Apply the scope of a DDL trigger to the current database.
ALL SERVER - Apply the scope of a DDL trigger to the current server.
WITH ENCRYPTION - Encrypt the text of the CREATE TRIGGER statement.
EXECUTE AS - The security context under which the trigger will execute.
AFTER - The DML trigger will fire after the triggering SQL statement has executed successfully.(default)
INSTEAD OF - The DML trigger is executed instead of the triggering SQL statement.
DELETE / INSERT/UPDATE - The statements that activate the DML trigger.
event_type - Name of a T-SQL language event (Create../Alter../Drop..) that, after execution, will cause a DDL trigger to fire.
event_group - Name of a predefined grouping of T-SQL language events. Trigger fires after execution of any event in the group.
sql_statement - The trigger conditions and actions.

Example

ALTER TRIGGER MyTrigger
ON MySchema.MyTable
AFTER INSERT, UPDATE
AS
   EXEC msdb.dbo.sp_send_dbmail
        @profile_name = 'Database Administrator',
        @recipients = 'name@somewhere.com',
        @body = 'email message goes here.',
        @subject = 'New record added';
GO

# When they kick at your front door, How you gonna come?
With your hands on your head, Or on the trigger of your gun # ~ The Clash, London Calling (The Guns Of Brixton)

Related commands:

CREATE TRIGGER
ENABLE TRIGGER
DISABLE TRIGGER
DROP TRIGGER
sys.triggers
sp_settriggerorder
CREATE TABLE
COLUMNS_UPDATED
TRIGGER_NESTLEVEL
EVENTDATA
Equivalent Oracle command: ALTER TRIGGER