7.2 Types of Triggers (AFTER, INSTEAD OF)

 Triggers in SQL Server are special types of stored procedures that are automatically executed (or "triggered") in response to specific events, such as INSERT, UPDATE, DELETE, or even schema changes. There are two main types of triggers: AFTER triggers and INSTEAD OF triggers.

7.2.1 AFTER Triggers

AFTER triggers are executed after the triggering event has occurred, but before the changes are committed to the database.

Example:

Consider a table Employee with columns EmployeeID, FirstName, LastName, and Salary. We'll create an AFTER INSERT trigger that automatically updates a log table whenever a new employee is inserted.

-- Create Employee table
CREATE TABLE Employee (
    EmployeeID INT PRIMARY KEY,
    FirstName NVARCHAR(50),
    LastName NVARCHAR(50),
    Salary DECIMAL(10, 2)
);
-- Create Log table
CREATE TABLE EmployeeLog (
    LogID INT PRIMARY KEY,
    Action NVARCHAR(50),
    EmployeeID INT,
    LogDate DATETIME
);
-- Create AFTER INSERT Trigger
CREATE TRIGGER AfterInsertEmployee
ON Employee
AFTER INSERT
AS
BEGIN
    INSERT INTO EmployeeLog (Action, EmployeeID, LogDate)
    SELECT 'Employee Inserted', EmployeeID, GETDATE()
    FROM INSERTED;
END;
-- Insert a new employee
INSERT INTO Employee (EmployeeID, FirstName, LastName, Salary)
VALUES (1, 'John', 'Doe', 50000);

Query Output:

LogID	Action	EmployeeID	LogDate
1	Employee Inserted	1	[Current Timestamp]

In this example, when a new employee is inserted into the Employee table, the AFTER INSERT trigger is fired, and a corresponding entry is added to the EmployeeLog table.

7.2.2 INSTEAD OF Triggers

INSTEAD OF triggers are executed instead of the triggering statement. They allow you to override the standard actions associated with the triggering statement.

Example:

Let's create an INSTEAD OF UPDATE trigger on the Employee table that prevents updating the salary directly and instead updates a history table.

-- Create SalaryHistory table
CREATE TABLE SalaryHistory (
    HistoryID INT PRIMARY KEY,
    EmployeeID INT,
    OldSalary DECIMAL(10, 2),
    NewSalary DECIMAL(10, 2),
    UpdateDate DATETIME
);
-- Create INSTEAD OF UPDATE Trigger
CREATE TRIGGER InsteadOfUpdateSalary
ON Employee
INSTEAD OF UPDATE
AS
BEGIN
    DECLARE @EmployeeID INT, @OldSalary DECIMAL(10, 2), @NewSalary DECIMAL(10, 2);
    SELECT @EmployeeID = EmployeeID, @OldSalary = Salary
    FROM DELETED;
    SELECT @NewSalary = Salary
    FROM INSERTED;
    UPDATE Employee
    SET Salary = @NewSalary
    WHERE EmployeeID = @EmployeeID;
    INSERT INTO SalaryHistory (EmployeeID, OldSalary, NewSalary, UpdateDate)
    VALUES (@EmployeeID, @OldSalary, @NewSalary, GETDATE());
END;
-- Update the salary of an employee
UPDATE Employee
SET Salary = 60000
WHERE EmployeeID = 1;

Query Output:

HistoryID	EmployeeID	OldSalary	NewSalary	UpdateDate
1	1	50000.00	60000.00	[Current Timestamp]

In this example, when the salary of an employee is updated, the INSTEAD OF UPDATE trigger is fired, and the salary change is recorded in the SalaryHistory table instead of directly updating the Employee table.

These examples illustrate the use of AFTER and INSTEAD OF triggers in SQL Server and how they can be applied to perform custom actions in response to specific events.