CODEWITHSUNDEEP
mysqleventstriggers
zardilior
zardilior

Reputation: 2978

Best solution workaround for needing a mysql event on insert?

I'm inserting a programmed delivery into sql being this a table made up of: Best solution for:


-programmed_id int
-interval int //in days or in whats easier to work with

-quantity float

-product int a foregin key

I want to create three triggers that run on insert so that they create an event that runs every interval of time (specified in the new row) that updates products setting products.quantity=products.quantity - programmed_delivery.quantity where products.id=product

I need the second trigger to delete the event and recreate it on update obviously with the new variables

On delete I got only one problem how can I know which event to delete depending on which row is deleted?

The thing is I just realized you cant handle events inside triggers. Anybody knows a workaround? Maybe a php solution? Suggestions?

Thanks a lot for your help.

After a while....

Would this work?? as a test to do what I Want to do

discounting the deliveries daily

Create database prueba; use prueba; Create table t1(id_producto int(11) not null auto_increment,quantity int(11) not null,primary key(id_producto)); Create table t2(id_delivery int(11) not null auto_increment,id_producto int(11) not null,quantity int(11) not null,fecha date not null default '2014-12-11',primary key(id_delivery)); insert into t1(quantity) values(5),(6),(8); insert into t2(id_producto,quantity,fecha) values(1,1,curdate()),(2,2,curdate()),(1,2,curdate()); Create Event If not exists deliveries on schedule every 1 day do Update t1 inner join (select id_producto as id,quantity from t2 where fecha=curdate()) as t22 on t22.id=t1.id_producto set t1.quantity=t1.quantity-t22.quantity where t1.id_producto>0;

Upvotes: 1

Views: 186

Answers (1)

RandomSeed
RandomSeed

Reputation: 29769

To address the problem of updating or deleting related entries from the event table, just create a foregin key to the programmed_delivery table.

Indeed it is not possible to manage events from a trigger, but a single "master" event could run on a regular and frequent basis (even every minute), which checks the event for something to do.

The triggers on the programmed_delivery table would update this custom event table.

But it is possible that you do not need such an event table. The "master event" could just check the programmed_delivery table to determine whether the stock should be updated at this time.

Upvotes: 0

Related Questions