ALTER TRIGGER
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 | view )
[ WITH ENCRYPTION ]
{
{ ( FOR | AFTER | INSTEAD OF ) { [ DELETE ] [ , ] [ INSERT ] [ , ] [ UPDATE ] }
[ NOT FOR REPLICATION ]
AS
sql_statement [ ...n ]
}
|
{ ( FOR | AFTER | INSTEAD OF ) { [ 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 existing trigger to alter.
table | view
Is the table or view on which the trigger is executed.
WITH ENCRYPTION
Encrypts the syscomments entries that contain the text of the ALTER TRIGGER statement. Using WITH ENCRYPTION prevents the trigger from being published as part of SQL Server replication.
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.
AFTER
Specifies that the trigger is fired only after the triggering SQL statement is executed successfully. All referential cascade actions and constraint checks also must have been successful before this trigger executes.
AFTER is the default, if only the FOR keyword is specified.
AFTER triggers may be defined only on tables.
INSTEAD OF
Specifies that the trigger is executed instead of the triggering SQL statement, thus overriding the actions of the triggering statements.
At most, one INSTEAD OF trigger per INSERT, UPDATE, or DELETE statement can be defined on a table or view. However, it is possible to define views on views where each view has its own INSTEAD OF trigger.
INSTEAD OF triggers are not allowed on views created with WITH CHECK OPTION. SQL Server will raise an error if an INSTEAD OF trigger is added to a view for which WITH CHECK OPTION was specified. The user must remove that option using ALTER VIEW before defining the INSTEAD OF trigger.
{ [DELETE] [,] [INSERT] [,] [UPDATE] } | { [INSERT] [,] [UPDATE]}
Are keywords that specify which data modification statements, when attempted against this table or view, 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.
For INSTEAD OF triggers, the DELETE option is not allowed on tables that have a referential relationship specifying a cascade action ON DELETE. Similarly, the UPDATE option is not allowed on tables that have a referential relationship specifying a cascade action ON UPDATE. For more information, see ALTER TABLE.
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(s) and action(s).
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.
ALTER TRIGGER supports manually updateable views through INSTEAD OF triggers on tables and views. Microsoft® SQL Server™ applies ALTER TRIGGER the same way for all types of triggers (AFTER, INSTEAD-OF).
The first and last AFTER triggers to be executed on a table may be specified by using sp_settriggerorder. Only one first and one last AFTER trigger may be specified on a table; if there are other AFTER triggers on the same table, they will be executed in an undefined sequence.
If an ALTER TRIGGER statement changes a first or last trigger, the first or last attribute set on the modified trigger is dropped, and the order value must be reset with sp_settriggerorder.
An AFTER trigger is executed only after the triggering SQL statement, including all referential cascade actions and constraint checks associated with the object updated or deleted, is executed successfully. The AFTER trigger operation checks for the effects of the triggering statement as well as all referential cascade UPDATE and DELETE actions caused by the triggering statement.
When a DELETE action to a child or referencing table is the result of a CASCADE on a DELETE from the parent table, and an INSTEAD OF trigger on DELETE is defined on that child table, the trigger is ignored and the DELETE action is executed.
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.
USE pubs
GO
ALTER TRIGGER royalty_reminder
ON roysched
FOR INSERT
AS RAISERROR (50009, 16, 10)
Message 50009 is a user-defined message in sysmessages. For more information about creating user-defined messages, see sp_addmessage.