저장 프로시저 / 트리거

CREATE PROCEDURE transfer(src int, dst int, amt int)
LANGUAGE plpgsql
AS $$
BEGIN
    IF (SELECT balance FROM account WHERE id = src) < amt THEN
        RAISE EXCEPTION '잔액 부족';
    END IF;
    UPDATE account SET balance = balance - amt WHERE id = src;
    UPDATE account SET balance = balance + amt WHERE id = dst;
END;
$$;

CALL transfer(1, 2, 100);                          -- 앱은 한 번만 호출

CREATE FUNCTION log_balance() RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO audit_log(account_id, old_balance, new_balance)
    VALUES (OLD.id, OLD.balance, NEW.balance);   -- OLD = 변경 전 행, NEW = 변경 후 행
    RETURN NEW;
END;
$$;

CREATE TRIGGER trg_balance_audit
AFTER UPDATE OF balance ON account
FOR EACH ROW EXECUTE FUNCTION log_balance();

class Db:
    def __init__(self):
        self.bal = {1: 1000, 2: 500}
        self.audit = []
        self.trips = 0
        self.triggers = []

    def _update(self, acc, delta):
        old = self.bal[acc]
        self.bal[acc] += delta
        for t in self.triggers:                   # 이벤트가 나면 트리거가 자동 실행
            t(acc, old, self.bal[acc])

    def run(self, sql, *args):                    # 앱이 보내는 문장 하나 = 왕복 1번
        self.trips += 1
        if sql == 'select':
            return self.bal[args[0]]
        self._update(*args)

    def call_transfer(self, src, dst, amt):       # 프로시저 호출 = 왕복 1번
        self.trips += 1
        if self.bal[src] < amt:
            raise ValueError('잔액 부족')
        self._update(src, -amt)                   # 나머지는 DB 안에서 처리
        self._update(dst, amt)

db = Db()
db.triggers.append(lambda acc, old, new: db.audit.append((acc, old, new)))

if db.run('select', 1) >= 100:                    # 프로시저 없이: 문장마다 왕복
    db.run('update', 1, -100)
    db.run('update', 2, 100)
print('앱이 하나씩:', db.trips, db.bal)

db.trips = 0
db.call_transfer(1, 2, 100)                       # 프로시저: 한 번 호출
print('프로시저:', db.trips, db.bal)
print('트리거가 남긴 이력:', len(db.audit), db.audit[0])
# 앱이 하나씩: 3 {1: 900, 2: 600}
# 프로시저: 1 {1: 800, 2: 700}
# 트리거가 남긴 이력: 4 (1, 1000, 900)

// 프로시저 없이: 문장마다 서버와 주고받는다
try (Connection conn = ds.getConnection()) {
    conn.setAutoCommit(false);
    int bal;
    try (PreparedStatement ps = conn.prepareStatement("SELECT balance FROM account WHERE id = ?")) {
        ps.setInt(1, 1);
        try (ResultSet rs = ps.executeQuery()) { rs.next(); bal = rs.getInt(1); }
    }
    if (bal >= 100) { /* UPDATE 두 번 */ }
    conn.commit();
}

// 프로시저: 한 번 호출 (PostgreSQL 11 이상의 CALL 문)
try (Connection conn = ds.getConnection();
     PreparedStatement ps = conn.prepareStatement("CALL transfer(?, ?, ?)")) {
    ps.setInt(1, 1);
    ps.setInt(2, 2);
    ps.setInt(3, 100);
    ps.execute();                                   // 잔액 부족이면 SQLException
}