How to Create a Trigger in MySQL (Practical Examples)

Have you ever wanted your database to automatically log data changes, validate input, or update another table every time there is an INSERT, UPDATE, or DELETE operation? That is exactly what a Trigger in MySQL does. A trigger is a procedure executed automatically by MySQL in response to a specific event on a table. This article covers how to create MySQL triggers in full, with real examples.

Basic Trigger Concept

A trigger has three main components:

• Event: When the trigger fires — INSERT, UPDATE, or DELETE.
• Timing: Whether the trigger runs BEFORE or AFTER the event occurs.
• Body: The SQL logic executed when the trigger fires.

Inside the trigger body you can access two special references: NEW (the new value that will be / has been saved) and OLD (the old value before it is changed/deleted).

Preparing the Example Tables

-- Main product table
CREATE TABLE produk (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nama VARCHAR(200) NOT NULL,
    harga DECIMAL(10,2) NOT NULL,
    stok INT NOT NULL DEFAULT 0,
    diperbarui DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Log table to record price changes
CREATE TABLE log_perubahan_harga (
    id INT AUTO_INCREMENT PRIMARY KEY,
    id_produk INT NOT NULL,
    harga_lama DECIMAL(10,2) NOT NULL,
    harga_baru DECIMAL(10,2) NOT NULL,
    diubah_pada DATETIME DEFAULT CURRENT_TIMESTAMP,
    diubah_oleh VARCHAR(100) DEFAULT USER()
) ENGINE=InnoDB;

-- Log table for stock
CREATE TABLE log_stok (
    id INT AUTO_INCREMENT PRIMARY KEY,
    id_produk INT NOT NULL,
    aksi ENUM('MASUK', 'KELUAR', 'HAPUS') NOT NULL,
    stok_sebelum INT NOT NULL,
    stok_sesudah INT NOT NULL,
    selisih INT NOT NULL,
    dicatat DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

AFTER UPDATE Trigger – Logging Price Changes

This trigger automatically records every price change into the log table:

DELIMITER $$

CREATE TRIGGER trg_log_perubahan_harga
AFTER UPDATE ON produk
FOR EACH ROW
BEGIN
    -- Only log if the price actually changed
    IF OLD.harga <> NEW.harga THEN
        INSERT INTO log_perubahan_harga
            (id_produk, harga_lama, harga_baru)
        VALUES
            (NEW.id, OLD.harga, NEW.harga);
    END IF;
END$$

DELIMITER ;

-- Test the trigger
INSERT INTO produk (nama, harga, stok) VALUES ('Laptop ASUS', 8500000, 10);
UPDATE produk SET harga = 9000000 WHERE id = 1;

-- Check the log
SELECT * FROM log_perubahan_harga;

AFTER UPDATE Trigger – Logging Stock Changes

DELIMITER $$

CREATE TRIGGER trg_log_stok
AFTER UPDATE ON produk
FOR EACH ROW
BEGIN
    DECLARE v_aksi ENUM('MASUK', 'KELUAR', 'HAPUS');
    DECLARE v_selisih INT;

    SET v_selisih = NEW.stok - OLD.stok;

    IF v_selisih > 0 THEN
        SET v_aksi = 'MASUK';
    ELSEIF v_selisih < 0 THEN
        SET v_aksi = 'KELUAR';
    ELSE
        LEAVE; -- no stock change
    END IF;

    INSERT INTO log_stok
        (id_produk, aksi, stok_sebelum, stok_sesudah, selisih)
    VALUES
        (NEW.id, v_aksi, OLD.stok, NEW.stok, ABS(v_selisih));
END$$

DELIMITER ;

BEFORE INSERT Trigger – Data Validation

A BEFORE trigger is useful for validating or modifying data before it is saved:

DELIMITER $$

CREATE TRIGGER trg_validasi_harga
BEFORE INSERT ON produk
FOR EACH ROW
BEGIN
    -- Make sure the price is not negative
    IF NEW.harga <= 0 THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Product price must be greater than 0';
    END IF;

    -- Make sure the stock is not negative
    IF NEW.stok < 0 THEN
        SET NEW.stok = 0;  -- automatically set to 0 if negative
    END IF;

    -- Capitalize the product name
    SET NEW.nama = UPPER(LEFT(NEW.nama, 1))
                   || LOWER(SUBSTRING(NEW.nama, 2));
END$$

DELIMITER ;

-- Test: this will FAIL because the price is negative
-- INSERT INTO produk (nama, harga, stok) VALUES ('Test', -100, 5);

-- Test: a negative stock will automatically become 0
INSERT INTO produk (nama, harga, stok) VALUES ('Mouse Wireless', 350000, -5);
SELECT stok FROM produk WHERE nama = 'Mouse Wireless';

AFTER DELETE Trigger – Archiving Deleted Data

-- Create the archive table
CREATE TABLE arsip_produk (
    id INT NOT NULL,
    nama VARCHAR(200),
    harga DECIMAL(10,2),
    stok INT,
    dihapus_pada DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

DELIMITER $$

CREATE TRIGGER trg_arsip_produk
AFTER DELETE ON produk
FOR EACH ROW
BEGIN
    INSERT INTO arsip_produk (id, nama, harga, stok)
    VALUES (OLD.id, OLD.nama, OLD.harga, OLD.stok);

    -- Record it in the stock log too
    INSERT INTO log_stok
        (id_produk, aksi, stok_sebelum, stok_sesudah, selisih)
    VALUES
        (OLD.id, 'HAPUS', OLD.stok, 0, OLD.stok);
END$$

DELIMITER ;

Viewing and Dropping Triggers

-- View all triggers in the active database
SHOW TRIGGERS;

-- View triggers on a specific table
SHOW TRIGGERS FROM nama_database LIKE 'produk';

-- View trigger details from INFORMATION_SCHEMA
SELECT
    TRIGGER_NAME,
    EVENT_MANIPULATION,
    ACTION_TIMING,
    EVENT_OBJECT_TABLE
FROM INFORMATION_SCHEMA.TRIGGERS
WHERE TRIGGER_SCHEMA = DATABASE()
ORDER BY EVENT_OBJECT_TABLE, ACTION_TIMING;

-- Drop a trigger
DROP TRIGGER IF EXISTS trg_log_perubahan_harga;

Things to Keep in Mind

• A single table can have a maximum of 6 triggers (BEFORE/AFTER × INSERT/UPDATE/DELETE).
• By default, a trigger cannot call itself recursively.
• Avoid heavy logic inside a trigger because it will slow down DML operations.
• Use SIGNAL SQLSTATE to cancel an operation and show an error message.
• The OLD reference is available for UPDATE and DELETE, while NEW is for INSERT and UPDATE.

Conclusion

MySQL triggers are a powerful tool for automating database actions in response to INSERT, UPDATE, or DELETE events. With triggers you can build an automatic audit-log system, validate data before it is saved, or maintain data consistency between tables without writing extra logic in your application. Use BEFORE triggers to validate and modify data, and AFTER triggers to record and synchronize data to other tables.