Saltar al contenido principal

Database Triggers for Business Logic and Audit Trails

Database triggers allow you to execute code automatically when specific events occur in your database. This post demonstrates how to use triggers for business logic, data validation, and audit trails in your example application.

🎯 What are Database Triggers?​

Triggers are stored procedures that automatically execute in response to specific database events:

  • BEFORE INSERT: Before a new row is inserted
  • AFTER INSERT: After a new row is inserted
  • BEFORE UPDATE: Before a row is updated
  • AFTER UPDATE: After a row is updated
  • BEFORE DELETE: Before a row is deleted
  • AFTER DELETE: After a row is deleted

Benefits​

  • Data integrity: Enforce business rules at database level
  • Audit trails: Track all data changes automatically
  • Automatic calculations: Compute derived values
  • Consistency: Ensure rules apply regardless of application

📊 Audit Trail Implementation​

Create Audit Table​

First, create a table to store audit logs:

CREATE TABLE audit_log (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    table_name VARCHAR(100) NOT NULL,
    record_id INT NOT NULL,
    action VARCHAR(10) NOT NULL, -- INSERT, UPDATE, DELETE
    old_values JSON,
    new_values JSON,
    changed_by VARCHAR(100),
    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_table_record (table_name, record_id),
    INDEX idx_changed_at (changed_at)
) ENGINE=InnoDB;

Customer Audit Trigger​

Create trigger to audit customer changes:

DELIMITER //

CREATE TRIGGER customers_audit_insert
AFTER INSERT ON customers
FOR EACH ROW
BEGIN
    INSERT INTO audit_log (
        table_name,
        record_id,
        action,
        new_values,
        changed_by
    ) VALUES (
        'customers',
        NEW.id,
        'INSERT',
        JSON_OBJECT(
            'name', NEW.name,
            'email', NEW.email,
            'phone', NEW.phone,
            'address', NEW.address
        ),
        USER()
    );
END //

CREATE TRIGGER customers_audit_update
AFTER UPDATE ON customers
FOR EACH ROW
BEGIN
    INSERT INTO audit_log (
        table_name,
        record_id,
        action,
        old_values,
        new_values,
        changed_by
    ) VALUES (
        'customers',
        NEW.id,
        'UPDATE',
        JSON_OBJECT(
            'name', OLD.name,
            'email', OLD.email,
            'phone', OLD.phone,
            'address', OLD.address
        ),
        JSON_OBJECT(
            'name', NEW.name,
            'email', NEW.email,
            'phone', NEW.phone,
            'address', NEW.address
        ),
        USER()
    );
END //

CREATE TRIGGER customers_audit_delete
AFTER DELETE ON customers
FOR EACH ROW
BEGIN
    INSERT INTO audit_log (
        table_name,
        record_id,
        action,
        old_values,
        changed_by
    ) VALUES (
        'customers',
        OLD.id,
        'DELETE',
        JSON_OBJECT(
            'name', OLD.name,
            'email', OLD.email,
            'phone', OLD.phone,
            'address', OLD.address
        ),
        USER()
    );
END //

DELIMITER ;

💰 Business Logic: Automatic Total Calculation​

Sale Total Trigger​

Automatically calculate sale total when items are added:

DELIMITER //

CREATE TRIGGER sale_items_after_insert
AFTER INSERT ON sale_items
FOR EACH ROW
BEGIN
    UPDATE sales
    SET total = (
        SELECT SUM(subtotal)
        FROM sale_items
        WHERE sale_id = NEW.sale_id
    ),
    updated_at = CURRENT_TIMESTAMP
    WHERE id = NEW.sale_id;
END //

CREATE TRIGGER sale_items_after_update
AFTER UPDATE ON sale_items
FOR EACH ROW
BEGIN
    UPDATE sales
    SET total = (
        SELECT SUM(subtotal)
        FROM sale_items
        WHERE sale_id = NEW.sale_id
    ),
    updated_at = CURRENT_TIMESTAMP
    WHERE id = NEW.sale_id;
END //

CREATE TRIGGER sale_items_after_delete
AFTER DELETE ON sale_items
FOR EACH ROW
BEGIN
    UPDATE sales
    SET total = COALESCE((
        SELECT SUM(subtotal)
        FROM sale_items
        WHERE sale_id = OLD.sale_id
    ), 0),
    updated_at = CURRENT_TIMESTAMP
    WHERE id = OLD.sale_id;
END //

DELIMITER ;

📦 Stock Management Triggers​

Automatic Stock Updates​

Update product stock when sales occur:

DELIMITER //

CREATE TRIGGER sale_items_stock_decrement
AFTER INSERT ON sale_items
FOR EACH ROW
BEGIN
    UPDATE products
    SET stock = stock - NEW.quantity,
        updated_at = CURRENT_TIMESTAMP
    WHERE id = NEW.product_id;
