How to create aTrigger After Update

Question

How to create aTrigger After Update

Navigation

#1 by (0 votes)

0

I need to create a Trigger that performs an update on column A of table T, as soon as column B of this table is updated. So I believe I need to create an After Update. But from what I understand, it is not possible. When you try 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

Follow my trigger

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 ;

mysql trigger

asked by anonymous 03.08.2016 / 21:12

1 answer

Map for Unity3D [closed] Use Ionic 2 for Windows Phone / Windows Universal applications?

score 0 · Answer 1

You can not manipulate the data in the table that triggered the 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 ;