Error while running a Trigger

Asked

Viewed 54 times

1

I have a problem in a Rigger that when I do an input of a record I want to automatically pass a value that comes by default is 1 to 0 only it is always giving this error.

Can’t update table 'receipts' in stored Function/Trigger because it is already used by statement which Invoked this stored Function/Trigger.

Trigger

CREATE TRIGGER actrecibo AFTER INSERT on Recibos
FOR EACH ROW 
BEGIN
        UPDATE recibos set is_new = 0 WHERE id = NEW.id;
END

Someone knows how to look for this to happen and how I can fix it ?

mysql trigger

  • According to that answer you’re not allowed to do that. http://stackoverflow.com/questions/11247590/mysql-trigger-set-values-for-new-row-and-update-ther-in-the-sametable

2 answers

1

I didn’t understand the context of the use of Trigger, but analyzing only the code I realized the following.

After the INSERT you are updating the field is_new = 0 of the registration you have just entered.

If that’s it you can try it like this:

CREATE TRIGGER actrecibo BEFORE INSERT on Recibos
FOR EACH ROW 
BEGIN
      SET NEW.is_new = 0;
END

0

I believe with BEFORE you can do this.

CREATE TRIGGER actrecibo BEFORE INSERT on Recibos
FOR EACH ROW 
BEGIN
      SET NEW.id = ( SELECT MAX(id) FROM Recibos ) + 1
END

Browser other questions tagged mysql trigger

You are not signed in. Login or sign up in order to post.