Create trigger using variable from table

Question

I'm relatively new to triggers in MYSQL, so sorry if this is a pretty basic thing I'm trying to do. I've found how to set one up allowing a static update, however I haven't seen anything about how to use one of the fields from the inital update as a variable inside the trigger statement

Example:

Table 1, items:

id | name | total_stock
1 | item | 8
2 | item2 | 0

Table 2, item_options:

id | item_id | option | stock
1 | 1 | test | 5
2 | 1 | test2 | 3
3 | 2 | test | 0

If I then update item_options:

UPDATE `item_options` SET `stock`=7 WHERE `id`=1

Or insert a new item into item_options:

INSERT INTO `item_options` (`item_id`,`option`,`stock`) VALUES ('2','add','2')

Then I'd like (if it's possible) to use a trigger to update the total_stock in the items table with the SUM of stock in the item_options table with the same corresponding item_id.

So, I guess my question is in two parts:

Is this possible?
Can someone point me in the right direction of how to do this?

Klaus Byskov Pedersen · Accepted Answer · 2012-07-04 09:49:17Z

2

You can use the pseudo rows new or old as described here.

It should be something along these lines:

CREATE
TRIGGER my_trigger after insert, update
ON item_options FOR EACH ROW 
BEGIN
    update items set total_stock = (select sum(stock) from item_options where item_id = new.item_id) where item_id = new.item_id;
END;

Please note that I haven't tested it, but it should give you the general idea.

answered Jul 4, 2012 at 9:49

Klaus Byskov Pedersen

122k31 gold badges192 silver badges223 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Create trigger using variable from table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related