Prabhash Dilhan Akmeemana Prabhash Dilhan Akmeemana - 1 month ago 9
MySQL Question

handle multiple rows in a BEFORE INSERT trigger when inserting multiple rows through single SQL query

I have USER table which has "id" and "name" columns.there is PRODUCT table which has "id" and "user_id" columns."user_id" column has foreign key reference to "id" column in USER table.when I am inserting multiple rows using single sql query which has the same user_id.like

INSERT INTO PRODUCT(id,user_id) VALUES(1,1),(2,1),(3,1),(4,1);


I want to execute before insert trigger for handle below conditions.


  • on the PRODUCT table, get the number of rows which have same foreign key references as the inserting records' foreign key.

  • if above number greater than or equal to three.allow to insert the all the rows.

  • if the above number is 0 and the number of raws which are trying to insert is greater than or equal to three then allow to insert all the raws.

  • if the above number is 0 and the number of raws which are trying to insert is less than to three then don't allow to insert all the raws and send an error message.



can anyone help me to write the BEFORE INSERT trigger to handle all of those conditions?

Answer Source

You can use left join and count on existing entries to achieve only inserting entries unless there exists a user id with one of two products.

    CREATE TABLE if not exists `product_46353892` (
      `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
      `user_id` int(11) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

    /* 
        truncate table to allow rerun of example 
        */
    truncate product_46353892 ;

    /* 
        set up condition to stop any more products being added ... user_id has one or two products 
        here we insert two products
        */
    insert into product_46353892 ( id, user_id )
    select `new`.* from (
        select 1 id, 1 user_id
        union all
        select 2 id, 1 user_id
    ) `new`
    left join (
        select user_id, count(*) count from product_46353892 product
        group by user_id
    ) old
    on new.user_id = old.user_id
    where ( old.count is null or old.count >= 3 ) ;

    insert into product_46353892 ( id, user_id )
    select `new`.* from (
        select 3 id, 1 user_id
        union all
        select 4 id, 1 user_id
        union all 
        select 5 id, 1 user_id 
        union all 
        select 6 id, 1 user_id
    ) `new`
    left join (
        select user_id, count(*) count from product_46353892 product
        group by user_id
    ) old
    on new.user_id = old.user_id
    where ( old.count is null or old.count >= 3 ) ;

    insert into product_46353892 ( id, user_id )
    select `new`.* from (
        select 7 id, 2 user_id
        union all
        select 8 id, 2 user_id
        union all 
        select 9 id, 2 user_id 
        union all 
        select 10 id, 2 user_id
    ) `new`
    left join (
        select user_id, count(*) count from product_46353892 product
        group by user_id
    ) old
    on new.user_id = old.user_id
    where ( old.count is null or old.count >= 3 ) ;

    select * from product_46353892 ;

Example results. Note that id's 3,4,5 and 6 were not inserted.

id  user_id
1   1
2   1
7   2
8   2
9   2
10  2