MySQL CREATE TRIGGER

Master MySQL CREATE TRIGGER — full syntax, OLD and NEW availability per event type, a worked audit-log example with item_changes capturing both old and new prices, SHOW TRIGGERS and information_schema.TRIGGERS for inspection, DROP TRIGGER IF EXISTS for safe deployment, and best practices.

The CREATE TRIGGER statement defines a new trigger on a table. The trigger fires automatically when the matching INSERT, UPDATE, or DELETE event occurs.

If the body has multiple statements, use a BEGIN ... END block and change the delimiter so the inner semicolons don't end the CREATE TRIGGER prematurely:

Inside the trigger body, the OLD and NEW pseudo-rows expose the affected row's data. Which is available depends on the event:

Reference fields with OLD.column_name and NEW.column_name. Inside a BEFORE trigger you can assign to NEW.column_name with SET NEW.column_name = ... to modify what gets stored.

Build a worked example: an items table with an item_changes audit log. Whenever an item is updated, the trigger writes a record to the log including who-changed-what.

First, the tables:

DROP TABLE IF EXISTS item_changes;
DROP TABLE IF EXISTS items;

CREATE TABLE items (
    id     INT          PRIMARY KEY,
    name   VARCHAR(255) NOT NULL,
    price  DECIMAL(10, 2) NOT NULL
);

CREATE TABLE item_changes (
    change_id        INT       PRIMARY KEY AUTO_INCREMENT,
    item_id          INT,
    change_type      VARCHAR(10),
    old_price        DECIMAL(10, 2),
    new_price        DECIMAL(10, 2),
    change_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (item_id) REFERENCES items(id)
);

-- Seed
INSERT INTO items (id, name, price) VALUES (1, 'Widget', 50.00);

Now the trigger — note DROP TRIGGER IF EXISTS at the top for idempotent deployment:

DROP TRIGGER IF EXISTS update_items_trigger;

DELIMITER $$

CREATE TRIGGER update_items_trigger
    AFTER UPDATE ON items
    FOR EACH ROW
BEGIN
    INSERT INTO item_changes (item_id, change_type, old_price, new_price)
    VALUES (NEW.id, 'UPDATE', OLD.price, NEW.price);
END$$

DELIMITER ;

Trigger an update:

UPDATE items SET price = 60.00 WHERE id = 1;

SELECT * FROM item_changes;

Notice we never explicitly inserted into item_changes — the trigger did it automatically. The application code only ran UPDATE items; the audit log appeared as a side effect.

Use SHOW TRIGGERS to find what's installed:

-- All triggers in the current database
SHOW TRIGGERS;

-- Filter by table name
SHOW TRIGGERS LIKE 'items';

-- Filter by event type
SHOW TRIGGERS WHERE `Event` = 'UPDATE';

For programmatic access, query the metadata directly. The same data with much more flexibility:

SELECT trigger_name,
       event_object_table  AS `table`,
       event_manipulation  AS `event`,
       action_timing       AS timing,
       definer
FROM   information_schema.TRIGGERS
WHERE  trigger_schema = DATABASE()
ORDER  BY event_object_table, action_timing, event_manipulation;

DROP TRIGGER IF EXISTS update_items_trigger;

Always include IF EXISTS in deployment scripts — without it, dropping a non-existent trigger errors out and aborts the script.

1. Always include DROP TRIGGER IF EXISTS at the top of any CREATE TRIGGER deployment script. Idempotent re-deployment for free.
2. Use descriptive names. employees_before_insert_validate_age beats trg1. Triggers live for years; future you will thank present you.
3. One trigger, one job. Don't pile multiple unrelated concerns into one trigger body — split into separate triggers ordered with FOLLOWS.
4. Capture both OLD and NEW in audit triggers — knowing only "something changed" is rarely useful; knowing "price went from 50 to 60" is.
5. Keep trigger bodies fast. Remember the per-row multiplier — anything slow gets multiplied by the row count of the triggering statement.
6. Don't put complex business logic in triggers. If you need it, prefer application code or a stored procedure called explicitly.
7. Disable triggers for bulk loads when possible. Add a flag column or temporarily drop and recreate the trigger around the bulk operation.
8. Document with SHOW TRIGGERS output as part of your schema documentation. Triggers are easy to forget about.

• CREATE TRIGGER name {BEFORE|AFTER} {INSERT|UPDATE|DELETE} ON table FOR EACH ROW body; creates a row-level trigger.
• For multi-statement bodies, use BEGIN ... END with DELIMITER $$.
• OLD.col and NEW.col access the row data; availability depends on the event (no OLD on INSERT, no NEW on DELETE).
• BEFORE triggers can modify NEW via SET NEW.col = ... — last chance to alter the row.
• SHOW TRIGGERS lists triggers; information_schema.TRIGGERS gives programmatic access with joins and arbitrary filters.
• DROP TRIGGER IF EXISTS name; removes a trigger safely. Triggers also drop automatically when their table is dropped.
• Always use DROP TRIGGER IF EXISTS in deployment scripts for idempotence.