END //

CREATE TRIGGER sale_items_stock_restore
AFTER DELETE ON sale_items
FOR EACH ROW
BEGIN
    UPDATE products
    SET stock = stock + OLD.quantity,
        updated_at = CURRENT_TIMESTAMP
    WHERE id = OLD.product_id;
END //

CREATE TRIGGER sale_items_stock_update
AFTER UPDATE ON sale_items
FOR EACH ROW
BEGIN
    -- Restore old quantity
    UPDATE products
    SET stock = stock + OLD.quantity,
        updated_at = CURRENT_TIMESTAMP
    WHERE id = OLD.product_id;
    
    -- Decrement new quantity
    UPDATE products
    SET stock = stock - NEW.quantity,
        updated_at = CURRENT_TIMESTAMP
    WHERE id = NEW.product_id;
END //

DELIMITER ;

Stock Validation​

Prevent sales when stock is insufficient:

DELIMITER //

CREATE TRIGGER sale_items_stock_check
BEFORE INSERT ON sale_items
FOR EACH ROW
BEGIN
    DECLARE current_stock INT;
    
    SELECT stock INTO current_stock
    FROM products
    WHERE id = NEW.product_id;
    
    IF current_stock < NEW.quantity THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Insufficient stock for product';
    END IF;
END //

DELIMITER ;

🔔 Low Stock Alerts​

Create Alert Table​

CREATE TABLE stock_alerts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    product_id INT NOT NULL,
    current_stock INT NOT NULL,
    threshold INT NOT NULL,
    alert_type VARCHAR(20) NOT NULL, -- 'LOW', 'OUT'
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    resolved_at TIMESTAMP NULL,
    FOREIGN KEY (product_id) REFERENCES products(id),
    INDEX idx_product (product_id),
    INDEX idx_resolved (resolved_at)
);

Low Stock Trigger​

DELIMITER //

CREATE TRIGGER products_low_stock_check
AFTER UPDATE ON products
FOR EACH ROW
BEGIN
    DECLARE threshold INT DEFAULT 10;
    
    -- Check if stock dropped below threshold
    IF NEW.stock < threshold AND OLD.stock >= threshold THEN
        INSERT INTO stock_alerts (
            product_id,
            current_stock,
            threshold,
            alert_type
        ) VALUES (
            NEW.id,
            NEW.stock,
            threshold,
            'LOW'
        );
    END IF;
    
    -- Check if stock is out
    IF NEW.stock = 0 AND OLD.stock > 0 THEN
        INSERT INTO stock_alerts (
            product_id,
            current_stock,
            threshold,
            alert_type
        ) VALUES (
            NEW.id,
            NEW.stock,
            0,
            'OUT'
        );
    END IF;
    
    -- Resolve alert if stock restored
    IF NEW.stock >= threshold AND OLD.stock < threshold THEN
        UPDATE stock_alerts
        SET resolved_at = CURRENT_TIMESTAMP
        WHERE product_id = NEW.id
        AND resolved_at IS NULL
        AND alert_type = 'LOW';
    END IF;
END //

DELIMITER ;

📈 Price History Tracking​

Create Price History Table​

CREATE TABLE price_history (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    product_id INT NOT NULL,
    old_price DECIMAL(10,2),
    new_price DECIMAL(10,2),
    changed_by VARCHAR(100),
    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (product_id) REFERENCES products(id),
    INDEX idx_product (product_id),
    INDEX idx_changed_at (changed_at)
);

Price Change Trigger​

DELIMITER //

CREATE TRIGGER products_price_history
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_by
        ) VALUES (
            NEW.id,
            OLD.price,
            NEW.price,
            USER()
        );
    END IF;
END //

DELIMITER ;

🔒 Data Validation Triggers​

Email Validation​

DELIMITER //

CREATE TRIGGER customers_email_validation
BEFORE INSERT ON customers
FOR EACH ROW
BEGIN
    IF NEW.email NOT REGEXP '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\\.[A-Za-z]{2,}$' THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Invalid email format';
    END IF;
END //

CREATE TRIGGER customers_email_validation_update
BEFORE UPDATE ON customers
FOR EACH ROW
BEGIN
    IF NEW.email NOT REGEXP '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\\.[A-Za-z]{2,}$' THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Invalid email format';
    END IF;
END //

DELIMITER ;

Price Validation​

DELIMITER //

CREATE TRIGGER products_price_validation
BEFORE INSERT ON products
FOR EACH ROW
BEGIN
    IF NEW.price < 0 THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Price cannot be negative';
    END IF;
    
    IF NEW.stock < 0 THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Stock cannot be negative';
    END IF;
END //

DELIMITER ;

