Triggers in SQL

Triggers

Triggers in SQL are special types of stored procedures that are automatically executed, or "triggered", when certain events occur in the database. These events can include data modifications such as INSERT, UPDATE, or DELETE operations, as well as changes to the database schema. Triggers are used to enforce business rules, maintain audit trails, and automatically update or synchronize data.

Types of Triggers

1. AFTER Triggers:

AFTER triggers are executed after the specified event (e.g., INSERT, UPDATE, DELETE) has occurred. These triggers are useful when you want to perform additional operations after the initial data modification.

AFTER INSERT Trigger

This trigger is executed after a new record is inserted into a table.

CREATE TRIGGER trgAfterInsertOnEmployees
ON Employees
AFTER INSERT
AS
BEGIN
    INSERT INTO EmployeeAudit (EmployeeID, FirstName, LastName, Department, Action, ActionDate)
    SELECT EmployeeID, FirstName, LastName, Department, 'INSERT', GETDATE()
    FROM inserted;
END;

AFTER UPDATE Trigger

This trigger is executed after an existing record is updated.

CREATE TRIGGER trgAfterUpdateOnEmployees
ON Employees
AFTER UPDATE
AS
BEGIN
    INSERT INTO EmployeeAudit (EmployeeID, FirstName, LastName, Department, Action, ActionDate)
    SELECT EmployeeID, FirstName, LastName, Department, 'UPDATE', GETDATE()
    FROM inserted;
END;

AFTER DELETE Trigger

This trigger is executed after a record is deleted from a table.

CREATE TRIGGER trgAfterDeleteOnEmployees
ON Employees
AFTER DELETE
AS
BEGIN
    INSERT INTO EmployeeAudit (EmployeeID, FirstName, LastName, Department, Action, ActionDate)
    SELECT EmployeeID, FirstName, LastName, Department, 'DELETE', GETDATE()
    FROM deleted;
END;

2. INSTEAD OF Triggers:

INSTEAD OF triggers are executed in place of the specified event. These triggers allow you to override the default behavior of the database engine, providing a way to implement custom logic before the actual data modification occurs.

INSTEAD OF INSERT Trigger

This trigger allows you to perform custom actions instead of the default insert operation.

CREATE TRIGGER trgInsteadOfInsertOnEmployees
ON Employees
INSTEAD OF INSERT
AS
BEGIN
    -- Custom logic before the insert
    INSERT INTO Employees (EmployeeID, FirstName, LastName, Department)
    SELECT EmployeeID, FirstName, LastName, Department
    FROM inserted;

    INSERT INTO EmployeeAudit (EmployeeID, FirstName, LastName, Department, Action, ActionDate)
    SELECT EmployeeID, FirstName, LastName, Department, 'INSERT', GETDATE()
    FROM inserted;
END;

INSTEAD OF UPDATE Trigger

This trigger allows you to perform custom actions instead of the default update operation.

CREATE TRIGGER trgInsteadOfUpdateOnEmployees
ON Employees
INSTEAD OF UPDATE
AS
BEGIN
    -- Custom logic before the update
    UPDATE Employees
    SET FirstName = inserted.FirstName, LastName = inserted.LastName, Department = inserted.Department
    FROM Employees
    INNER JOIN inserted ON Employees.EmployeeID = inserted.EmployeeID;

    INSERT INTO EmployeeAudit (EmployeeID, FirstName, LastName, Department, Action, ActionDate)
    SELECT EmployeeID, FirstName, LastName, Department, 'UPDATE', GETDATE()
    FROM inserted;
END;

INSTEAD OF DELETE Trigger

This trigger allows you to perform custom actions instead of the default delete operation.

CREATE TRIGGER trgInsteadOfDeleteOnEmployees
ON Employees
INSTEAD OF DELETE
AS
BEGIN
    -- Archive the data before deleting
    INSERT INTO ArchivedEmployees (EmployeeID, FirstName, LastName, Department, ArchivedDate)
    SELECT EmployeeID, FirstName, LastName, Department, GETDATE()
    FROM deleted;

    DELETE FROM Employees
    WHERE EmployeeID IN (SELECT EmployeeID FROM deleted);

    INSERT INTO EmployeeAudit (EmployeeID, FirstName, LastName, Department, Action, ActionDate)
    SELECT EmployeeID, FirstName, LastName, Department, 'DELETE', GETDATE()
    FROM deleted;
END;

3. DDL Triggers:

DDL (Data Definition Language) triggers are fired in response to changes in the database schema, such as the creation, alteration, or deletion of tables, views, or other objects.

DDL Trigger for Schema Changes

This trigger is executed when a table is created, altered, or dropped.

CREATE TRIGGER trgOnDatabaseChanges
ON DATABASE
FOR CREATE_TABLE, ALTER_TABLE, DROP_TABLE
AS
BEGIN
    PRINT 'A schema change has occurred.';
END;