Functions & Triggers in MySQL

-- Create function
DELIMITER $$
CREATE FUNCTION CalculateTax(price DECIMAL(10,2))
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
  RETURN price * 0.08;
END$$
DELIMITER ;

-- Use function
SELECT price, CalculateTax(price) as tax
FROM products;

-- Create trigger
DELIMITER $$
CREATE TRIGGER update_modified
BEFORE UPDATE ON users
FOR EACH ROW
BEGIN
  SET NEW.modified_at = NOW();
END$$
DELIMITER ;

-- DETAILED_TAB:
-- Complex trigger with logging
DELIMITER $$
CREATE TRIGGER audit_user_changes
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
  INSERT INTO audit_log (
    table_name,
    record_id,
    action,
    old_values,
    new_values,
    changed_by,
    changed_at
  ) VALUES (
    'users',
    NEW.id,
    'UPDATE',
    JSON_OBJECT('name', OLD.name, 'email', OLD.email),
    JSON_OBJECT('name', NEW.name, 'email', NEW.email),
    USER(),
    NOW()
  );
END$$
DELIMITER ;

-- List triggers
SHOW TRIGGERS;
DROP TRIGGER IF EXISTS trigger_name;