I have a table that I insert all of the transactions from two locations into:
CREATE TABLE transactions (
payment_id INT NOT NULL PRIMARY KEY,
payment_date TIMESTAMP NOT NULL,
store_id INT NOT NULL,
amount FLOAT NOT NULL
);
INSERT INTO transactions
SELECT payment.payment_id,
payment.payment_date,
staff.store_id,
payment.amount
FROM staff
JOIN payment ON staff.staff_id = payment.staff_id
ORDER BY payment.payment_id ASC;
And I create a table that keeps track of this table's averages:
CREATE TABLE store_averages AS
SELECT transactions.store_id, avg(transactions.amount)
FROM transactions
WHERE transactions.store_id = 1;
INSERT INTO store_averages
SELECT transactions.store_id, avg(transactions.amount)
FROM transactions
WHERE traansactions.store_id = 2
GROUP BY transactions.store_id;
How can I write a TRIGGER that updates the TWO ROW TABLE (store_averages), whenever a row is INSERTED into the first table (transactions)?
