0

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 ?

0

Instead of saying marketplace_saleperpartner., say NEW. (or OLD., if appropriate).

Not the answer you're looking for? Browse other questions tagged or ask your own question.