11. Triggers

Triggers in SQL

A trigger is a named set of T-SQL (or SQL) commands that automatically executes in response to certain events, such as DDL operations (CREATE, ALTER, DROP) or DML operations (INSERT, UPDATE, DELETE).

Purpose of Triggers

• Perform additional actions before or after the main event.

• Enforce complex business rules (for example, preventing certain rows from being inserted).

• Maintain audit logs by capturing changes to data.

• Handle cascading operations in related tables.

DDL vs. DML Triggers

• DDL Triggers fire in response to statements like CREATE, ALTER, or DROP on a database object.

• DML Triggers fire in response to INSERT, UPDATE, or DELETE statements on a table or view.

Types of DML Triggers

• AFTER Trigger

  Executes after the core statement’s work is mostly done (about 80% complete). Commonly used for logging actions or validating data after it is altered.

  Example: AFTER DELETE might log which rows were deleted.

• INSTEAD OF Trigger

  Replaces the original operation. This allows you to intercept the action and decide how (or if) it should proceed.

  Example: INSTEAD OF INSERT might check a user’s age; if under 18, handle differently, if over 18, allow insertion.

Temporary Tables for DML Triggers

When a DML trigger fires, SQL Server automatically creates two temporary tables (where applicable):

• INSERTED holds newly inserted rows (in the case of INSERT or UPDATE).

• DELETED holds rows that have been deleted (in the case of DELETE or UPDATE).
  These tables have the same structure as the base table, making it easier to reference old and new data in the trigger body.
  

Example: AFTER UPDATE Trigger

CREATE TRIGGER BookUpdates
ON Books
AFTER UPDATE
AS
BEGIN
    PRINT @@ROWCOUNT;  -- Number of rows updated
END
GO

UPDATE Books
SET Pages = Pages + 1
WHERE Pages > 400;
GO

Example: AFTER INSERT Trigger with Validation

This trigger prevents adding a book if its YearPress is less than 2000.

CREATE TRIGGER OldBook
ON Books
AFTER INSERT
AS
BEGIN
    DECLARE @year INT;

    SELECT @year = YearPress
    FROM Inserted;

    IF (@year < 2000)
    BEGIN
        PRINT 'Year < 2000';
        ROLLBACK TRAN;  -- Undo the INSERT
    END
    ELSE
    BEGIN
        PRINT 'ok!';
    END
END
GO

-- Successful insert
INSERT INTO Books (Id, [Name], Pages, YearPress, Id_Themes, Id_Category, Id_Author, Id_Press, Quantity)
VALUES (100, N'Yeni kitab', 100, 2017, 2, 2, 2, 2, 5);

-- Fails due to trigger
INSERT INTO Books (Id, [Name], Pages, YearPress, Id_Themes, Id_Category, Id_Author, Id_Press, Quantity)
VALUES (99, N'Yeni kitab', 100, 1917, 2, 2, 2, 2, 5);

Example: AFTER DELETE Trigger with Restrictions

This trigger disallows deleting books from the BHV press.

CREATE TRIGGER NotBHV
ON Books
AFTER DELETE
AS
BEGIN
    DECLARE @id_bhv INT;

    SELECT @id_bhv = Id
    FROM Press
    WHERE [Name] LIKE 'BHV';

    IF EXISTS (
        SELECT *
        FROM Deleted
        WHERE Id_Press = @id_bhv
    )
    BEGIN
        PRINT 'Can not delete book from BHV!';
        ROLLBACK TRAN;
    END
END
GO

-- Attempting to delete a BHV book fails
DELETE
FROM Books
WHERE Id = 100;
GO

Example: INSTEAD OF Trigger

An INSTEAD OF trigger intercepts the action and replaces it.

CREATE TRIGGER NoInsertLibs
ON Libs
INSTEAD OF INSERT
AS
BEGIN
    PRINT 'No insert Libs';
END
GO

INSERT INTO Libs (Id, FirstName, LastName)
VALUES (5, N'qwqe', N'asd');

-- The above insert prints "No insert Libs" and does not insert any rows.
SELECT * FROM Libs;

DROP TRIGGER NoInsertLibs;
GO

General Rules and Restrictions

• You cannot create triggers on temporary tables.

• Triggers cannot accept parameters.

• You cannot explicitly invoke a trigger; it fires automatically.

• One table can have multiple triggers for the same event type (e.g., multiple AFTER UPDATE triggers), but only one INSTEAD OF trigger per event type.

• Certain statements (DROP, CREATE, ALTER DATABASE, ALTER TABLE, GRANT, REVOKE, SELECT INTO) are not allowed within triggers.

Reference

The content in this document is based on the original notes provided in Azerbaijani. For further details, you can refer to the original document using the following link:

Original Note - Azerbaijani Version