Functions & Triggers

DELIMITER // -- Simple function that returns a calculated value CREATE FUNCTION CalculateTax(amount DECIMAL(10,2)) RETURNS DECIMAL(10,2) DETERMINISTIC BEGIN DECLARE tax_rate DECIMAL(5,4) DEFAULT 0.0825; RETURN amount * tax_rate; END // DELIMITER ; -- Use the function in queries SELECT order_id, total_amount, CalculateTax(total_amount) AS tax, total_amount + CalculateTax(total_amount) AS total_with_tax FROM orders;

DELIMITER // -- DETERMINISTIC: Same input always gives same output CREATE FUNCTION GetDiscount(total_spent DECIMAL(10,2)) RETURNS DECIMAL(5,2) DETERMINISTIC BEGIN RETURN CASE WHEN total_spent >= 10000 THEN 20.00 WHEN total_spent >= 5000 THEN 15.00 WHEN total_spent >= 1000 THEN 10.00 ELSE 0.00 END; END // -- NOT DETERMINISTIC: Output may vary (uses NOW(), RAND(), etc.) CREATE FUNCTION GetCurrentYear() RETURNS INT NOT DETERMINISTIC READS SQL DATA BEGIN RETURN YEAR(NOW()); END // -- NO SQL: Function doesn't contain SQL statements -- READS SQL DATA: Function reads data but doesn't modify -- MODIFIES SQL DATA: Function modifies data (use carefully!) DELIMITER ;

DELIMITER // -- Function to calculate customer loyalty tier CREATE FUNCTION GetCustomerTier(customer_id_param INT) RETURNS VARCHAR(20) READS SQL DATA BEGIN DECLARE total_spent DECIMAL(10,2); DECLARE order_count INT; DECLARE tier VARCHAR(20); SELECT COALESCE(SUM(total_amount), 0), COUNT(*) INTO total_spent, order_count FROM orders WHERE customer_id = customer_id_param; IF total_spent > 10000 OR order_count > 20 THEN SET tier = 'Platinum'; ELSEIF total_spent > 5000 OR order_count > 10 THEN SET tier = 'Gold'; ELSEIF total_spent > 1000 OR order_count > 5 THEN SET tier = 'Silver'; ELSE SET tier = 'Bronze'; END IF; RETURN tier; END // -- Calculate string similarity (Levenshtein distance simplified) CREATE FUNCTION StringSimilarity(str1 VARCHAR(255), str2 VARCHAR(255)) RETURNS INT DETERMINISTIC BEGIN DECLARE len1 INT DEFAULT LENGTH(str1); DECLARE len2 INT DEFAULT LENGTH(str2); IF str1 = str2 THEN RETURN 100; ELSEIF len1 = 0 OR len2 = 0 THEN RETURN 0; END IF; -- Simplified similarity based on common characters RETURN ROUND( (LENGTH(str1) + LENGTH(str2) - LENGTH(REPLACE(CONCAT(str1, str2), SUBSTRING(str1, 1, 1), ''))) * 100 / (LENGTH(str1) + LENGTH(str2)) ); END // DELIMITER ; -- Use functions in queries SELECT customer_id, customer_name, GetCustomerTier(customer_id) AS tier, GetDiscount(total_spent) AS discount_rate FROM customers ORDER BY total_spent DESC;

Functions: ✓ Must return a single value ✓ Can be used in SELECT, WHERE, HAVING ✓ Cannot modify data (usually) ✓ Cannot use OUT/INOUT parameters ✓ Can be part of expressions Procedures: ✓ May or may not return values ✓ Cannot be used in SELECT directly ✓ Can modify data ✓ Can use OUT/INOUT parameters ✓ Called with CALL statement

DELIMITER // -- BEFORE INSERT trigger: validates data before insertion CREATE TRIGGER validate_product_price 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_quantity < 0 THEN SET NEW.stock_quantity = 0; END IF; END // -- AFTER INSERT trigger: perform action after insertion CREATE TRIGGER log_new_customer AFTER INSERT ON customers FOR EACH ROW BEGIN INSERT INTO audit_log (table_name, action, record_id, created_at) VALUES ('customers', 'INSERT', NEW.customer_id, NOW()); END // DELIMITER ;

BEFORE Triggers: - Execute before the operation - Can modify NEW values - Can prevent operation with SIGNAL - Use for validation and data modification AFTER Triggers: - Execute after the operation - Cannot modify NEW values - Cannot prevent operation - Use for logging and cascading changes

DELIMITER // -- Track changes to sensitive data CREATE TRIGGER track_price_changes BEFORE UPDATE ON products FOR EACH ROW BEGIN -- Only log if price actually changed IF NEW.price != OLD.price THEN INSERT INTO price_history ( product_id, old_price, new_price, changed_by, changed_at ) VALUES ( OLD.product_id, OLD.price, NEW.price, USER(), NOW() ); END IF; END // -- Automatically update timestamps CREATE TRIGGER update_product_timestamp BEFORE UPDATE ON products FOR EACH ROW BEGIN SET NEW.updated_at = NOW(); END // -- Prevent unauthorized changes CREATE TRIGGER protect_archived_orders BEFORE UPDATE ON orders FOR EACH ROW BEGIN IF OLD.status = 'archived' THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Cannot modify archived orders'; END IF; END // DELIMITER ;

