Sqlite의 트리거(trigger) 정의

Posted on: 2019년 09월 25일 2019년 09월 25일
Categories: 강의자료, 데이터베이스

참조
1. https://nmshome.tistory.com
2. https://thinking-jmini.tistory.com/21
트리거의 정의
1. 데이터베이스가 미리 정해 놓은 조건이 충족되거나, 특정 테이블에 삽입/수정/삭제 등의 데이터 변경이 발생하면 자동 실행되도록 정의한 동작
2. 트리거 이름은 해당 데이터베이스에서 유일해야 함
트리거 생성
1. CREATE TRIGGER [트리거 명]
2. [동작시점] [SQL 연산] ON [테이블 명]
3. BEGIN
4. [수행할 SQL문]
5. END
동작 시점
1. BEFORE : 대상 테이블의 INSERT, DELETE, UPDATE 동작 직전에 수행
2. AFTER : 대상 테이블의 INSERT, DELETE, UPDATE 동작 이후에 수행
3. INSTEAD OF : 대상 테이블의 INSERT, DELETE, UPDATE 동작 대신에 수행
트리거 삭제
1. DROP TRIGGER [트리거 명]

트리거 예

/* 테스트 테이블 생성 */
CREATE TABLE address (uid INTEGER PRIMARY KEY, name TEXT, gender INTEGER);
CREATE TABLE metadata (uid INTEGER PRIMARY KEY, man_count INTEGER, woman_count INTEGER, change_name_count INTEGER);

/* address 테이블에 DELETE 동작이 발생하였을 때, metadata 테이블을 갱신하는 트리거 생성 */
CREATE TRIGGER delete_address
DELETE ON address /* BEFORE DELETE ON address */  /* AFTER DELETE ON address */
BEGIN
	UPDATE metadata SET man_count = man_count - 1 WHERE old.gender = 1; /* 남자 카운트 증가 */
	UPDATE metadata SET woman_count = woman_count - 1 WHERE old.gender = 0; /* 여자 카운트 증가 */
END;

/* address 테이블에 INSERT 동작이 발생하였을 때, metadata 테이블을 갱신하는 트리거 생성 */
CREATE TRIGGER insert_address
INSERT ON address
BEGIN
	UPDATE metadata SET man_count = man_count + 1 WHERE new.gender = 1; /* 남자 카운트 증가 */
	UPDATE metadata SET woman_count = woman_count + 1 WHERE new.gender = 0; /* 여자 카운트 증가 */
END;

/* address 테이블의 name 필드에 UPDATE 동작이 발생하였을 때, metadata 테이블을 갱신하는 트리거 생성 */
CREATE TRIGGER update_address
UPDATE OF name ON address    /* BEFORE UPDATE OF name ON address */
BEGIN
	UPDATE metadata SET change_name_count = change_name_count + 1; /* 이름변경 카운트 증가 */
END;

/* metadata 테이블에 기본 행(Row) 추가 */
INSERT INTO metadata VALUES (1, 0, 0, 0); /* address 테이블에 남자(gender=1) 셋, 여자(gender=0) 셋 추가 */
INSERT INTO address(name, gender) VALUES ('James', 1); /* insert_address 트리거 (남자 카운트 증가) */
INSERT INTO address(name, gender) VALUES ('Richard', 1); /* insert_address 트리거 (남자 카운트 증가) */
INSERT INTO address(name, gender) VALUES ('Daniel', 1); /* insert_address 트리거 (남자 카운트 증가) */
INSERT INTO address(name, gender) VALUES ('Jane', 0); /* insert_address 트리거 (여자 카운트 증가) */
INSERT INTO address(name, gender) VALUES ('Suzan', 0); /* insert_address 트리거 (여자 카운트 증가) */
INSERT INTO address(name, gender) VALUES ('Alice', 0); /* insert_address 트리거 (여자 카운트 증가) */
DELETE FROM address WHERE name='Daniel';
UPDATE address SET name='Jane Junior' WHERE name='Jane';

/* 테스트 테이블 생성 */

CREATE TABLE address (uid INTEGER PRIMARY KEY, name TEXT, gender INTEGER);

CREATE TABLE metadata (uid INTEGER PRIMARY KEY, man_count INTEGER, woman_count INTEGER, change_name_count INTEGER);

/* address 테이블에 DELETE 동작이 발생하였을 때, metadata 테이블을 갱신하는 트리거 생성 */

CREATE TRIGGER delete_address

DELETE ON address /* BEFORE DELETE ON address */ /* AFTER DELETE ON address */

BEGIN

UPDATE metadata SET man_count = man_count - 1 WHERE old.gender = 1; /* 남자 카운트 증가 */

UPDATE metadata SET woman_count = woman_count - 1 WHERE old.gender = 0; /* 여자 카운트 증가 */

END;

/* address 테이블에 INSERT 동작이 발생하였을 때, metadata 테이블을 갱신하는 트리거 생성 */

CREATE TRIGGER insert_address

INSERT ON address

BEGIN

UPDATE metadata SET man_count = man_count + 1 WHERE new.gender = 1; /* 남자 카운트 증가 */

UPDATE metadata SET woman_count = woman_count + 1 WHERE new.gender = 0; /* 여자 카운트 증가 */

END;

/* address 테이블의 name 필드에 UPDATE 동작이 발생하였을 때, metadata 테이블을 갱신하는 트리거 생성 */

CREATE TRIGGER update_address

UPDATE OF name ON address /* BEFORE UPDATE OF name ON address */

BEGIN

UPDATE metadata SET change_name_count = change_name_count + 1; /* 이름변경 카운트 증가 */

END;

/* metadata 테이블에 기본 행(Row) 추가 */

INSERT INTO metadata VALUES (1, 0, 0, 0); /* address 테이블에 남자(gender=1) 셋, 여자(gender=0) 셋 추가 */

INSERT INTO address(name, gender) VALUES ('James', 1); /* insert_address 트리거 (남자 카운트 증가) */

INSERT INTO address(name, gender) VALUES ('Richard', 1); /* insert_address 트리거 (남자 카운트 증가) */

INSERT INTO address(name, gender) VALUES ('Daniel', 1); /* insert_address 트리거 (남자 카운트 증가) */

INSERT INTO address(name, gender) VALUES ('Jane', 0); /* insert_address 트리거 (여자 카운트 증가) */

INSERT INTO address(name, gender) VALUES ('Suzan', 0); /* insert_address 트리거 (여자 카운트 증가) */

INSERT INTO address(name, gender) VALUES ('Alice', 0); /* insert_address 트리거 (여자 카운트 증가) */

DELETE FROM address WHERE name='Daniel';

UPDATE address SET name='Jane Junior' WHERE name='Jane';

whan0623

517