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.

Answer 4

I needed to add an additional row to the same table, based on a specific condition on an aggregate of the table and was unable to update my application queries to handle it outside of the database, due to stability lock policy.

An alternative solution is to utilize Events in MySQL to read a "staging" table that holds the pending changes. This works by eliminating the circular reference that would be caused by the trigger. The event then executes the desired changes, without initiating the trigger, by using a session variable to leave the trigger early. Please modify the event timing to suit your needs, such as EVERY 5 SECOND.

Staging Table

CREATE TABLE `table_staging` (
   `id` INT NOT NULL,
   `value` VARCHAR(250) NOT NULL,
   `added` TINYINT(1) NOT NULL DEFAULT '0',
   PRIMARY KEY (`id`)
);

Trigger

CREATE TRIGGER `table_after_insert` AFTER INSERT ON `table` FOR EACH ROW 
tableTrigger: BEGIN

    IF @EXIT_TRIGGER IS NOT NULL THEN
        /* do not execute the trigger */
        LEAVE tableTrigger;
    END IF;

    /* insert the record into staging table if it does not already exist */
    INSERT IGNORE INTO `table_staging`(`id`, `value`)
    VALUES (NEW.id, 'custom value');

END;

Event

CREATE EVENT `table_staging_add`
    ON SCHEDULE
        EVERY 1 MINUTE STARTS '2020-03-31 18:16:48'
    ON COMPLETION NOT PRESERVE
    ENABLE
    COMMENT ''
    DO BEGIN

    /* avoid executing if the event is currently running */
    IF @EXIT_TRIGGER IS NULL THEN

        SET @EXIT_TRIGGER=TRUE;

        /* add the values that have not already been updated */
        INSERT INTO table(`value`)
        SELECT 
            ts.value
        FROM table_staging AS ts
        WHERE ts.added = 0;

        /* update the records to exclude them on the next pass */
        UPDATE table_staging AS ts
        SET ts.added = 1;

        /* alternatively remove all the records from the staging table */
        /* TRUNCATE table_staging; */

        /* reset the state of execution */
        SET @EXIT_TRIGGER=NULL;

    END IF;
END;

Notes

Be sure to enable the event scheduler in your MySQL configuration (my.ini on Windows or my.cnf on Linux).

[mysqld]
event_scheduler = ON