소개
이 랩에서는 SQLite 트리거 자동화를 탐구합니다. 테이블에서 특정 이벤트에 대한 응답으로 SQL 문을 자동으로 실행하는 트리거를 생성, 검증 및 관리하는 방법을 배우게 됩니다. 이 랩은 감사 로그를 생성하고 트리거를 효과적으로 관리하는 과정을 안내합니다.
테이블 생성: employees 및 employee_audit
이 단계에서는 두 개의 테이블을 생성합니다: 직원 데이터를 저장하는 employees 테이블과 급여 업데이트를 기록하는 employee_audit 테이블입니다.
먼저, 터미널에서 SQLite 셸을 엽니다.
sqlite3 /home/labex/project/test.db
이 명령어는 SQLite 셸을 열고 test.db라는 데이터베이스 파일에 연결합니다. 파일이 존재하지 않으면 SQLite 가 생성합니다.
이제 다음과 같은 구조로 employees 테이블을 생성합니다.
CREATE TABLE employees (
id INTEGER PRIMARY KEY,
name TEXT,
salary REAL
);
이 SQL 명령어는 세 개의 열을 가진 employees라는 테이블을 생성합니다:
id: 각 직원의 기본 키 역할을 하는 정수입니다.name: 직원의 이름을 저장하는 텍스트 필드입니다.salary: 직원의 급여를 저장하는 실수입니다.
다음으로, employee_audit 테이블을 생성합니다:
CREATE TABLE employee_audit (
id INTEGER PRIMARY KEY AUTOINCREMENT,
employee_id INTEGER,
old_salary REAL,
new_salary REAL,
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);
이 테이블은 급여 변경 로그를 저장합니다. 열은 다음과 같습니다:
id: 각 감사 항목에 대한 고유 식별자이며, 자동으로 증가합니다.employee_id: 급여가 변경된 직원의 ID 입니다.old_salary: 업데이트 전의 급여입니다.new_salary: 업데이트 후의 급여입니다.updated_at: 업데이트가 발생한 시점을 나타내는 타임스탬프입니다.
employees 테이블에 샘플 데이터 삽입
이 단계에서는 employees 테이블에 몇 가지 샘플 데이터를 삽입합니다. 이 데이터는 다음 단계에서 생성할 트리거를 테스트하는 데 사용됩니다.
다음 SQL 명령을 실행하여 세 개의 직원 레코드를 삽입합니다:
INSERT INTO employees (id, name, salary) VALUES (1, 'Alice', 50000.00);
INSERT INTO employees (id, name, salary) VALUES (2, 'Bob', 60000.00);
INSERT INTO employees (id, name, salary) VALUES (3, 'Charlie', 70000.00);
이 명령어는 지정된 id, name, 및 salary 값을 사용하여 employees 테이블에 세 개의 행을 추가합니다.
데이터가 올바르게 추가되었는지 확인하려면, 이 명령을 실행하여 테이블의 모든 레코드를 봅니다:
SELECT * FROM employees;
예상 출력:
1|Alice|50000.0
2|Bob|60000.0
3|Charlie|70000.0
이 출력은 각 레코드의 id, name, 및 salary를 보여줍니다.
트리거 생성: salary_update_trigger
이제 employee_audit 테이블에 급여 업데이트를 자동으로 기록하는 트리거를 생성합니다.
다음 SQL 명령을 실행하여 트리거를 생성합니다:
CREATE TRIGGER salary_update_trigger
AFTER UPDATE OF salary ON employees
BEGIN
INSERT INTO employee_audit (employee_id, old_salary, new_salary)
VALUES (OLD.id, OLD.salary, NEW.salary);
END;
이 명령어는 salary_update_trigger라는 트리거를 생성합니다. 이 트리거는 employees 테이블의 salary 열에 대한 업데이트가 발생한 후 자동으로 실행됩니다.
AFTER UPDATE OF salary ON employees: 트리거가employees테이블의salary열에 대한 업데이트 후에 활성화되도록 지정합니다.BEGIN ... END: 트리거가 활성화될 때 실행될 SQL 문을 묶습니다.INSERT INTO employee_audit ...: 직원의 ID, 이전 급여 및 새 급여를 사용하여employee_audit테이블에 새 레코드를 삽입합니다.OLD.id: 업데이트 전 행의id를 참조합니다.OLD.salary: 업데이트 전의salary를 참조합니다.NEW.salary: 업데이트 후의salary를 참조합니다.
트리거 테스트
이 단계에서는 직원의 급여를 업데이트한 다음, employee_audit 테이블에 새 레코드가 추가되었는지 확인하여 트리거를 테스트합니다.
Bob 의 급여를 업데이트해 보겠습니다:
UPDATE employees SET salary = 65000.00 WHERE id = 2;
이 명령어는 id가 2 인 직원 (Bob) 의 salary 열을 65000.00으로 업데이트합니다.
이제 employee_audit 테이블을 쿼리하여 트리거가 작동했는지 확인합니다:
SELECT * FROM employee_audit;
예상 출력:
1|2|60000.0|65000.0|...
employee_audit 테이블에서 Bob 의 직원 ID, 이전 급여 및 새 급여가 있는 행을 볼 수 있습니다. updated_at 열에는 업데이트의 타임스탬프가 표시됩니다. 정확한 타임스탬프는 업데이트를 실행한 시간에 따라 달라집니다.
트리거 삭제
이 단계에서는 SQLite 데이터베이스에서 트리거를 삭제하거나 제거하는 방법을 배웁니다.
salary_update_trigger를 삭제하려면 다음 명령을 실행합니다:
DROP TRIGGER salary_update_trigger;
이 명령어는 데이터베이스에서 salary_update_trigger를 제거합니다.
트리거가 삭제되었는지 확인하려면 sqlite_master 테이블을 쿼리할 수 있습니다:
SELECT name FROM sqlite_master WHERE type='trigger' AND name='salary_update_trigger';
이 쿼리는 빈 결과 집합을 반환해야 하며, 이는 트리거가 더 이상 존재하지 않음을 나타냅니다.
이제 Alice 의 급여를 업데이트하여 트리거가 실제로 제거되었는지 확인해 보겠습니다:
UPDATE employees SET salary = 55000.00 WHERE id = 1;
그런 다음 employee_audit 테이블을 확인합니다:
SELECT * FROM employee_audit;
employee_audit 테이블에 새 항목이 추가되지 않은 것을 확인할 수 있으며, 이는 트리거가 성공적으로 삭제되었음을 확인합니다.
마지막으로 SQLite 셸을 종료합니다:
.exit
요약
이 랩에서는 특정 테이블의 업데이트 이벤트에 대한 응답으로 SQL 문을 자동으로 실행하는 SQLite 트리거를 만드는 방법을 배웠습니다. employees 테이블과 employee_audit 테이블을 만들었습니다. 그런 다음 급여 업데이트를 employee_audit 테이블에 기록하는 salary_update_trigger라는 트리거를 만들었습니다. 마지막으로 트리거를 삭제하는 방법을 배웠습니다.
