SQLite 트리거: CREATE TRIGGER와 OLD/NEW 사용법

자동으로 SQL을 실행해 주는 트리거

SQLite 트리거는 특정 테이블에서 정해진 이벤트가 일어날 때마다 자동으로 실행되는 SQL 블록입니다. 한 번만 정의해 두면, 언제 실행할지는 SQLite가 알아서 판단해 줍니다.

기본 형태는 다음과 같습니다.

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    price REAL NOT NULL
);

CREATE TABLE price_history (
    product_id INTEGER,
    old_price REAL,
    new_price REAL,
    changed_at TEXT DEFAULT CURRENT_TIMESTAMP
);

CREATE TRIGGER log_price_change
AFTER UPDATE OF price ON products
BEGIN
    INSERT INTO price_history(product_id, old_price, new_price)
    VALUES (OLD.id, OLD.price, NEW.price);
END;

INSERT INTO products(name, price) VALUES ('공책', 5.00);
UPDATE products SET price = 6.50 WHERE name = '공책';
SELECT * FROM price_history;

우리는 price_history에 INSERT를 직접 써준 적이 없습니다. 트리거가 알아서 처리한 거죠. 앞으로 일어나는 모든 가격 변경은 CLI에서 오든, 스크립트에서 오든, 앱에서 오든 똑같은 방식으로 기록됩니다.

CREATE TRIGGER 구문 뜯어보기

문법을 한 조각씩 살펴봅시다:

CREATE TRIGGER trigger_name
{ BEFORE | AFTER | INSTEAD OF } { INSERT | UPDATE [ OF column_list ] | DELETE }
ON table_name
[ FOR EACH ROW ]
[ WHEN condition ]
BEGIN
    -- 하나 이상의 문장
END;

• 타이밍(Timing) — BEFORE는 변경 전에, AFTER는 변경 후에 실행되고, INSTEAD OF는 변경 자체를 대체합니다(뷰에서만 사용 가능).
• 이벤트(Event) — 어떤 작업이 트리거를 발동시키는지 지정합니다. UPDATE OF col1, col2로 작성하면 특정 컬럼이 갱신될 때만 실행되도록 좁힐 수 있습니다.
• 테이블(Table) — 감시 대상이 되는 테이블입니다.
• FOR EACH ROW — SQLite는 행 단위(row-level) 트리거만 지원하기 때문에 이 구문은 사실상 기본값입니다. 명시적으로 적어도 되지만 동작은 달라지지 않습니다.
• WHEN — 선택적인 조건절입니다. 이 조건이 참일 때만 트리거 본문이 실행됩니다.
• 본문(Body) — BEGIN과 END 사이에 들어가는 하나 이상의 문장입니다. 각 문장은 세미콜론으로 끝나야 합니다.

문법은 이게 전부입니다. 실무에서 쓰는 트리거는 대부분 5~10줄 정도면 충분합니다.

OLD와 NEW: 변경 중인 행 참조하기

트리거 본문 안에서는 두 개의 가상 행(pseudo-row)을 통해 데이터에 접근할 수 있습니다.

• NEW — 들어오는 새 행입니다. INSERT와 UPDATE 트리거에서 사용할 수 있습니다.
• OLD — 기존 행입니다. UPDATE와 DELETE 트리거에서 사용할 수 있습니다.

DELETE 트리거에는 OLD만, INSERT 트리거에는 NEW만 존재합니다. UPDATE 트리거에서는 둘 다 쓸 수 있습니다.

CREATE TABLE accounts (
    id INTEGER PRIMARY KEY,
    name TEXT,
    balance REAL NOT NULL
);

CREATE TABLE deletions (
    table_name TEXT,
    row_id INTEGER,
    snapshot TEXT,
    deleted_at TEXT DEFAULT CURRENT_TIMESTAMP
);

CREATE TRIGGER archive_account
AFTER DELETE ON accounts
BEGIN
    INSERT INTO deletions(table_name, row_id, snapshot)
    VALUES ('accounts', OLD.id, OLD.name || ' / ' || OLD.balance);
END;

INSERT INTO accounts(name, balance) VALUES ('Rosa', 120.00);
DELETE FROM accounts WHERE name = 'Rosa';
SELECT * FROM deletions;

삭제된 행은 accounts 테이블에서 사라졌지만, 사라지기 전에 deletions 테이블에 그 데이터가 그대로 보관되어 있습니다.

BEFORE 트리거: 행을 검증하거나 보정하기

