-- Lecture 7: SQL Constraints and Triggers
-- Example: Bank accounts and transfers

-- This started as a skeleton, the definitions were filled in during the lecture

-- Triggers

--------------------------------------------------------------------------------
-- Update balance of account when a new transfer is added 

CREATE OR REPLACE FUNCTION make_transfer() RETURNS TRIGGER AS $$
BEGIN
 UPDATE Accounts SET balance = balance - NEW.amount WHERE holder=NEW.sender;
 UPDATE Accounts SET balance = balance + NEW.amount WHERE holder=NEW.receiver;
 RETURN NULL;
END
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS  after_insert_on_transfer ON Transfers;

CREATE TRIGGER after_insert_on_transfer
  AFTER INSERT ON Transfers
  FOR EACH ROW
  EXECUTE FUNCTION make_transfer();


--------------------------------------------------------------------------------
-- Make sure there is enough money in the bank

CREATE OR REPLACE FUNCTION checkMinimumTotalBalance() RETURNS TRIGGER AS $$
DECLARE
  total INT;
BEGIN
  total := (SELECT SUM(balance) FROM Accounts);
  IF total < 100000 THEN
    RAISE EXCEPTION 'the total balance is %, which is too low', total;
  END IF;
  RETURN NULL;
END
$$ LANGUAGE plpgsql;

CREATE TRIGGER minimumBankBalance
  AFTER UPDATE OR DELETE ON Accounts
  FOR EACH STATEMENT
  EXECUTE FUNCTION checkMinimumTotalBalance();