Practice Modifying Triggers in SQL-Server 2022

Earlier in this module, we created the trgSalary trigger to enforce a business rule in which no employee is allowed to have a salary greater than $200,000 unless authorized by the employee's manager (with the manager’s employee ID entered as part of the transaction). The company wishes to change this business rule so that authorization will now be required for any salary greater than $150,000, as opposed to $200,000 (I guess too many people were being paid too much money). Remember that the Employees table contains these columns

Column name	Data type
EmployeeID	int
Salary	smallmoney
HireDate	smalldatetime
ApprovalID	int

Updated Transact-SQL trigger optimized for SQL Server 2022:

ALTER TRIGGER trgSalary ON employees
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;

    IF EXISTS (
        SELECT 1
        FROM INSERTED
        WHERE Salary > 150000 AND ApprovalID IS NULL
    )
    BEGIN
        DELETE e
        FROM employees e
        INNER JOIN INSERTED i ON e.EmployeeID = i.EmployeeID
        WHERE e.Salary > 200000 AND e.ApprovalID IS NULL;

        THROW 51000, 'ApprovalID is required for salaries exceeding $150,000.', 1;
    END
END

Key characteristics of this version:

• Uses AFTER INSERT to clearly define the timing of the trigger.
• Uses SET NOCOUNT ON; to suppress row count messages for better performance.
• Uses THROW for modern, structured error reporting.
• Uses EXISTS for an efficient conditional check.
• Preserves atomic logic suitable for transactional enforcement.

ALTER TRIGGER trgSalary ON employees
INSTEAD OF INSERT
AS
BEGIN
    SET NOCOUNT ON;

    -- Check for any inserted rows with Salary > 150000 and missing ApprovalID
    IF EXISTS (
        SELECT 1
        FROM INSERTED
        WHERE Salary > 150000 AND ApprovalID IS NULL
    )
    BEGIN
        THROW 51000, 'ApprovalID is required for salaries exceeding $150,000.', 1;
        RETURN;
    END

    -- Insert only valid rows
    INSERT INTO employees (EmployeeID, Salary, ApprovalID /*, other columns if applicable */)
    SELECT EmployeeID, Salary, ApprovalID /*, other columns */
    FROM INSERTED;
END

Notes for Implementation:

• Replace /*, other columns if applicable */ with the actual column list if the employees table has more columns.
• This trigger blocks invalid inserts entirely rather than allowing them and then deleting, which aligns with data integrity best practices.
• Valid rows are passed through from the INSERTED pseudo-table.

Modifies the definition of a DML, DDL, or logon trigger that was previously created by the CREATE TRIGGER statement. Triggers are created by using CREATE TRIGGER. They can be created directly from Transact-SQL statements or from methods of assemblies that are created in the Microsoft .NET Framework common language runtime (CLR) and uploaded to an instance of SQL Server. For more information about the parameters that are used in the ALTER TRIGGER statement, see CREATE TRIGGER (Transact-SQL).
ALTER TRIGGER applies to: SQL Server (SQL Server 2008 through current version), Azure SQL Database.

Here is the **rewritten SQL Server trigger syntax**, fully conforming to **SQL Server 2022** standards, structured cleanly into DML, DDL, and LOGON trigger sections:
✅ **1. DML Trigger Syntax (INSERT, UPDATE, DELETE on Table or View)**

ALTER TRIGGER [schema_name].[trigger_name]
ON { table_name | view_name }
[ WITH <dml_trigger_option> [ ,...n ] ]
{ AFTER | INSTEAD OF }
{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }
[ NOT FOR REPLICATION ]
AS
BEGIN
    { sql_statement [ ; ] [ ...n ] }
END

<dml_trigger_option> ::= 
    ENCRYPTION
  | EXECUTE AS { CALLER | SELF | OWNER | 'user_name' }

✅ **2. DDL Trigger Syntax (CREATE, ALTER, DROP, GRANT, etc.)**

ALTER TRIGGER [trigger_name]
ON { DATABASE | ALL SERVER }
[ WITH <ddl_trigger_option> [ ,...n ] ]
AFTER { event_type [ ,...n ] | event_group }
AS
BEGIN
    { sql_statement [ ; ] [ ...n ] }
END

<ddl_trigger_option> ::= 
    ENCRYPTION
  | EXECUTE AS { CALLER | SELF | OWNER | 'user_name' }

✅ **3. LOGON Trigger Syntax (Logon Event Only on ALL SERVER)**

ALTER TRIGGER [trigger_name]
ON ALL SERVER
[ WITH <logon_trigger_option> [ ,...n ] ]
AFTER LOGON
AS
BEGIN
    { sql_statement [ ; ] [ ...n ] }
END

<logon_trigger_option> ::= 
    ENCRYPTION
  | EXECUTE AS { CALLER | SELF | OWNER | 'user_name' }

✅ Optional: External Method Specification (for CLR triggers)
If the trigger uses CLR (Common Language Runtime):

EXTERNAL NAME [assembly_name].[class_name].[method_name]

🔍 Notes:

• AFTER triggers are default for DDL and LOGON events.
• INSTEAD OF is only valid for DML triggers on tables/views.
• Use BEGIN ... END to encapsulate SQL statements for clarity and maintainability.
• The EXECUTE AS clause ensures security context is explicitly defined.

Click the Exercise link below to practice modifying triggers.
Modifying Triggers - Exericse
In the next lesson, you will learn how to delete a trigger.