BEFORE 트리거는 행 변경이 디스크에 반영되기 전에 실행됩니다. 그래서 에러를 발생시키거나 데이터를 정규화하는 용도로 아주 유용합니다:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    email TEXT NOT NULL,
    created_at TEXT
);

CREATE TRIGGER lowercase_email
BEFORE INSERT ON users
BEGIN
    UPDATE users SET email = email; -- 아무 동작 없음, 단지 문맥 설명용
END;

CREATE TRIGGER reject_empty_email
BEFORE INSERT ON users
WHEN NEW.email IS NULL OR trim(NEW.email) = ''
BEGIN
    SELECT RAISE(ABORT, '이메일은 비어 있을 수 없습니다');
END;

INSERT INTO users(email) VALUES ('ada@example.com');
INSERT INTO users(email) VALUES ('');
SELECT * FROM users;

두 번째 INSERT는 행이 한 줄도 기록되기 전에 중단됩니다. RAISE(ABORT, '...')는 현재 문장을 취소하고 그 시작 시점으로 롤백하는데, 이외에도 RAISE(FAIL, ...), RAISE(ROLLBACK, ...), RAISE(IGNORE)를 활용하면 동작 방식을 더 세밀하게 조절할 수 있습니다.

순수한 데이터 검증이 목적이라면 CHECK 제약 조건을 우선 고려하세요. 선언적이라 옵티마이저도 이를 인식합니다. 다른 테이블을 조회해야 한다거나 CHECK로는 표현할 수 없는 규칙이 필요할 때 비로소 BEFORE 트리거를 꺼내 들면 됩니다.

WHEN 조건절: 조건부 트리거 만들기

WHEN 조건절을 사용하면 어떤 행 변경에 대해서만 트리거 본문을 실행할지 걸러낼 수 있습니다. 이 조건은 OLD와 NEW가 바인딩된 뒤에 행 단위로 평가됩니다:

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    total REAL NOT NULL,
    status TEXT NOT NULL
);

CREATE TABLE big_orders (
    order_id INTEGER,
    total REAL,
    flagged_at TEXT DEFAULT CURRENT_TIMESTAMP
);

CREATE TRIGGER flag_big_order
AFTER INSERT ON orders
WHEN NEW.total >= 1000
BEGIN
    INSERT INTO big_orders(order_id, total) VALUES (NEW.id, NEW.total);
END;

INSERT INTO orders(total, status) VALUES (50, 'paid');
INSERT INTO orders(total, status) VALUES (1500, 'paid');
INSERT INTO orders(total, status) VALUES (2200, 'paid');
SELECT * FROM big_orders;

첫 번째 주문은 조건에 걸리지 않아서 제외되고, 나머지 두 건만 기록됩니다. 만약 WHEN 조건절이 없었다면 모든 INSERT가 big_orders에 그대로 쌓였을 테고, 결국 조회 시점에 필터링하는 식으로 처리해야 했겠죠.

INSTEAD OF 트리거로 뷰에 쓰기 허용하기

뷰(view)는 기본적으로 읽기 전용입니다. 하지만 INSTEAD OF 트리거를 걸어 두면 뷰에 대한 쓰기 작업을 가로채서 대신 원하는 SQL을 실행할 수 있는데요, 보통은 이걸 활용해 뷰 뒤에 있는 실제 테이블에 대한 쓰기로 변환합니다.

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    first_name TEXT,
    last_name TEXT,
    salary REAL
);

CREATE VIEW employee_summary AS
SELECT id, first_name || ' ' || last_name AS full_name, salary
FROM employees;

CREATE TRIGGER employee_summary_insert
INSTEAD OF INSERT ON employee_summary
BEGIN
    INSERT INTO employees(first_name, last_name, salary)
    VALUES (
        substr(NEW.full_name, 1, instr(NEW.full_name, ' ') - 1),
        substr(NEW.full_name, instr(NEW.full_name, ' ') + 1),
        NEW.salary
    );
END;

INSERT INTO employee_summary(full_name, salary) VALUES ('Boris Petrov', 60000);
SELECT * FROM employees;

애플리케이션 입장에서는 뷰를 그냥 테이블처럼 다루면 됩니다. first_name과 last_name으로 쪼개는 작업은 트리거가 뒤에서 알아서 처리해 주죠.

SQLite 트리거 목록 조회와 삭제하기

트리거는 테이블, 인덱스와 함께 sqlite_master에 저장되어 있습니다:

