Triggers: Automatic Actions

The league wants a record of every player ever added or changed. You could ask every developer to remember to write a log row… but someone will forget. A trigger makes the database write the log by itself, every time.

Definition

A trigger is a special stored program that the database executes automatically when a specific event happens on a table, view or database — such as an INSERT, UPDATE or DELETE (DML triggers) or a CREATE / ALTER / DROP (DDL triggers). Nobody calls a trigger directly; it fires in response to the event.

• Automatic — unlike a stored procedure, there is no EXEC.
• Event — the WHEN: e.g. AFTER INSERT on players.
• Action — the WHAT: the SQL inside the trigger body.

How a trigger works

Trigger types

Syntax (SQL Server)

CREATE TRIGGER trigger_name
ON table_name                          -- WHICH table is watched
AFTER INSERT, UPDATE, DELETE           -- WHEN: the event(s)
AS
BEGIN
    -- WHAT: the SQL statements to run
END;

The inserted and deleted tables

Inside a SQL Server DML trigger you can read two special, temporary tables:

Note: A SQL Server trigger fires once per statement, not once per row. If one UPDATE changes 2 rows, inserted holds 2 rows. Always write set-based code like INSERT … SELECT … FROM inserted — never assume a single row.

Example — maintaining a log (AFTER INSERT)

CREATE TABLE player_log (
    log_id      INT IDENTITY(1,1) PRIMARY KEY,
    action      VARCHAR(10),
    player_name VARCHAR(50),
    logged_at   DATETIME DEFAULT GETDATE()
);

CREATE TRIGGER trg_log_insert
ON players
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;
    INSERT INTO player_log (action, player_name)
    SELECT 'INSERT', name
    FROM inserted;
END;

Now you only write:

INSERT INTO players VALUES (7, 'Hardik Pandya', 'MI', 'India', 300);
SELECT log_id, action, player_name FROM player_log;

Nobody called the trigger — it fired on its own.

Example — audit an UPDATE with old and new values

CREATE TRIGGER trg_log_runs
ON players
AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON;
    INSERT INTO runs_log (player_name, old_runs, new_runs)
    SELECT i.name, d.runs, i.runs
    FROM inserted AS i
    JOIN deleted  AS d ON i.id = d.id
    WHERE i.runs <> d.runs;
END;

UPDATE players SET runs = runs + 50 WHERE team = 'MI';

One UPDATE statement → the trigger fires once → two log rows (because inserted had two rows).

Example — INSTEAD OF DELETE (soft delete)

CREATE TRIGGER trg_no_delete
ON players
INSTEAD OF DELETE
AS
BEGIN
    SET NOCOUNT ON;
    UPDATE p
    SET p.team = NULL                  -- mark as released instead of deleting
    FROM players AS p
    JOIN deleted AS d ON p.id = d.id;
END;

DELETE FROM players WHERE id = 6; now keeps Bumrah's row and only clears his team.

Example — DDL trigger: guard the schema

CREATE TRIGGER trg_block_drop
ON DATABASE
FOR DROP_TABLE
AS
BEGIN
    PRINT 'Dropping tables is not allowed here!';
    ROLLBACK;
END;

Common mistakes

• ❌ Assuming one row: DECLARE @name VARCHAR(50) = (SELECT name FROM inserted) → breaks or logs only one row for multi-row statements. ✅ INSERT … SELECT … FROM inserted.
• ❌ Putting heavy logic in triggers → every INSERT/UPDATE becomes slow and debugging gets hard ("hidden" code). ✅ keep triggers small; use them for logging and rules.
• ❌ Forgetting a trigger exists and being confused by "extra" rows. ✅ document triggers; list them with SELECT name FROM sys.triggers.
• ❌ Writing SQLite / MySQL style NEW.name in SQL Server. ✅ SQL Server uses the inserted / deleted tables.

SQL Server vs MySQL / PostgreSQL / SQLite

The playground runs SQLite, so its "try it" example below uses SQLite trigger syntax (NEW.name) — the same idea, but not SQL Server code.

Interview tip

Q: Stored procedure vs trigger? A stored procedure runs only when someone calls it with EXEC and can take parameters. A trigger cannot be called and takes no parameters — it fires automatically when its event (INSERT / UPDATE / DELETE / DDL) happens.