Insert into same table trigger mysql

Question

I need to insert a discount line into a table everything time a I insert a line into the same table. Now i know that this could end in a endless loop but I have put checks in so it wont insert anything when the discount line is inserted.

Mysql doesnt seem to allow this.It doesnt even insert into the table let alone fire off the trigger

Any suggestions on how to do this?

Answer 1

You cannot alter a table (other than the current row) in a trigger attached to that table.

One solution is to insert into another table and have that trigger insert 2 rows into the table you're interested in.

If you make the other table a blackhole you don't have to worry about storage.

DELIMITER $$

CREATE TRIGGER ai_bh_test_each AFTER INSERT ON bh_test FOR EACH ROW
BEGIN
  INSERT INTO table1 (field1, field2, ...) VALUES (new.field1, new.field2, ....);
  INSERT INTO table1 ... values for the second row
END $$

DELIMITER ;

Answer 2

It isn't allowed in MySQL.

One solution would be to let the trigger insert two times into another table. Then you would do writes and updates to the write table and reads from the trigger managed read table.

Answer 3

Why don't you just change your INSERT code into something like this? :

INSERT INTO table1 (field1, field2, ...) 
VALUES ( @item, @price, ....)
     , ( @item, @discount, ...) ;

---

Another thing would be to re-evaluate your data structure. The way it is now, it seems - from the limited information you have provided - that it's not normalized. You are storing two types of info in the table.

Perhaps you can combine the two rows (that are to be inserted every time) into one, by adding a few columns in the table.

Or by splitting the table into two tables, one for the "normal" item rows and one for the discount items.