본 포스트는 박재호님의 유튜브 강의(링크)를 보고 일부 발췌하여 정리한 내용입니다.
사용된 자료, 샘플 데이터 등은 모두 SQLite Tutorial에서 확인할 수 있습니다.
1. 트리거
트리거는 테이블에 있는 데이터가 변경이 되었을 때 작업을 수행하도록 하는 매커니즘이다.
트리거를 사용하는 경우는 감사(audit)를 위해 변동사항을 기록하기 위한 로그 작성이나 비즈니스 규칙을 데이터베이스 레벨에서 강제해야 하는 경우이다.
다만 트리거는 양날의 검같아서 쓸 때는 편하지만 유지보수 차원에서는 심각한 문제를 야기할 수 있으므로 남용해서는 안 될 것이다.
트리거를 생성하기 위한 sql문의 형태는 아래와 같다.
CREATE TRIGGER [IF NOT EXISTS] trigger_name
[BEFORE|AFTER|INSTEAD OF] [INSERT|UPDATE|DELETE]
ON table_name
[WHEN condition]
BEGIN
statements;
END;INSERT, UPDATE, DELETE 전 또는 후에 무언가를 실행하겠다는 것은 금방 알 수 있을 것이다.
그런데 INSTEAD OF는 무엇일까?
지난 포스트에서 살펴봤던 뷰를 기억하고 있다면 특징이 무엇인지 떠올려 보자.
바로 읽기 전용이다. 따라서 추가, 삭제, 변경이 불가능하다.
그래서 트리거를 뷰에 걸어 다른 작업을 수행하도록 하기 위해 INSTEAD OF를 사용할 수 있다.
한편, 테이블을 삭제하게 되면 연관된 트리거가 모두 삭제되는데, 만약 트리거가 다른 테이블도 참조하고 있다면 삭제되지 않는다는 특징이 있다.
또한 INSERT, UPDATE, DELETE를 수행할 때는 해당 데이터에도 접근해야 하는데, 이 때 OLD.column_name이나 NEW.column_name같은 식으로 접근할 수 있다.
OLD, NEW를 사용할 수 있는 이벤트는 다음과 같이 나누어진다.
그러면 실제로 트리거가 어떻게 동작하는지 실습을 통해 확인해보자.
CREATE TABLE leads (
id integer PRIMARY KEY,
first_name text NOT NULL,
last_name text NOT NULL,
phone text NOT NULL,
email text NOT NULL,
source text NOT NULL
);
CREATE TRIGGER validate_email_before_insert_leads
BEFORE INSERT ON leads
BEGIN
SELECT
CASE
WHEN NEW.email NOT LIKE '%_@__%.__%' THEN
RAISE (ABORT,'Invalid email address')
END;
END;데이터를 새로 넣을 때 이메일이 형식에 맞지 않으면 오류를 일으키는 트리거이다.
형식에 맞지 않는 이메일을 입력하니 에러가 난 것을 볼 수 있다.
다음으로는 AFTER UPDATE 트리거를 생성해 볼 것이다.
어떤 데이터를 변경했을 때 그 이력을 남겨놓는 방법으로 트리거를 사용할 수 있다.
CREATE TABLE lead_logs (
id INTEGER PRIMARY KEY,
old_id int,
new_id int,
old_phone text,
new_phone text,
old_email text,
new_email text,
user_action text,
created_at text
);
CREATE TRIGGER log_contact_after_update
AFTER UPDATE ON leads
WHEN old.phone <> new.phone
OR old.email <> new.email
BEGIN
INSERT INTO lead_logs (
old_id,
new_id,
old_phone,
new_phone,
old_email,
new_email,
user_action,
created_at
)
VALUES
(
old.id,
new.id,
old.phone,
new.phone,
old.email,
new.email,
'UPDATE',
DATETIME('NOW')
) ;
END;lead_logs 테이블을 만들고, 이메일 또는 전화번호가 변경되었을 때 이 테이블에 변경된 내역을 저장하는 트리거이다.
트리거를 실행하기 전에 올바른 데이터를 복사해서 넣어주어야 한다..
INSERT INTO leads (first_name, last_name, email, phone)
VALUES ('John', 'Doe', 'john.doe@sqlitetutorial.net', '4089009334');
UPDATE leads
SET
phone = '4089998888',
email = 'john.smith@sqlitetutorial.net'
WHERE
id = 1;
전화번호와 이메일의 변경 내역이 기록된 것을 볼 수 있다.
2. INSTEAD OF 트리거
INSTEAD OF가 언제 쓰이는지는 위에서 이미 설명했다.
이번에는 실제로 이를 사용해 트리거를 생성해 보자.
아래의 테이블을 이용할 것이다.
트리거를 생성하기 전에 뷰를 먼저 만들어야 한다.
CREATE VIEW AlbumArtists(
AlbumTitle,
ArtistName
) AS
SELECT
Title,
Name
FROM
Albums
INNER JOIN Artists USING (ArtistId);뷰를 생성했으면 이제 여기에 데이터를 넣어보자.
INSERT INTO AlbumArtists(AlbumTitle,ArtistName)
VALUES('Who Do You Trust?','Papa Roach');사실 뷰는 읽기 전용이기 때문에 다음과 같이 에러가 난다.
이럴 때 INSTEAD OF 트리거를 사용해, 뷰에 데이터가 추가되려고 하면 원 테이블에 추가하도록 할 수 있다.
CREATE TRIGGER insert_artist_album_trg
INSTEAD OF INSERT ON AlbumArtists
BEGIN
-- insert the new artist first
INSERT INTO Artists(Name)
VALUES(NEW.ArtistName);
-- use the artist id to insert a new album
INSERT INTO Albums(Title, ArtistId)
VALUES(NEW.AlbumTitle, last_insert_rowid());
END;여기서 한 가지 눈여겨봐야 할 점은 albums테이블에 데이터를 넣는 과정이다.
albums 테이블은 artistid를 외래키로 가지고 있기 때문에 artists 테이블에 데이터를 넣고, 거기서 artistid를 last_insert_rowid로 가져와서 넣는다.
이는 테이블에 따로 Primary Key를 설정하지 않고, artistid를 integer로 설정해서 rowid의 alias처럼 동작하도록 만들었기 때문에 가능한 것이다.
방금 넣으려고 시도했던 데이터를 다시 넣으면 문제 없이 저장된 것을 볼 수 있다.
