Unable to insert into table after creating an After insert trigger

Question

I have two tables Table one: default_sellers_commissions have:

id
value - INT

Table two: marketplace_saleperpartner have:

id
userID
commission - INT default '0.00'
commission_id

What I need is after insert a new row in table marketplace_saleperpartner update the marketplace_saleperpartner.commission value with value from default_sellers_commissions.value where the marketplace_saleperpartner.commission_id = default_sellers_commissions.id

So I create a trigger :

CREATE TRIGGER `marketplacedefaultcommission`
AFTER INSERT ON `marketplace_saleperpartner`
FOR EACH ROW
UPDATE marketplace_saleperpartner
    SET `marketplace_saleperpartner`.`commision` = `default_sellers_commissions`.`value`
    WHERE `marketplace_saleperpartner`.`comm_id` = `default_sellers_commissions`.`id`
    AND `marketplace_saleperpartner`.`commision`='0.00';

P.S:
For some reason I need the marketplace_saleperpartner.commision to insert for the first time with value='0.00'

Now I am getting this error while trying to insert a new row into table marketplace_saleperpartner

MySql Error: Can't update table in stored function/trigger because it is already used by statement which invoked this stored function/trigger

Any help please ?

What does the insert statement look like? You cannot modify any of the table row values in a AFTER trigger. If you really need to create the row with a commission value of 0, then you'll need to do 2 inserts, as you cannot insert rows in the same table with a trigger. You can set the commission value to a value from a different table. Is that what you are trying to do? — JRD
@JRD Yes this is what I need, set the commission value to a value from a different table. — Ashraf Hefny

JRD JRD · Accepted Answer · 2015-12-02T17:18:34

You could use a BEFORE INSERT trigger that sets marketplace_saleperpartner.commision to value from marketplace_saleperpartner.

CREATE TRIGGER marketplacedefaultcommission
BEFORE INSERT ON marketplace_saleperpartner
FOR EACH ROW
BEGIN
    SET new.commission = (
        SELECT value
        FROM   default_sellers_commissions
        WHERE  id = new.commission_id
    );
END;

SQL Fiddle example

Unable to insert into table after creating an After insert trigger

1 Answers