소개
이 랩에서는 트리거를 사용하여 PostgreSQL 에서 작업을 자동화하는 방법을 배우게 됩니다. 이 랩은 PL/pgSQL을 사용하여 트리거 함수를 생성하고, employees 테이블의 급여 변경 사항을 별도의 salary_changes 테이블에 자동으로 기록하는 데 중점을 둡니다.
트리거 함수 작성, 테이블 이벤트 (특히 employees 테이블의 업데이트) 에 바인딩, 트리거 동작 테스트, 마지막으로 트리거 비활성화 및 삭제 과정을 안내해 드립니다.
테이블 및 트리거 함수 생성
이 단계에서는 employees 및 salary_changes 테이블을 생성한 다음 PL/pgSQL을 사용하여 log_salary_change 함수를 정의합니다.
먼저, postgres 사용자로 PostgreSQL 데이터베이스에 연결합니다.
sudo -u postgres psql
이제 employees 테이블을 생성합니다.
CREATE TABLE employees (
id SERIAL PRIMARY KEY,
name VARCHAR(100),
salary DECIMAL(10, 2)
);
이 명령은 id (기본 키), name 및 salary 열이 있는 테이블을 생성합니다.
다음으로, 급여 변경 내역을 저장하기 위해 salary_changes 테이블을 생성합니다.
CREATE TABLE salary_changes (
id SERIAL PRIMARY KEY,
employee_id INTEGER,
old_salary DECIMAL(10, 2),
new_salary DECIMAL(10, 2),
change_date TIMESTAMP
);
이제 PL/pgSQL을 사용하여 트리거 함수를 작성해 보겠습니다. 이 함수는 employees 테이블에 대한 업데이트 작업 전에 실행됩니다. 이 함수는 salary_changes 테이블에 이전 및 새 급여 값을 기록합니다.
CREATE OR REPLACE FUNCTION log_salary_change()
RETURNS TRIGGER AS $$
BEGIN
IF OLD.salary <> NEW.salary THEN
INSERT INTO salary_changes (employee_id, old_salary, new_salary, change_date)
VALUES (OLD.id, OLD.salary, NEW.salary, NOW());
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
이 코드는 log_salary_change라는 함수를 정의합니다. 급여가 변경되었는지 확인합니다. 변경된 경우, 직원 ID, 이전 급여, 새 급여 및 현재 타임스탬프와 함께 salary_changes 테이블에 새 행을 삽입합니다.
트리거 생성
이 단계에서는 급여 업데이트가 발생할 때 자동으로 실행되도록 log_salary_change 함수를 employees 테이블에 바인딩합니다.
다음 SQL 명령을 사용하여 트리거를 생성합니다.
CREATE TRIGGER salary_change_trigger
BEFORE UPDATE ON employees
FOR EACH ROW
EXECUTE FUNCTION log_salary_change();
이 명령은 salary_change_trigger라는 트리거를 생성합니다. 이 트리거는 employees 테이블에 대한 UPDATE 작업 전에 활성화되도록 지정합니다. FOR EACH ROW 절은 업데이트되는 각 행에 대해 트리거 함수가 실행되어야 함을 나타냅니다.
트리거 테스트
이 단계에서는 트리거의 동작을 테스트합니다. employees 테이블에 데이터를 삽입한 다음 급여를 업데이트하여 트리거가 salary_changes 테이블의 변경 사항을 올바르게 기록하는지 확인합니다.
먼저, employees 테이블에 초기 데이터를 삽입합니다.
INSERT INTO employees (name, salary) VALUES ('Alice', 50000.00);
INSERT INTO employees (name, salary) VALUES ('Bob', 60000.00);
이제 Alice 의 급여를 업데이트합니다.
UPDATE employees SET salary = 55000.00 WHERE name = 'Alice';
트리거가 제대로 작동했는지 확인하려면 salary_changes 테이블을 쿼리합니다.
SELECT * FROM salary_changes;
salary_changes 테이블에서 Alice 의 급여 변경에 대한 정보가 있는 행을 볼 수 있습니다.
다음으로, Bob 의 급여를 이미 가지고 있는 동일한 값으로 업데이트합니다.
UPDATE employees SET salary = 60000.00 WHERE name = 'Bob';
급여가 실제로 변경되지 않으므로 트리거는 salary_changes 테이블에 새 행을 삽입하지 않아야 합니다. 이를 확인해 보겠습니다.
SELECT * FROM salary_changes;
Alice 의 급여 변경과 관련된 하나의 행만 계속 표시되어야 합니다.
마지막으로, 새 직원을 삽입합니다.
INSERT INTO employees (name, salary) VALUES ('Charlie', 70000.00);
새 직원을 삽입하는 것은 UPDATE 이벤트에서만 실행되도록 설정되어 있으므로 log_salary_change 트리거를 활성화하지 않아야 합니다. salary_changes 테이블을 다시 확인하여 이를 확인해 보겠습니다.
SELECT * FROM salary_changes;
Alice 의 급여 변경과 관련된 하나의 행만 계속 표시되어야 합니다.
트리거 비활성화 및 삭제
이 단계에서는 PostgreSQL 에서 트리거를 비활성화하고 삭제하는 방법을 배웁니다.
먼저, salary_change_trigger를 비활성화합니다.
ALTER TABLE employees DISABLE TRIGGER salary_change_trigger;
이제 Alice 의 급여를 다시 업데이트합니다.
UPDATE employees SET salary = 60000.00 WHERE name = 'Alice';
salary_changes 테이블을 확인합니다.
SELECT * FROM salary_changes;
이전 업데이트에서 하나의 행만 계속 표시되어야 합니다. 이는 트리거가 비활성화되었음을 확인합니다.
트리거를 다시 활성화하려면 다음 명령을 사용할 수 있습니다.
ALTER TABLE employees ENABLE TRIGGER salary_change_trigger;
마지막으로, 트리거를 영구적으로 제거하려면 다음 명령을 사용하여 삭제합니다.
DROP TRIGGER salary_change_trigger ON employees;
요약
이 랩에서는 트리거를 사용하여 PostgreSQL 에서 작업을 자동화하는 방법을 배웠습니다. employees 테이블의 급여 변경 사항을 salary_changes 테이블에 기록하기 위해 PL/pgSQL을 사용하여 트리거 함수를 만들었습니다. 함수를 작성하고, 테이블 이벤트에 바인딩하고, 테스트하고, 비활성화/삭제했습니다.
