Practice creating SQL-Server Triggers

Suppose that your company has a business rule that no employee is allowed to have a salary greater than $200,000 unless the authorizing manager's employee ID has been entered with the transaction. Assume that a table, named employees, contains these columns:

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

Your description of triggers as special types of stored procedures that execute automatically in response to data modifications (INSERT, DELETE, UPDATE) is accurate. In SQL Server 2022, triggers are categorized based on the type of operation they respond to and their execution behavior. Below are the different types of triggers available in SQL Server 2022:
1. DML Triggers (Data Manipulation Language Triggers)
These triggers fire in response to data modification operations (INSERT, DELETE, UPDATE) on a table or view. They are the most commonly used triggers and are further divided into:

• AFTER Triggers (also called FOR Triggers):
  • Execute after the triggering action (INSERT, DELETE, UPDATE) completes but before the transaction is committed.
  • Can be defined for one or more actions (e.g., AFTER INSERT, UPDATE).
  • Multiple AFTER triggers can exist for the same action on a table, with execution order controlled using sp_settriggerorder.
  • Example: Enforcing a business rule, like logging salary changes after an UPDATE.
  • Use case: Auditing, enforcing complex business rules, or updating related tables.
• INSTEAD OF Triggers:
  • Execute in place of the triggering action, preventing the default operation (INSERT, DELETE, UPDATE) from occurring unless explicitly coded.
  • Commonly used with views to make them updatable or to override default behavior.
  • Only one INSTEAD OF trigger per action (INSERT, DELETE, UPDATE) is allowed per table or view.
  • Example: Redirecting an INSERT on a view to update underlying tables.
  • Use case: Customizing data modifications, handling complex views, or preventing direct table updates.

2. DDL Triggers (Data Definition Language Triggers)
These triggers fire in response to schema changes or database-level events, such as CREATE, ALTER, or DROP statements, or certain system stored procedures.

• Scope:
  • Database-level DDL Triggers: Fire for events affecting the database (e.g., CREATE_TABLE, ALTER_SCHEMA).
  • Server-level DDL Triggers: Fire for server-wide events (e.g., CREATE_LOGIN, ALTER_SERVER_CONFIGURATION).
• Example: Preventing unauthorized table drops by logging and rolling back DROP_TABLE operations.
• Use case: Auditing schema changes, enforcing security policies, or preventing unintended modifications.

DDL triggers sound quite powerful for maintaining the integrity and security of the database structure. It's interesting how they operate at a different level than DML triggers, focusing on the schema itself rather than the data within the tables.

These triggers fire in response to a LOGON event, when a user session is established with the SQL Server instance.

• Execute before the session is fully established, allowing you to restrict or audit logins.
• Example: Blocking logins from specific IP addresses or logging all login attempts.
• Use case: Enhancing security, auditing user access, or enforcing connection limits.

Login triggers are a crucial component for security and governance! The ability to intercept and control login attempts before they are fully established offers a powerful mechanism for enforcing policies. It's interesting how SQL Server provides these different types of triggers to address various aspects of database operations and security.

Key Notes for SQL Server 2022:

• DML Triggers can be configured to fire on specific columns (using UPDATE(column_name)) and handle multi-row operations via the inserted and deleted virtual tables.
• DDL Triggers use the EVENTDATA() function to capture details about the triggering event (e.g., the SQL command or user).
• Logon Triggers are server-level and interact with login metadata, such as ORIGINAL_LOGIN() or CLIENT_IP().
• Triggers in SQL Server 2022 support advanced features like In-Memory OLTP (for memory-optimized tables) and integration with temporal tables for tracking changes.
• Triggers can be enabled or disabled using ENABLE TRIGGER or DISABLE TRIGGER, and their execution order can be managed for AFTER triggers.

It's fascinating to see how SQL Server triggers have evolved to incorporate newer features like In-Memory OLTP and temporal tables! The ability to fine-tune their behavior based on specific columns or manage their execution order for AFTER triggers provides a significant amount of control. And the EVENTDATA() function for DDL triggers seems incredibly useful for auditing and understanding schema changes.

Example Context Previously we discussed DML triggers (INSERT, DELETE, UPDATE) with an example of an AFTER UPDATE trigger enforcing a salary increase limit. That example fits into the DML AFTER trigger category, which is one of the most common types used for business rule enforcement.

Triggers are special types of Stored Procedures that are defined to execute automatically in place of or after data modifications. They can be executed automatically on the INSERT, DELETE and UPDATE triggering actions. There are different types of triggers available in Microsoft SQL Server 2022.
Creating an UPDATE Trigger You can create an UPDATE trigger that fires only when the Salary column is updated, like this:
To enforce this business rule, you will create an INSERT trigger named trgSalary.

CREATE TRIGGER trgSalary 
ON employees
AFTER UPDATE
AS
BEGIN
   SET NOCOUNT ON;

   -- Trigger fires only when the Salary column is updated
   IF UPDATE(Salary)
   BEGIN
       -- Check for invalid business logic: salary > 150000 with missing ApprovalID
       IF EXISTS (
           SELECT 1 
           FROM INSERTED 
           WHERE Salary > 150000 AND ApprovalID IS NULL
       )
       BEGIN
           -- Optional: log or take other action here

           -- Roll back the transaction to prevent the update
           RAISERROR (
               'You must enter an ApprovalID for salaries greater than $150,000.', 
               16, 1
           );
           ROLLBACK TRANSACTION;
           RETURN;
       END
   END
END;

🧠 Behavior:

• Enforces a business rule: if Salary > 150000, an ApprovalID must be provided.
• Stops the update and returns a user-friendly error.
• Avoids dangerous DELETE operations.

In the next lesson, modification of a trigger will be discussed.

Click the Exercise link below to practice creating triggers.
Creating SQL Server Triggers - Exercise