CODEWITHSUNDEEP
mysqltriggersphpmyadmin
Alberto Muñoz Sánchez
Alberto Muñoz Sánchez

Reputation: 167

Execute a trigger on the same table when updating

I want to execute a trigger in phpMyAdmin-MySQL that includes an UPDATE statement on the same table, like this:

create trigger AFTER INSERT ON Table
  FOR EACH ROW BEGIN
    UPDATE Table
      SET Total = NEW.Total + 1.03
      WHERE order_number = NEW.order_number;
   END

Maybe, it is not possible to get this. If not, is there any way to simulate this?

Upvotes: 0

Views: 64

Answers (1)

Nick
Nick

Reputation: 147166

MySQL has a constraint that you can't update the same table in a trigger. However, if order_number is unique to the INSERT, I think you can achieve the same result by modifying the Total value in a BEFORE INSERT trigger instead:

CREATE TRIGGER update_total BEFORE INSERT ON Table
FOR EACH ROW 
BEGIN
  SET NEW.Total = NEW.Total + 1.03;
END

Demo on dbfiddle

Upvotes: 2

Related Questions