ALTER TRIGGER (T-SQL)

Alters the definition of a trigger created previously by the CREATE TRIGGER statement. For more information about the parameters used in the ALTER TRIGGER statement, see CREATE TRIGGER.

Syntax

ALTER TRIGGER trigger_name
ON table
[WITH ENCRYPTION]
{
    {FOR { [DELETE] [,] [UPDATE] [,][INSERT] }
        [NOT FOR REPLICATION]
        AS
            sql_statement [...n]
    }
    |
    {FOR { [INSERT] [,] [UPDATE] }
        [NOT FOR REPLICATION]
        AS
        {    IF UPDATE (column)
            [{AND | OR} UPDATE (column)]
                [...n]
            | IF (COLUMNS_UPDATED() {bitwise_operator} updated_bitmask)
                { comparison_operator} column_bitmask [...n]
        }
                sql_statement [...n]
    }
}

Arguments

trigger_name: Is the name of the existing trigger to alter.
table: Is the table on which the trigger is executed.
WITH ENCRYPTION: Encrypts the syscomments entries that contain the text of the ALTER TRIGGER statement.

Note If a previous trigger definition was created using WITH ENCRYPTION or RECOMPILE, these options are only enabled if they are included in ALTER TRIGGER.

FOR { [DELETE] [,] [UPDATE] [,][INSERT] } | { [INSERT] [,] [UPDATE] }: Specifies which data modification statements, when attempted against this table, activate the trigger. At least one option must be specified. Any combination of these, in any order is allowed in the trigger definition. If more than one option is specified, separate the options with commas.

Note If the ALTER TRIGGER statement specifies {INSERT, UPDATE, DELETE} | {INSERT, UPDATE} trigger information different from the original CREATE TRIGGER statement, the ALTER TRIGGER statement overrides the behavior specified in CREATE TRIGGER. For example, if an UPDATE trigger is altered to an INSERT trigger and a different INSERT trigger already exists, then an additional INSERT trigger is created.
NOT FOR REPLICATION: Indicates that the trigger should not be executed when a replication login such as sqlrepl modifies the table involved in the trigger.
AS: Are the actions the trigger is to take.
sql_statement: Is the trigger condition or conditions and action or actions.
n: Is a placeholder indicating that multiple Transact-SQL statements can be included in the trigger.
IF UPDATE (column): Tests for an INSERT or UPDATE action to a specified column and is not used with DELETE operations.
UPDATE(column) can be used anywhere inside the body of the trigger.
{AND | OR}: Specifies another column to test for either an INSERT or UPDATE action.
column: Is the name of the column to test for either an INSERT or UPDATE action.
IF (COLUMNS_UPDATED()): Tests to see, in an INSERT or UPDATE trigger only, whether the mentioned column or columns were inserted or updated. COLUMNS_UPDATED returns a varbinary bit pattern that indicates which columns of the table were inserted or updated.
COLUMNS_UPDATED can be used anywhere inside the body of the trigger.
bitwise_operator: Is the bitwise operator to use in the comparison.
updated_bitmask: Is the integer bitmask of those columns actually updated or inserted. For example, table t1 contains columns C1, C2, C3, C4, and C5. To check whether columns C2, C3, and C4 are all updated (with table t1 having an UPDATE trigger), specify a value of 14. To check whether only C2 is updated, specify a value of 2.
comparison_operator: Is the comparison operator. Use the equal sign (=) to check whether all columns specified in updated_bitmask are actually updated. Use the greater than symbol (>) to check whether any or not all columns specified in the updated_bitmask are updated.
column_bitmask: Is the integer bitmask of the columns to check.

Remarks

For more information about ALTER TRIGGER, see Remarks in CREATE TRIGGER.

Note Because Microsoft does not support the addition of user-defined triggers on system tables, it is recommended that no user-defined triggers be created on system tables.

Permissions

ALTER TRIGGER permissions default to members of the db_owner and db_ddladmin fixed database roles, and to the table owner. These permissions are not transferable.

Examples

This example creates a trigger that prints a user-defined message to the client when a user tries to add or change data in the roysched table. Then, the trigger is altered using ALTER TRIGGER to apply the trigger only on INSERT activities. This trigger is helpful because it reminds the user who updates or inserts rows into this table to also notify the book authors and publishers.

USE pubs

GO

CREATE TRIGGER royalty_reminder

ON roysched

WITH ENCRYPTION

FOR INSERT, UPDATE

AS RAISERROR (50009, 16, 10)

-- Now, alter the trigger.