1. Example. TRIGGER.sql

-- Let's create a database in our DBMS

IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = 'db_test')
BEGIN
    CREATE DATABASE db_test;
END

-- Using the database we created previously

USE db_test;

-- Creating the Records table

IF NOT EXISTS(SELECT * FROM sys.sysobjects WHERE name='Records' AND xtype='U')  
CREATE TABLE Records(
    Code                VARCHAR(15) NOT NULL,
    State               VARCHAR(20) DEFAULT 'START',
    StateChangedDate    DATETIME,
    CONSTRAINT          PK_Records PRIMARY KEY(code)    
);
GO

DELETE FROM Records WHERE Code IN ('exp1','exp2', 'exp3');
INSERT INTO Records(Code) VALUES('exp1');
INSERT INTO Records(Code) VALUES('exp2');
INSERT INTO Records(Code) VALUES('exp3');

-- Creating the RecStatusHistory table

IF NOT EXISTS (SELECT * FROM sys.sysobjects WHERE name='RecStatusHistory' AND xtype='U')  
CREATE TABLE RecStatusHistory(
    Id          INT IDENTITY,
    Code        VARCHAR(15) NOT NULL,
    State       VARCHAR(20) NOT NULL,
    DATE        DATETIME DEFAULT GETDATE(),
    CONSTRAINT  PK_RecStatusHistory PRIMARY KEY(id)
);
GO
 
-- Deleting the trigger if already exists in the database
--IF EXISTS(SELECT 1 FROM sys.triggers WHERE name = 'StatusChangeDateTrigger')
--  DROP TRIGGER StatusChangeDateTrigger
--GO
IF OBJECT_ID('StatusChangeDateTrigger', 'TR') IS NOT NULL
BEGIN
    DROP TRIGGER StatusChangeDateTrigger;
END;
GO

-- Creating the trigger
CREATE TRIGGER StatusChangeDateTrigger ON Records
AFTER UPDATE AS
    IF UPDATE(state)
        BEGIN
            UPDATE Records
            SET stateChangedDate = GETDATE()
            WHERE code = (SELECT code FROM inserted);
 
            INSERT INTO RecStatusHistory(Code, State)
            SELECT Code, State
            FROM deleted
            WHERE Code = deleted.Code
        END;
GO      
 
SELECT *
FROM Records

/** Output **/
-- The first time, the Records table has NULL values in the StateChangedDate
Code            State                StateChangedDate
--------------- -------------------- -----------------------
exp1            PENDING_PAYMENT      NULL
exp2            START                NULL
exp3            START                NULL

-- Let's see the magic of the trigger
UPDATE Records
SET State = 'PENDING_PAYMENT'
WHERE Code = 'exp1'
 
SELECT *
FROM RecStatusHistory

/** Output **/
Id          Code            State                DATE
----------- --------------- -------------------- -----------------------
1           exp1            START                2017-03-20 19:51:09.070

SELECT *
FROM Records

/** Output **/
Code            State                StateChangedDate
--------------- -------------------- -----------------------
exp1            PENDING_PAYMENT      2017-03-20 19:52:24.857
exp2            START                NULL
exp3            START                NULL