MySQL Forums
Forum List  »  Triggers

Trigger help - need to deduct values in one table column from another table after an order table is set to a certain value
Posted by: Koji Inoue
Date: November 15, 2019 06:08AM

Hello there,
I am experimenting with MySQL workbench, in which I am filling an orders table with values from a Python script regarding list of albums showing price and quantities.

I have designed the database so far in that the orders table is having it's total column deducted by the amount column in the payments table (linked by orderid). Once the total column is 0 for that orderid, then the shipped column is set to 'y'.

I would like to implement another trigger that deducts the quantity in the albums table by the quantity in the order_bridge table, once the the column shipped is set to 'y' in the orders table.

The association being that order_bridge table is linking albums and orders due to a many-to-many relationship, linked by orderid (orders, order_bridge) then by albumid (order_bridge, albums).

Here are my tables:

create table orders
( orderid int auto_increment,
total decimal(5,2),
entrydate datetime default current_timestamp,
shipped char(1) default 'n',
customerid int,

primary key(orderid),
foreign key(customerid) references customers(customerid)
);

create table payments (
entry int auto_increment,
orderid int,
amount decimal(5,2),
entrydate datetime default current_timestamp,

primary key(entry),
foreign key(orderid) references orders(orderid)
);



create table order_bridge (
orderid int,
albumid int,
quantity int,

primary key (orderid,albumid),
foreign key (orderid) references orders (orderid),
foreign key (albumid) references albums (albumid)
);


create table albums
( albumid int,
albumname varchar(100),
artistname varchar(50),
price decimal (4,2),
quantity int,

primary key (albumid)
);

Here is the trigger I have which works fine:

delimiter $$

CREATE TRIGGER update_balance
after insert ON payments
FOR EACH ROW
BEGIN
UPDATE orders
SET total = total - new.amount
WHERE orderid = new.orderid;
UPDATE orders
SET shipped = 'y'
WHERE total = 0;
END$$

I was thinking the new trigger might need some kind of inner join in order to link the association between three tables, but I think I read somewhere that this might be an issue or not allowed by MySQL.

Some help or suggestions would be great.

Thank you.

Options: ReplyQuote


Subject
Views
Written By
Posted
Trigger help - need to deduct values in one table column from another table after an order table is set to a certain value
270
November 15, 2019 06:08AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.