Problem

Given a table of customer data, write a trigger that will automatically update the customer's balance field whenever a new purchase is made.
Input:
Table: customer
id name balance
1 John 100
2 Jane 200
3 Joe 300
4 Jill 400
5 Jake 500
Table: purchase
id customer_id amount
1 1 10
2 2 20
3 3 30
4 4 40
5 5 50
Output:
Table: customer
id name balance
1 John 110
2 Jane 220
3 Joe 330
4 Jill 440
5 Jake 550

Solution

CREATE TRIGGER update_balance
AFTER INSERT ON purchase
FOR EACH ROW
BEGIN
  UPDATE customer
  SET balance = balance + NEW.amount
  WHERE id = NEW.customer_id;
END;

/*

The trigger is optimal because it is only executed when a new purchase is made.

*/