CREATE TABLE notes (id INTEGER PRIMARY KEY, body TEXT);

CREATE TRIGGER touch_note
AFTER UPDATE ON notes
BEGIN
    SELECT 1; -- 플레이스홀더 본문
END;

SELECT name, tbl_name, sql
FROM sqlite_master
WHERE type = 'trigger';

DROP TRIGGER IF EXISTS touch_note;

SELECT count(*) AS remaining
FROM sqlite_master
WHERE type = 'trigger';

DROP TRIGGER IF EXISTS name; 이 형태가 안전합니다. 그리고 트리거가 붙어 있는 테이블을 삭제하면 트리거도 함께 사라지니, 굳이 미리 정리할 필요는 없습니다.

알아두면 좋은 함정들

처음 트리거를 다룰 때 흔히 걸려 넘어지는 지점들이 있습니다.

• 트리거는 문장 단위가 아니라 행 단위로 실행됩니다. 1,000개의 행을 건드리는 UPDATE는 트리거를 1,000번 호출합니다. 트리거 본문 자체가 무거운 작업이라면 비용이 빠르게 쌓입니다.
• 트리거는 바깥 트랜잭션 안에서 실행됩니다. 바깥 문장이 롤백되면 트리거가 쓴 내용도 함께 롤백됩니다. 보통은 이게 원하는 동작이지만, 반대로 말하면 "무슨 일이 있어도 이건 꼭 기록하고 싶다"는 용도로는 트리거가 적합하지 않다는 뜻이기도 합니다.
• 재귀 트리거는 기본적으로 꺼져 있습니다. 같은 테이블을 수정하는 트리거라도 자기 자신을 다시 호출하지 않습니다. 활성화하려면 PRAGMA recursive_triggers = ON;을 설정해야 하는데, 명확한 이유가 없다면 그냥 끈 채로 두는 편이 낫습니다.
• 애플리케이션이 트리거를 우회할 수 있는 유일한 방법은 데이터베이스 자체를 거치지 않는 것뿐입니다. 모든 쓰기가 SQLite를 통한다면 트리거는 반드시 실행됩니다. ORM이 원시 SQL로 일괄 처리를 하더라도 마찬가지로 트리거가 동작합니다.
• 비즈니스 로직을 여러 트리거에 흩뿌리지 마세요. 트리거는 호출하는 코드에서는 보이지 않습니다. "이 행이 왜 생겼지?"를 디버깅하는 사람은 결국 sqlite_master를 뒤져야 합니다. 감사 로그, 파생 컬럼, 뷰 쓰기 처리처럼 횡단 관심사에만 사용하고, 나머지는 애플리케이션 코드에 두세요.

현실적인 감사 로그(audit log) 예제

지금까지 본 패턴들을 한데 모아, posts 테이블의 모든 변경 사항을 추적해 보겠습니다.

CREATE TABLE posts (
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    body TEXT,
    updated_at TEXT
);

CREATE TABLE post_audit (
    post_id INTEGER,
    action TEXT,
    old_title TEXT,
    new_title TEXT,
    at TEXT DEFAULT CURRENT_TIMESTAMP
);

CREATE TRIGGER posts_audit_update
AFTER UPDATE OF title, body ON posts
BEGIN
    UPDATE posts SET updated_at = CURRENT_TIMESTAMP WHERE id = NEW.id;
    INSERT INTO post_audit(post_id, action, old_title, new_title)
    VALUES (NEW.id, 'update', OLD.title, NEW.title);
END;

INSERT INTO posts(title, body) VALUES ('초안', '첫 번째 작성본');
UPDATE posts SET title = '게시됨' WHERE id = 1;
SELECT id, title, updated_at FROM posts;
SELECT * FROM post_audit;

트리거 하나로 updated_at을 정확하게 유지하면서 감사(audit) 기록까지 한곳에서 남길 수 있습니다. 정작 UPDATE를 실행하는 애플리케이션 코드는 이런 동작이 뒤에서 일어나고 있다는 사실조차 알 필요가 없죠.

다음 주제: JSON 지원

트리거가 행(row) 단위 이벤트를 자동화하는 도구라면, 이번엔 행 안에 무엇을 담을 수 있는지로 넘어가 봅시다. 바로 JSON입니다. SQLite는 SQL을 벗어나지 않고도 구조화된 데이터를 조회하고 수정할 수 있는 풍부한 JSON 함수를 제공하는데, 이어지는 페이지에서 이 내용을 다룹니다.