📊 Viewing and Managing Triggers​

List All Triggers​

-- Show all triggers
SHOW TRIGGERS;

-- Show triggers for specific table
SHOW TRIGGERS FROM filess_example LIKE 'customers%';

-- Query information schema
SELECT 
    TRIGGER_NAME,
    EVENT_MANIPULATION,
    EVENT_OBJECT_TABLE,
    ACTION_STATEMENT
FROM INFORMATION_SCHEMA.TRIGGERS
WHERE TRIGGER_SCHEMA = 'filess_example';

Drop Triggers​

DROP TRIGGER IF EXISTS customers_audit_insert;
DROP TRIGGER IF EXISTS customers_audit_update;
DROP TRIGGER IF EXISTS customers_audit_delete;

Modify Triggers​

Triggers cannot be modified directly. You must drop and recreate:

-- Drop old trigger
DROP TRIGGER customers_audit_insert;

-- Create new version
CREATE TRIGGER customers_audit_insert
AFTER INSERT ON customers
FOR EACH ROW
BEGIN
    -- Updated logic here
END;

🧪 Testing Triggers​

Test Audit Trail​

-- Insert customer
INSERT INTO customers (name, email, phone) 
VALUES ('Test User', '[email protected]', '555-0100');

-- Check audit log
SELECT * FROM audit_log 
WHERE table_name = 'customers' 
ORDER BY changed_at DESC 
LIMIT 1;

Test Stock Management​

-- Create sale with items
INSERT INTO sales (customer_id, total, status) 
VALUES (1, 0, 'pending');

INSERT INTO sale_items (sale_id, product_id, quantity, price, subtotal)
VALUES (1, 1, 2, 29.99, 59.98);

-- Check stock was decremented
SELECT id, name, stock FROM products WHERE id = 1;

-- Check sale total was calculated
SELECT id, total FROM sales WHERE id = 1;

Test Validation​

-- Try invalid email (should fail)
INSERT INTO customers (name, email) 
VALUES ('Test', 'invalid-email');
-- Error: Invalid email format

-- Try negative price (should fail)
INSERT INTO products (name, price, stock, sku) 
VALUES ('Test', -10.00, 10, 'TEST-001');
-- Error: Price cannot be negative

🎯 Integration with Example App​

Query Audit Trail​

// src/services/audit.service.ts
export class AuditService {
  async getAuditLog(tableName: string, recordId: number) {
    return prisma.$queryRaw`
      SELECT * FROM audit_log
      WHERE table_name = ${tableName}
      AND record_id = ${recordId}
      ORDER BY changed_at DESC
    `;
  }
  
  async getRecentChanges(hours: number = 24) {
    return prisma.$queryRaw`
      SELECT * FROM audit_log
      WHERE changed_at >= DATE_SUB(NOW(), INTERVAL ${hours} HOUR)
      ORDER BY changed_at DESC
    `;
  }
}

Check Stock Alerts​

// src/services/alert.service.ts
export class AlertService {
  async getActiveAlerts() {
    return prisma.$queryRaw`
      SELECT sa.*, p.name as product_name
      FROM stock_alerts sa
      JOIN products p ON sa.product_id = p.id
      WHERE sa.resolved_at IS NULL
      ORDER BY sa.created_at DESC
    `;
  }
}

⚠️ Trigger Best Practices​

  1. Keep triggers simple: Complex logic belongs in application code
  2. Avoid recursive triggers: Triggers that modify the same table
  3. Handle errors: Use SIGNAL for validation errors
  4. Performance: Triggers execute for every row - keep them fast
  5. Documentation: Document all triggers and their purposes
  6. Testing: Test triggers thoroughly before production
  7. Monitoring: Monitor trigger performance

🔍 Debugging Triggers​

Enable General Query Log​

SET GLOBAL general_log = 'ON';
SET GLOBAL log_output = 'TABLE';

-- View trigger executions
SELECT * FROM mysql.general_log 
WHERE command_type = 'Query' 
AND argument LIKE '%TRIGGER%';

Add Debug Logging​

DELIMITER //

CREATE TRIGGER debug_trigger
AFTER INSERT ON customers
FOR EACH ROW
BEGIN
    -- Log to a debug table
    INSERT INTO debug_log (message, created_at)
    VALUES (CONCAT('Customer inserted: ', NEW.id), NOW());
END //

DELIMITER ;

✅ Checklist​

  • Audit triggers created for all critical tables
  • Business logic triggers implemented
  • Validation triggers in place
  • Stock management triggers working
  • Triggers tested thoroughly
  • Performance impact assessed
  • Documentation updated
  • Monitoring configured

Database triggers provide a powerful way to enforce business rules and maintain data integrity at the database level, ensuring consistency regardless of how data is accessed.