Export (0) Print
Expand All
ABS
ALL
AND
ANY
AVG
bit
COS
COT
DAY
EXP
GO
IN
LEN
LOG
MAX
MIN
NOT
OR
PI
SET
SIN
STR
SUM
TAN
USE
VAR
Expand Minimize

ALTER TRIGGER

SQL Server 2000

  Topic last updated -- June 2007

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

Indicates that SQL Server will convert the original text of the trigger definition to an obfuscated format. Note that obfuscated triggers can be reverse engineered because SQL Server must de-obfuscate triggers for execution. In SQL Server 2000, the obfuscated text is visible in the syscomments system table and may be susceptible to de-obfuscation attempts.

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)
GO
-- 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.

See Also

DROP TRIGGER

Programming Stored Procedures

sp_addmessage

Transactions

Using Identifiers

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft