[데이터베이스] Ch5. SQL 고급3 - 트리거

트리거(Trigger)

• 데이터베이스에서 발생하는 특정 사건에 대한 반응
• 시스템이 자동으로 수행 하는 구문
• ex
  학생이 새로운 교과목을 이수할 때마다 전체 이수 학점을 다시 계산해야 하는 경우
• 트리거 명시 조건
  - 트리거가 실행될 시점 ( 사건 or 조건 명시 )
  - 트리거가 실행될 때 수행되어야 할 동작 명시
• 트리거는 SQL 표준이 도입되어있지만, 대부분 비표준구문을 사용.
• 특정 사건(INSERT, DELETE, UPDATE) 후에 활성화 되는 트리거
  - after update of takes on grade
  : takes 릴레이션의 grade 속성 UPDATE 이후 실행되는 트리거
• 특정 사건(INSERT, DELETE, UPDATE) 전에 활성화 되는 트리거
  - 이 경우, 잘못된 INSERT, DELETE, UPDATE를 방지할 수 있는 추가 제약 기능도 함!
• 사건 전과 후의 속성 값을 참조할 수 있음.
  - 갱신되거나 삭제된 행의 이전 값을 참조
  : referencing old row as
  - 갱신되거나 삽입된 행의 새로운 값을 참조
  : referencing new row as
  

// 학점이 없음을 나타내기 위해 튜플의 학점 값이 공백으로 갱신된 경우,
// 공백을 널 값으로 대체하는 트리거
create trigger setnull_triger before update of takes
referencing new row as nrow
for each now
when (nrow.grade = ' ')
begin atomic
   set nrow.grade = null;
end;

참조 무결성을 위한 트리거

create trigger timeslot_check1 after insert on section // timeslot 트리거 생성
referencing new row as nrow // 새로 추가된 행의 값을 참조하는 변수 nrow 정의
// nrow 는 이행변수(transition variable)라고 부른다.
for each row // 각각의 새로 추가된 행에 대하여 반복
when (nrow.time_slot_id not in (
   select time_slot_id
   from time_slot)
) // when 구문을 만족할 경우, begin과 end 사이의 부분을 실행
// 즉 insert된 새로운 행(nrow)가 time_slot 테이블에 존재하지 않을 경우, rollback 실행
begin
   rollback // rollback 을 사용함으로써 참조 무결성 제약 조건을 위반하는 트랜잭션을 무효화
end;

create trigger timeslot_check2 after delete on time_slot
// time_slot 릴레이션에 대한 delete가 수행된 후에 시작되는 트리거 생성
referencing old row as orow // 삭제된 행의 삭제 전 값을 참조하는 변수 orow 정의
for each row
when (orow.time_slot_id not in (
   select time_slot_id
   from time_slot )
   // orow(삭제된 행의 삭제 전 값을 나타내는 행)의 time_slot_id가 현재 time_slot_id에 존재하지 않을 경우.
   and
   orow.time_slot_id in (
   select time_slot_id
   from section )
   // orow 가 현재 time_slot_id에 존재할 경우.
// 즉, time_slot_id가 time_slot 테이블에 남아있지 않고(not in) 동시에 section 테이블에는 존재하는 경우에,
)
begin
   rollback // rollback 시행
end;

tot_cred(총 수강 학점) 값의 유지를 위한 트리거

create trigger credits_earned after update of takes on grade 
// takes 릴레이션의 grade 열에 대한 update가 진행 된 후 실행되는 트리거 생성
referencing new row as nrow // 업데이트 된 후 새로운 값을 참조하는 nrow 변수 생성
referencing old row as orow // 업데이트 된 후 예전 값을 참조하는 orow 변수 생성
for each row
when nrow.grade <> 'F' and nrow.grade is not null
// 업데이트 된 grade의 신규 값이 'F'가 아니고 grade가 null값이 아닌 경우
and // 이면서
(orow.grade = 'F' or orow.grade is null)
// 업데이트 된 grade의 예전 값이 'F' 이거나 예전 값이 null 값이었을 경우
begin atomic
// begin atomic ... end 는 여러 SQL 문장을 단일 복합 문장으로 통합함.
// 몸체 부분의 SQL이 모두 성공하거나 모두 실패해야함.
   update student
   set tot_cred = tot_cred +
      (select credits
      from course
      where course.course_id = nrow.course_id)
      // 업데이트 된 grade의 신규 값의 course_id 의 credits 값을 tot_cred(총 credit)에 더함.
   where student.id = nrow.id;
end;

Postgres 에서 Trigger 생성

• Postgres 에서 새로운 trigger 를 생성하기 위해서는 두 가지 단계를 거친다
  - CREATE FUNCTION 이용 트리거 함수를 정의
  - CREATE TRIGGER 이용 트리거 함수를 테이블과 연관짓기

/** employee 의 이름이 변하는 경우 employee_audits 라는 별도의 테이블에
변화에 대해 기록하고 싶다고 가정. 다음의 SQL 문들을 사용 테이블과 트리거 함수 생성
*/
DROP TABLE IF EXISTS employees;
// employees 테이블이 있을 경우 create table 에 오류 발생할 수 있기에 drop table 먼저 함.

CREATE TABLE employees (
   id INT GENERATED ALWAYS AS IDENTITY
   first_name VARCHAR(40) NOT NULL,
   last_name VARCHAR(40) NOT NULL,
   PRIMARY KEY (id)
);
// 이 부분은 해당 열이 자동으로 값이 생성되는 것을 나타냄.
// 일련번호 형태로 자동 증가하며, 새로운 레코드가 삽입될 때마다 자동으로 증가하는 일련번호를 할당.

CREATE TABLE employee_audits (
   id INT GENERATED ALWAYS AS IDENTITY
   employee_id INT NOT NULL,
   last_name VARCHAR(40) NOT NULL,
   changed_on TIMESTAMP(6) NOT NULL
);

CREATE OR REPLACE FUNCTION log_last_name_changes()
   RETURNS TRIGGER
   LANGUAGE PLPGSQL
   AS
$$
BEGIN
   IF NEW.last_name <> OLD.last_name THEN
      INSERT INTO employee_audits(employee_id, last_name, changed_on)
      VALUES( OLD.id, OLD.last_name, now() )
// 새로운 값(NEW)과 이전 값(OLD)의 last_name을 비교하여 변경되었는지 확인
// last_name이 변경된 경우, employee_audits 테이블에 변경 사항을 기록합
   );
   END IF;
   RETURN NEW;
END;
$$;

/**
다음의 SQL 문들을 사용하여 트리거가 생성되어
employees 릴레이션에 update 가 일어나기 전에 트리거 함수 log_last_name_change() 가 호출되게 함.
실제 튜플을 employees에 넣고 갱신을 하여 트리거가 제대로 동작 되었음을 확인해보자
*/
CREATE TRIGGER last_name_changes BEFORE UPDATE ON employees
FOR EACH ROW
EXECUTE PROCEDURE long_last_name_changes();
// long_last_name_changes() 는 함수이지만, 트리거에서 함수를 호출할 때는 EXCUTE PROCEDURE 을 사용함.

INSERT INTO employees (first_name, last_name)
VALUES ('John', 'Doe');

INSERT INTO employees (first_name, last_name)
VALUES('Lily', 'Bush');

SELECT * FROM employees;

UPDATE employees
SET last_name = 'Brown'
WHERE ID = 2;

SELECT * FROM employees;
SELECT * FROM employee_audits;

Postgres 에서 Trigger 삭제

• Trigger 를 삭제하려면 DROP TRIGGER 실행

DROP TRIGGER last_name_changes ON employees;