I have two tables and I have to create a trigger. When I insert a new tuple in t
ID: 3548196 • Letter: I
Question
I have two tables and I have to create a trigger.
When I insert a new tuple in the sales table, its quantity should be decreased by 1 in the stock table.
For example, if I insert VALUES (1, 1, 6.98, 12/09/13) in the sales talbe, the first row in the stock table's quantity should be 99.
Please help!
CREATE TABLE stocks ( st_id int references books(b_id), st_price money NOT NULL, st_quantity int NOT NULL ); CREATE TABLE sales ( order_id serial PRIMARY KEY, s_b_id int NOT NULL references books(b_id), s_a_id int NOT NULL references accounts(a_id), s_price money NOT NULL, s date date NOT NULL ); I have two tables and I have to create a trigger. When I insert a new tuple in the sales table, its quantity should be decreased by 1 in the stock table. For example, if I insert VALUES (1, 1, 6.98, 12/09/13) in the sales talbe, the first row in the stock table's quantity should be 99.Explanation / Answer
CREATE OR REPLACE TRIGGER sales_check
AFTER INSERT ON sales
BEGIN
UPDATE stocks
SET st_quantity = st_quantity-1
WHERE st_price = (select st_price FROM sales WHERE order_id =
(select max(order_id) from sales))
END;