SQL-Server Triggers  «Prev  Next»

Lesson 7 Modifying triggers
Objective Practice modifying triggers in SQL Server 2022

Modifying triggers in SQL-Server 2022

If your business rules change, you might need to modify your triggers, using either SSMS or Transact-SQL.
If my business rules change, you might need to modify your triggers, using either SSMS 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.

Transact-SQL Code (Trigger Alteration Template):
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.
The uploaded image contains a template structure for altering an existing trigger in Transact-SQL (T-SQL). Here is the transcribed and formatted SQL code: 
ALTER TRIGGER trigger_name 
ON table
[WITH ENCRYPTION]
{
    FOR trigger_type
        [WITH APPEND]
        [NOT FOR REPLICATION]
    AS
    {
        IF UPDATE (column)
            [[AND | OR] UPDATE (column)]
            [...n]

        IF (COLUMNS_UPDATED() {bitwise_operator} updated_bitmask)
            {comparison_operator} column_bitmask
            [...n]

        trigger_code [...n]
    }
}

🧠 Explanation of Components:
  • ALTER TRIGGER: Used to modify an existing trigger.
  • trigger_name: The name of the trigger you want to modify.
  • ON table: The target table for the trigger.
  • WITH ENCRYPTION: Optionally hides the trigger logic.
  • FOR trigger_type: Specifies operations (INSERT, UPDATE, DELETE).
  • WITH APPEND: Appends trigger logic to existing logic (less commonly used).
  • NOT FOR REPLICATION: Prevents the trigger from firing during replication.
  • IF UPDATE (column): Checks if a specific column was updated.
  • COLUMNS_UPDATED(): Returns a bitmask indicating updated columns.
  • trigger_code [...n]: Placeholder for the trigger's business logic.
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.
`WITH ENCRYPTION` is still used in SQL Server 2022.
It serves the same purpose as in earlier versions: to encrypt the entries for the trigger in the SQL Server system tables. This prevents the trigger's definition from being directly viewed using system catalog views or functions. So, the syntax you provided is still valid and functional in SQL Server 2022 for encrypting the trigger definition.

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.
`WITH APPEND` is still supported and can be used in SQL Server 2022**.
However, it's important to understand its functionality and why it's **less commonly used** in modern SQL Server development.
When used with `ALTER TRIGGER`, `WITH APPEND` attempts to add the new trigger logic **after** the existing trigger logic for the specified `trigger_type` on the table.
Why it's less commonÖ
  • Complexity and Maintenance: Appending trigger logic can make triggers harder to understand and maintain. It can become difficult to trace the order of operations and the overall effect of the trigger, especially if multiple ALTER TRIGGER ... WITH APPEND statements have been executed over time.
  • Unpredictable Order: The exact order in which the appended logic is executed relative to the original logic might not always be immediately obvious or easily controlled. This can lead to unexpected behavior.
  • Best Practice: Modify Existing Logic Directly: The generally recommended approach is to retrieve the existing trigger definition (using tools like SSMS or system views like sys.sql_modules), incorporate the new logic directly into the existing code, and then use ALTER TRIGGER without WITH APPEND to replace the entire trigger definition. This promotes better organization and clarity.
  • Version Control Benefits: Modifying the entire trigger and redeploying it aligns better with version control practices, allowing you to track changes to the complete trigger definition.

In summaryÖ While `WITH APPEND` is still a valid syntax in SQL Server 2022, it's often discouraged in favor of directly modifying and replacing the entire trigger definition for improved maintainability and predictability. You might encounter it in older codebases, but for new development, the direct modification approach is generally preferred.

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