SQL-Server Triggers  «Prev  Next»

Lesson 7 Modifying triggers
Objective Practice modifying triggers.

Modifying triggers in SQL-Server 2016

If your business rules change, you might need to modify your triggers, using either Enterprise Manager or Transact-SQL.

Using Enterprise Manager

To use Enterprise Manager to modify your triggers:
  1. Right-click the name of the table that the trigger is assigned to.
  2. Choose the All Tasks | Manage Triggers menu option, and select the trigger to modify from the drop-down list.
  3. Enter the appropriate Transact-SQL statements in the Trigger Properties dialog box, shown below:

Using Transact-SQL

Use the ALTER TRIGGER statement to modify a trigger. The syntax for this statement is shown in the following series of images.

trigger_name is the name of the trigger you are going to modify. A trigger is a database object, so it must have a unique name within the database.
1) trigger_name is the name of the trigger you are going to modify. A trigger is a database object, so it must have a unique name within the database.

table is the name of the table on which you will modify the trigger.
2) table is the name of the table on which you will modify the trigger.

WITH ENCRYPTION encrypts the text so that it can not be read from the syscomments table..
3) WITH ENCRYPTION encrypts the text so that it can not be read from the syscomments table.

trigger_type is the type of trigger you wish the trigger to become. The type can be INSERT, UPDATE, or DELETE, depending on the type you want the trigger to become.
4) trigger_type is the type of trigger you wish the trigger to become. The type can be INSERT, UPDATE, or DELETE, depending on the type you want the trigger to become.

WITH APPEND is an optional statement that indicates to SQL Server that if a trigger of the same type already exists, this trigger should be added
5) WITH APPEND is an optional statement that indicates to SQL Server that if a trigger of the same type already exists, this trigger should be added.

NOT FOR REPLICATION indicates that any replication operations that affect data in the table should not fir the trigger. The code should affect the data anyway,
6) NOT FOR REPLICATION indicates that any replication operations that affect data in the table should not fir the trigger. The code should affect the data anyway,

column is used if you wish to test for a specific column to have data inserted or updated into it. Multiple columns can be specified.
7) column is used if you wish to test for a specific column to have data inserted or updated into it. Multiple columns can be specified.

Bitwise_operator, updated_bitmask, comparison_operator, and column_bitmask all are affected by the COLUMNS_UPDATED clause, which is used to test a bit-filed representing one or more columns that are updated.
8) Bitwise_operator, updated_bitmask, comparison_operator, and column_bitmask all are affected by the COLUMNS_UPDATED clause, which is used to test a bit-filed representing one or more columns that are updated.

trigger_code is the Transact-SQL code that you will use to implement your business rules, using the INSERTED and DELETED special trigger tables.
9) trigger_code is the Transact-SQL code that you will use to implement your business rules, using the INSERTED and DELETED special trigger tables.


The second way to modify a trigger is by using Transact-SQL code. As you probably noticed, the syntax for ALTER TRIGGER is almost the same as the syntax for CREATE TRIGGER. Therefore, an alternative to modifying your triggers is to delete and re-create them. Be aware that if you do so, you will lose any reference to the trigger from dependency objects, as well as any permissions assigned to that object.
In the next lesson, you will learn how to modify a trigger so that it conforms to unique business rules.

SEMrush Software