How to fix ERROR 1422 (HY000): Explicit or implicit commit is not allowed here in MySQL

-- Show the creation code of the stored function/trigger
SHOW CREATE FUNCTION function_name;
SHOW CREATE TRIGGER trigger_name;

-- Check for problematic statements in MySQL Workbench or command-line
-- Look for these keywords inside the function/trigger body:
-- - COMMIT
-- - ROLLBACK  
-- - START TRANSACTION or BEGIN (if not part of BEGIN...END block)
-- - TRUNCATE
-- - DDL statements (ALTER, CREATE, DROP)
-- - Administrative statements

CREATE TRIGGER my_trigger AFTER INSERT ON users
FOR EACH ROW
BEGIN
  INSERT INTO audit_log VALUES (NEW.id, NOW());
  COMMIT;  -- ERROR 1422: This is not allowed in triggers
END;

CREATE FUNCTION check_user(user_id INT) RETURNS BOOLEAN
BEGIN
  DECLARE count INT;
  SELECT COUNT(*) INTO count FROM users WHERE id = user_id;
  COMMIT;  -- ERROR 1422: Functions cannot have COMMIT
  RETURN count > 0;
END;

-- WRONG: Function with COMMIT
CREATE FUNCTION get_user_count() RETURNS INT
BEGIN
  DECLARE cnt INT;
  SELECT COUNT(*) INTO cnt FROM users;
  COMMIT;  -- ERROR 1422
  RETURN cnt;
END;

-- CORRECT: Remove the COMMIT statement
CREATE FUNCTION get_user_count() RETURNS INT
BEGIN
  DECLARE cnt INT;
  SELECT COUNT(*) INTO cnt FROM users;
  RETURN cnt;
END;

-- WRONG: Trigger with ROLLBACK
CREATE TRIGGER validate_user BEFORE INSERT ON users
FOR EACH ROW
BEGIN
  IF NEW.email IS NULL THEN
    ROLLBACK;  -- ERROR 1422
  END IF;
END;

-- CORRECT: Use SIGNAL to raise error instead
CREATE TRIGGER validate_user BEFORE INSERT ON users
FOR EACH ROW
BEGIN
  IF NEW.email IS NULL THEN
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'Email cannot be null';
  END IF;
END;

-- WRONG: TRUNCATE in trigger (causes implicit commit)
CREATE TRIGGER reset_cache AFTER DELETE ON items
FOR EACH ROW
BEGIN
  TRUNCATE TABLE cache_table;  -- ERROR 1422
END;

-- CORRECT: Use DELETE instead
CREATE TRIGGER reset_cache AFTER DELETE ON items
FOR EACH ROW
BEGIN
  DELETE FROM cache_table;
END;

-- WRONG: TRUNCATE in function
CREATE FUNCTION clear_logs() RETURNS INT
BEGIN
  TRUNCATE TABLE logs;  -- ERROR 1422
  RETURN 1;
END;

-- CORRECT: Use DELETE
CREATE FUNCTION clear_logs() RETURNS INT
BEGIN
  DELETE FROM logs;
  RETURN ROW_COUNT();
END;

-- WRONG: ALTER TABLE inside function
CREATE FUNCTION add_column() RETURNS INT
BEGIN
  ALTER TABLE users ADD COLUMN new_field INT;  -- ERROR 1422
  RETURN 1;
END;

-- CORRECT: Execute schema changes outside stored routines
-- Run DDL in application code, migrations, or manually:
ALTER TABLE users ADD COLUMN new_field INT;

-- Or use a stored procedure (not function) for complex operations
CREATE PROCEDURE schema_migration()
BEGIN
  -- Can use transaction control, but cannot be called from triggers
  ALTER TABLE users ADD COLUMN new_field INT;
  COMMIT;
END;

-- WRONG: Function with complex transaction logic
CREATE FUNCTION transfer_money(from_id INT, to_id INT, amount DECIMAL) RETURNS BOOLEAN
BEGIN
  START TRANSACTION;  -- ERROR 1422
  UPDATE accounts SET balance = balance - amount WHERE id = from_id;
  UPDATE accounts SET balance = balance + amount WHERE id = to_id;
  COMMIT;  -- ERROR 1422
  RETURN TRUE;
END;

-- CORRECT: Use a stored procedure instead
CREATE PROCEDURE transfer_money(from_id INT, to_id INT, amount DECIMAL, OUT success BOOLEAN)
BEGIN
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    ROLLBACK;
    SET success = FALSE;
  END;

  START TRANSACTION;
  UPDATE accounts SET balance = balance - amount WHERE id = from_id;
  UPDATE accounts SET balance = balance + amount WHERE id = to_id;
  COMMIT;
  SET success = TRUE;
END;

-- Call the procedure from your application
CALL transfer_money(1, 2, 100.00, @success);
SELECT @success;

-- WRONG: Trying to rollback in trigger
CREATE TRIGGER validate_salary BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
  IF NEW.salary < 0 THEN
    ROLLBACK;  -- ERROR 1422
  END IF;
END;

-- CORRECT: Use SIGNAL to raise error
CREATE TRIGGER validate_salary BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
  IF NEW.salary < 0 THEN
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'Salary cannot be negative';
  END IF;
END;

-- Comprehensive error handling example
CREATE TRIGGER process_order BEFORE INSERT ON orders
FOR EACH ROW
BEGIN
  -- Validate inventory
  IF (SELECT quantity FROM inventory WHERE product_id = NEW.product_id) < NEW.qty THEN
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'Insufficient inventory';
  END IF;
  
  -- Validate customer
  IF NOT EXISTS (SELECT 1 FROM customers WHERE id = NEW.customer_id) THEN
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'Customer not found';
  END IF;
  
  -- Update inventory (implicit part of trigger transaction)
  UPDATE inventory SET quantity = quantity - NEW.qty 
  WHERE product_id = NEW.product_id;
END;

-- Test function without transaction control
DELIMITER //

CREATE FUNCTION get_user_summary(user_id INT) RETURNS VARCHAR(500)
BEGIN
  DECLARE summary VARCHAR(500);
  SELECT CONCAT(first_name, ' ', last_name, ' - ', email)
  INTO summary
  FROM users
  WHERE id = user_id;
  RETURN COALESCE(summary, 'User not found');
END //

DELIMITER ;

-- Test the function
SELECT get_user_summary(1);
SELECT get_user_summary(999);  -- Non-existent user

-- Test trigger behavior
DELIMITER //

CREATE TRIGGER log_user_creation AFTER INSERT ON users
FOR EACH ROW
BEGIN
  INSERT INTO audit_log (action, user_id, created_at) 
  VALUES ('CREATE_USER', NEW.id, NOW());
END //

DELIMITER ;

-- Test trigger by inserting data
INSERT INTO users (first_name, last_name, email) 
VALUES ('John', 'Doe', '[email protected]');

-- Verify trigger executed
SELECT * FROM audit_log WHERE user_id = LAST_INSERT_ID();

-- Verify error handling works
-- This INSERT should fail with trigger validation error
INSERT INTO employees (name, salary) VALUES ('Test', -1000);
-- Expected: Error 1644 - SIGNAL error from trigger validation