Post
Ask Question

Creating Triggers in SQL Server

Introduction

In SQL Server, triggers are special types of stored procedures that automatically execute or fire when certain events occur in the database. They are used to enforce business rules, data integrity, and audit changes. This article will guide you through the creation of triggers in SQL Server, explaining their types and providing practical examples.

Types of Triggers

  1. DML Triggers (Data Manipulation Language): These triggers fire in response to DML events such as INSERT, UPDATE, and DELETE.
  2. DDL Triggers (Data Definition Language): These triggers fire in response to DDL events such as CREATE, ALTER, and DROP.
  3. Logon Triggers: These triggers fire in response to logon events.

DML Triggers

DML triggers can be further classified into.

  • AFTER Triggers: Execute after the triggering event.
  • INSTEAD OF Triggers: Execute instead of the triggering event.

Creating an AFTER Trigger

An AFTER trigger is used to execute a set of SQL statements after an INSERT, UPDATE, or DELETE operation on a table.

Example. Creating an AFTER INSERT Trigger.

Let's create a trigger that logs changes whenever a new record is inserted into the Employees table.

-- Step 1: Create the log table
CREATE TABLE EmployeeChangesLog (
    ChangeID INT IDENTITY(1,1) PRIMARY KEY,
    EmployeeID INT,
    ChangeType NVARCHAR(50),
    ChangeDate DATETIME DEFAULT GETDATE()
);
-- Step 2: Create the AFTER INSERT trigger
CREATE TRIGGER trgAfterInsert
ON Employees
AFTER INSERT
AS
BEGIN
    INSERT INTO EmployeeChangesLog (EmployeeID, ChangeType)
    SELECT EmployeeID, 'INSERT'
    FROM inserted;
END;

In this example.

  • We first create a log table EmployeeChangesLog to store the changes.
  • We then create an AFTER INSERT trigger on the Employees table that inserts a record into the EmployeeChangesLog table whenever a new record is inserted into the Employees table.

Creating an AFTER UPDATE Trigger

CREATE TRIGGER trgAfterUpdate ON Employees
AFTER UPDATE
AS
BEGIN
    INSERT INTO EmployeeChangesLog (EmployeeID, ChangeType)
    SELECT EmployeeID, 'UPDATE' FROM inserted;
END;

Creating an AFTER-DELETE Trigger

CREATE TRIGGER trgAfterDelete ON Employees
AFTER DELETE
AS
BEGIN
    INSERT INTO EmployeeChangesLog (EmployeeID, ChangeType)
    SELECT EmployeeID, 'DELETE' FROM deleted;
END;

INSTEAD OF Triggers

INSTEAD OF triggers are used to perform an action instead of the triggering event. They are often used to override the default behavior of INSERT, UPDATE, or DELETE operations.

Example. Creating an INSTEAD OF UPDATE Trigger.

Let's create a trigger that updates an Employee table but ensures that the Salary field cannot be set to a negative value.

CREATE TRIGGER trgInsteadOfUpdate ON Employees
INSTEAD OF UPDATE
AS
BEGIN
    UPDATE Employees
    SET
        Name = inserted.Name,
        Position = inserted.Position,
        Salary = CASE 
                    WHEN inserted.Salary < 0 THEN 0 
                    ELSE inserted.Salary 
                 END
    FROM inserted
    WHERE Employees.EmployeeID = inserted.EmployeeID;
END;

In this example, the trigger ensures that if an attempt is made to set the Salary to a negative value, it will be set to 0 instead.

DDL Triggers

DDL triggers are used to respond to changes in the database schema, such as creating, altering, or dropping objects.

Example. Creating a DDL Trigger.

Let's create a trigger that logs changes whenever a table is created or dropped.

-- Step 1: Create the log table
CREATE TABLE SchemaChangesLog (
    ChangeID INT IDENTITY(1,1) PRIMARY KEY,
    EventType NVARCHAR(50),
    EventDate DATETIME DEFAULT GETDATE(),
    EventData NVARCHAR(MAX)
);
-- Step 2: Create the DDL trigger
CREATE TRIGGER trgSchemaChanges
ON DATABASE
FOR CREATE_TABLE, DROP_TABLE
AS
BEGIN
    DECLARE @EventData XML;
    SET @EventData = EVENTDATA();
    
    INSERT INTO SchemaChangesLog (EventType, EventData)
    VALUES (@EventData.value('(/EVENT_INSTANCE/EventType)[1]', 'NVARCHAR(50)'), 
            @EventData.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'NVARCHAR(MAX)'));
END;

In this example.

  • We first create a log table SchemaChangesLog to store the changes.
  • We then create a FOR CREATE_TABLE, DROP_TABLE trigger on the database that logs changes whenever a table is created or dropped.

Logon Triggers

Logon triggers fire in response to logon events and can be used to control and monitor login activity.

Example. Creating a Logon Trigger.

Let's create a trigger that restricts logins during a specific time period.

CREATE TRIGGER trgRestrictLogon
ON ALL SERVER
FOR LOGON
AS
BEGIN
    DECLARE @currentHour INT;
    SET @currentHour = DATEPART(HOUR, GETDATE());

    IF @currentHour NOT BETWEEN 9 AND 17
    BEGIN
        ROLLBACK;
        PRINT 'Logins are only allowed between 9 AM and 5 PM';
    END;
END;

In this example, the trigger restricts logins to between 9 AM and 5 PM. If a login attempt is made outside of this period, it is denied.

Conclusion

Triggers in SQL Server are powerful tools for automating responses to database events. By understanding and utilizing different types of triggers, you can enforce business rules, maintain data integrity, and monitor changes effectively. Whether you're using DML, DDL, or logon triggers, the key is to implement them thoughtfully to ensure they enhance your database's functionality and performance.


Similar Articles