-- Trigger: Automatically deduct balance when withdrawal is approved

DELIMITER $$

CREATE TRIGGER after_withdrawal_approved
AFTER UPDATE ON withdrawals
FOR EACH ROW
BEGIN
    -- Check if status changed from 'pending' to 'approved'
    IF OLD.status = 'pending' AND NEW.status = 'approved' THEN
        -- Deduct the amount from user's balance
        UPDATE users 
        SET balance = balance - NEW.amount 
        WHERE id = NEW.user_id;
    END IF;
END$$

DELIMITER ;