Triggers in SQL Server

A Trigger is a special type of stored procedure that is not executed manually. It fires automatically when a specific event occurs on a table.

It’s like an alarm system or a motion sensor: you don’t turn it on every time; you simply set up “If someone opens this door, sound the alarm.”

In SQL, the rule would be: “If someone deletes a customer, save it to the history table”

Event Types

A Trigger executes automatically when a specific event occurs on a table. These events can be INSERT, DELETE, and UPDATE.

Within a trigger, SQL Server creates two temporary in-memory tables that contain the data affected by the operation:

Depending on the event type, you will have data in one, the other, or both of these tables.

AFTER Triggers

These are the most common. They execute after the action has succeeded (INSERT, UPDATE, DELETE), but before committing the transaction.

For example, imagine we want that if someone changes a product’s price, a record of “who, when, and how much” is automatically saved.

-- 1. The table where we will save the history
CREATE TABLE HistorialPrecios (
    ProductoID INT,
    PrecioAntiguo DECIMAL(10,2),
    PrecioNuevo DECIMAL(10,2),
    Usuario VARCHAR(50),
    FechaCambio DATETIME DEFAULT GETDATE()
);
GO

-- 2. The Trigger
CREATE TRIGGER trg_AuditoriaPrecios
ON Productos
AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON;

    -- Detect if the Price column was touched
    IF UPDATE(Precio)
    BEGIN
        -- Insert into the history by joining the magical tables
        INSERT INTO HistorialPrecios (ProductoID, PrecioAntiguo, PrecioNuevo, Usuario)
        SELECT 
            i.ProductoID, 
            d.Precio,  -- Old value (comes from DELETED)
            i.Precio,  -- New value (comes from INSERTED)
            SYSTEM_USER -- Current user
        FROM inserted i
        INNER JOIN deleted d ON i.ProductoID = d.ProductoID;
    END
END

Now, every time you do an UPDATE Productos SET Precio = ..., the trigger will fire silently and save the evidence.

INSTEAD OF Triggers

These are (interceptor) triggers that cancel the original action and execute their own code instead. They are very useful for protecting data or handling complex Views.

For example, the case of “Soft Delete”. In many companies, deleting data (DELETE) is prohibited. Instead, a column Activo = 0 is marked.

We can enforce this with a trigger, even if the programmer tries to do a DELETE.

CREATE TRIGGER trg_ProtegerClientes
ON Clientes
INSTEAD OF DELETE
AS
BEGIN
    SET NOCOUNT ON;

    -- Instead of deleting, we update the Activo field
    UPDATE Clientes
    SET Activo = 0
    WHERE ClienteID IN (SELECT ClienteID FROM deleted);
    
    PRINT 'The record was not deleted, it has been marked as inactive.';
END

If you execute DELETE FROM Clientes WHERE ID = 1, the record will still be there, but marked as inactive.