MySQL Triggers

Trigger Types

CREATE TRIGGER before_insert
BEFORE INSERT ON table_name

CREATE TRIGGER after_update
AFTER UPDATE ON table_name

-- Fires when new row added
BEFORE/AFTER INSERT

-- Fires when row modified
BEFORE/AFTER UPDATE

🔹 Creating Triggers

Create triggers using CREATE TRIGGER statement. Specify timing (BEFORE or AFTER), event (INSERT, UPDATE, DELETE), and table name. Use NEW to reference new row values and OLD for previous values. The trigger body contains SQL statements executed automatically when the event occurs.

-- BEFORE INSERT trigger
DELIMITER //
CREATE TRIGGER validate_email
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
    IF NEW.email NOT LIKE '%@%' THEN
        SIGNAL SQLSTATE '45000' 
        SET MESSAGE_TEXT = 'Invalid email format';
    END IF;
END //
DELIMITER ;

-- AFTER UPDATE trigger
DELIMITER //
CREATE TRIGGER log_price_change
AFTER UPDATE ON products
FOR EACH ROW
BEGIN
    IF OLD.price != NEW.price THEN
        INSERT INTO price_history (product_id, old_price, new_price, changed_at)
        VALUES (NEW.id, OLD.price, NEW.price, NOW());
    END IF;
END //
DELIMITER ;

🔹 BEFORE Triggers

BEFORE triggers execute before the INSERT, UPDATE, or DELETE operation. They can modify NEW values before data is written to the table. Use BEFORE triggers for data validation, setting default values, or preventing invalid operations by raising errors with SIGNAL statement.

-- Auto-set timestamp before insert
DELIMITER //
CREATE TRIGGER set_created_date
BEFORE INSERT ON orders
FOR EACH ROW
BEGIN
    SET NEW.created_at = NOW();
    SET NEW.status = 'pending';
END //
DELIMITER ;

-- Validate data before update
DELIMITER //
CREATE TRIGGER check_salary
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
    IF NEW.salary < 0 THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Salary cannot be negative';
    END IF;
END //
DELIMITER ;

🔹 AFTER Triggers

AFTER triggers execute after the INSERT, UPDATE, or DELETE operation completes. They cannot modify the affected row but can perform additional operations like logging, updating related tables, or sending notifications. AFTER triggers are ideal for audit trails and maintaining derived data.

-- Log deletions
DELIMITER //
CREATE TRIGGER log_user_deletion
AFTER DELETE ON users
FOR EACH ROW
BEGIN
    INSERT INTO deleted_users_log (user_id, username, deleted_at)
    VALUES (OLD.id, OLD.username, NOW());
END //
DELIMITER ;

-- Update inventory after sale
DELIMITER //
CREATE TRIGGER update_stock
AFTER INSERT ON order_items
FOR EACH ROW
BEGIN
    UPDATE products 
    SET stock = stock - NEW.quantity
    WHERE id = NEW.product_id;
END //
DELIMITER ;

🔹 Using NEW and OLD

NEW refers to the new row being inserted or the updated values in an UPDATE. OLD refers to the existing row being updated or deleted. INSERT triggers can only use NEW. DELETE triggers can only use OLD. UPDATE triggers can use both to compare old and new values.

-- Using NEW in INSERT trigger
DELIMITER //
CREATE TRIGGER uppercase_username
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
    SET NEW.username = UPPER(NEW.username);
END //
DELIMITER ;

-- Using OLD and NEW in UPDATE trigger
DELIMITER //
CREATE TRIGGER track_changes
AFTER UPDATE ON products
FOR EACH ROW
BEGIN
    IF OLD.price != NEW.price THEN
        INSERT INTO audit_log (table_name, record_id, old_value, new_value)
        VALUES ('products', NEW.id, OLD.price, NEW.price);
    END IF;
END //
DELIMITER ;

INSERT INTO users (username) VALUES ('john');
-- Stored as: 'JOHN' (automatically uppercased)

🔹 Viewing and Dropping Triggers

List all triggers using SHOW TRIGGERS or query information_schema. View trigger definition with SHOW CREATE TRIGGER. Drop triggers with DROP TRIGGER when no longer needed. Each table can have multiple triggers but only one trigger per timing-event combination per table.

-- Show all triggers in database
SHOW TRIGGERS;

-- Show triggers for specific table
SHOW TRIGGERS WHERE `Table` = 'users';

-- View trigger definition
SHOW CREATE TRIGGER before_user_insert;

-- Drop a trigger
DROP TRIGGER IF EXISTS before_user_insert;

-- Query information schema
SELECT * FROM information_schema.triggers 
WHERE trigger_schema = 'your_database';

🔹 Trigger Best Practices

Keep triggers simple and focused on single tasks. Avoid complex logic that slows down operations. Don't create triggers that modify the same table they're triggered on to prevent infinite loops. Document trigger purposes clearly. Test triggers thoroughly as they execute automatically and can affect data integrity.