DELIMITER // -- Soft delete implementation CREATE TRIGGER soft_delete_customer BEFORE DELETE ON customers FOR EACH ROW BEGIN -- Instead of deleting, mark as inactive INSERT INTO deleted_customers SELECT *, NOW() AS deleted_at FROM customers WHERE customer_id = OLD.customer_id; -- Prevent actual deletion SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Use soft delete instead'; END // -- Cascade delete with logging CREATE TRIGGER log_order_deletion BEFORE DELETE ON orders FOR EACH ROW BEGIN -- Log the deletion INSERT INTO audit_log ( table_name, action, record_id, old_data, created_at ) VALUES ( 'orders', 'DELETE', OLD.order_id, JSON_OBJECT( 'customer_id', OLD.customer_id, 'total_amount', OLD.total_amount, 'status', OLD.status ), NOW() ); -- Delete related order items DELETE FROM order_items WHERE order_id = OLD.order_id; END // DELIMITER ;

-- Create comprehensive audit log table CREATE TABLE audit_trail ( audit_id INT AUTO_INCREMENT PRIMARY KEY, table_name VARCHAR(64), operation VARCHAR(10), record_id INT, old_values JSON, new_values JSON, changed_by VARCHAR(255), changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); DELIMITER // -- Generic audit trigger for INSERT CREATE TRIGGER audit_product_insert AFTER INSERT ON products FOR EACH ROW BEGIN INSERT INTO audit_trail ( table_name, operation, record_id, new_values, changed_by ) VALUES ( 'products', 'INSERT', NEW.product_id, JSON_OBJECT( 'product_name', NEW.product_name, 'price', NEW.price, 'stock_quantity', NEW.stock_quantity ), USER() ); END // -- Generic audit trigger for UPDATE CREATE TRIGGER audit_product_update AFTER UPDATE ON products FOR EACH ROW BEGIN INSERT INTO audit_trail ( table_name, operation, record_id, old_values, new_values, changed_by ) VALUES ( 'products', 'UPDATE', NEW.product_id, JSON_OBJECT( 'product_name', OLD.product_name, 'price', OLD.price, 'stock_quantity', OLD.stock_quantity ), JSON_OBJECT( 'product_name', NEW.product_name, 'price', NEW.price, 'stock_quantity', NEW.stock_quantity ), USER() ); END // -- Generic audit trigger for DELETE CREATE TRIGGER audit_product_delete BEFORE DELETE ON products FOR EACH ROW BEGIN INSERT INTO audit_trail ( table_name, operation, record_id, old_values, changed_by ) VALUES ( 'products', 'DELETE', OLD.product_id, JSON_OBJECT( 'product_name', OLD.product_name, 'price', OLD.price, 'stock_quantity', OLD.stock_quantity ), USER() ); END // DELIMITER ;

DELIMITER // -- Automatically update order totals CREATE TRIGGER calculate_order_total AFTER INSERT ON order_items FOR EACH ROW BEGIN UPDATE orders SET total_amount = ( SELECT SUM(quantity * unit_price) FROM order_items WHERE order_id = NEW.order_id ) WHERE order_id = NEW.order_id; END // -- Update customer statistics CREATE TRIGGER update_customer_stats AFTER INSERT ON orders FOR EACH ROW BEGIN UPDATE customers SET total_orders = total_orders + 1, total_spent = total_spent + NEW.total_amount, last_order_date = NEW.order_date WHERE customer_id = NEW.customer_id; END // DELIMITER ;

-- Show all triggers in current database SHOW TRIGGERS; -- Show triggers for specific table SHOW TRIGGERS WHERE `Table` = 'products'; -- Show trigger definition SHOW CREATE TRIGGER validate_product_price; -- Drop a trigger DROP TRIGGER IF EXISTS validate_product_price; -- Query information_schema for trigger details SELECT TRIGGER_NAME, EVENT_MANIPULATION, EVENT_OBJECT_TABLE, ACTION_TIMING, ACTION_STATEMENT FROM information_schema.TRIGGERS WHERE TRIGGER_SCHEMA = 'your_database' ORDER BY EVENT_OBJECT_TABLE, ACTION_TIMING;

✓ Keep triggers simple and fast ✓ Avoid complex logic in triggers ✓ Don't create triggers that call triggers (recursive) ✓ Use triggers for data integrity, not business logic ✓ Document all triggers thoroughly ✓ Test triggers extensively ✓ Consider performance impact ✓ Use BEFORE triggers for validation ✓ Use AFTER triggers for logging ✗ Don't put all business logic in triggers ✗ Avoid triggers that modify multiple tables ✗ Don't use triggers as a replacement for application logic

DELIMITER // -- Function to check stock availability CREATE FUNCTION CheckStockAvailable( p_product_id INT, p_quantity INT ) RETURNS BOOLEAN READS SQL DATA BEGIN DECLARE available INT; SELECT stock_quantity INTO available FROM products WHERE product_id = p_product_id; RETURN available >= p_quantity; END // -- Trigger to validate and update inventory CREATE TRIGGER validate_order_item BEFORE INSERT ON order_items FOR EACH ROW BEGIN IF NOT CheckStockAvailable(NEW.product_id, NEW.quantity) THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Insufficient stock for this product'; END IF; -- Reserve inventory UPDATE products SET stock_quantity = stock_quantity - NEW.quantity WHERE product_id = NEW.product_id; -- Log inventory change INSERT INTO inventory_log ( product_id, change_type, quantity_change, reason, created_at ) VALUES ( NEW.product_id, 'SALE', -NEW.quantity, CONCAT('Order item #', NEW.order_item_id), NOW() ); END // DELIMITER ;