How to create a Rigger After Update

Question

I need to create a Trigger that performs an update in column A of table T, as soon as column B of that table is updated. So I think I need to create an After Update. But from what I understand, it’s not possible. When trying to do this, the following error occurs:

16:03:54    UPDATE esms_conta SET valor_sms=0.3 WHERE id= 127   Error Code: 1442. Can't update table 'esms_conta' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.    0.000 sec

Just follow my Rigger

DELIMITER ;;
    CREATE TRIGGER `e-sms-db`.`esms_conta_AFTER_UPDATE` AFTER UPDATE ON `esms_conta`
     FOR EACH ROW
     BEGIN
         UPDATE esms_conta SET sms_disponivel = 10 WHERE id = NEW.id;
     END ;;
DELIMITER ;

Answer 1

You cannot mess with the data of the table itself that activated the trigger (Trigger). The solution is to create a Trigger before update:

DELIMITER ;;
CREATE TRIGGER `e-sms-db`.`esms_conta_b_u` BEFORE UPDATE ON `esms_conta`
FOR EACH ROW
BEGIN
    SET NEW.sms_disponivel = 10;
END ;;
DELIMITER ;