[Postgresql] trigger(트리거) 생성방법 및 예제로 테스트 해보기

 

트리거(TRIGGER)란?

trigger는 데이터베이스가 미리 정해놓은 조건을 만족하거나 어떠한 동작이 수행되면 자동적으로 수행되는 행동.
테이블이나 뷰가 INSERT, UPDATE, DELETE등의 DML문에 의해 자동으로 실행되도록 할 수 있다.

이제 트리거를 생성법에 대해서 알아보자!

 

0. 트리거 특수 변수 및 알아둘 기능 

- create or replace : 오브젝트를 생성하거나 수정하겠다는 의미. 처음생성할땐 create명령어가 먹고 drop 하지 않는 한 replace가 그다음부터는 실행될것이다. 따라서 대부분 오브젝트를 생성할때 생성하거나 수정한다~ 라는 키워드로 같이 쓰는편.

- function test() returns trigger as $trigger_testt$ : 함수 test()를 생성할건데 이 함수는 트리거를 반환한다. 그 트리거이름을 $$ 사이에 넣어준다.

- declare :  사용할 변수선언하는 곳

- begin ~ end : 몸통부분, 표현하고자 하는 일련의 과정을 적는 곳.

- for each row : 반복하여 각각의 row에 접근. 이걸 해야 .new / .old 키워드로 삽입/삭제/수정되는 값들에 접근할 수 있다.

- raise notice '문자열 %',변수 : 일반 출력문으로 count값과 old / new 값들이 잘 나오는지 확인하기 위해서 디버깅용 사용?, % 갯수만큼 뒤에 출력할 변수들을 덧붙여 주면된다.

 [ 특수 변수 ] 

- NEW
행 수준 트리거의 INSERT / UPDATE 작업에 대한 새 데이터베이스 행을 보유하는 변수. 
DELETE작업에 대해 null값.
접근할 수 있는 값은 트리거가 바라보고 있는 테이블에 한해서 가능

- OLD
행 수준 트리거의 UPDATE / DELETE 작업에 대한 이전 데이터베이스 행을 보유하는 변수 입니다. 
INSERT작업에 대해 null값.
접근할 수 있는 값은 트리거가 바라보고 있는 테이블에 한해서 가능.

- TG_NAME
데이터 유형 name; 실제로 발생한 트리거의 이름을 포함하는 변수.

- TG_WHEN
데이터 유형 text; 트리거의 정의에 따라 BEFORE, AFTER, INSTEAD OF의 문자열 .

- TG_LEVEL
데이터 유형 text; 트리거의 정의에 따라 ROW, STATEMENT의 문자열 .

- TG_OP
데이터 유형 text; INSERT, UPDATE, DELETE, TRUNCATE트리거가 발생한 작업을 알려주는 문자열.

- TG_RELID
데이터 유형 oid; 트리거 호출을 일으킨 테이블의 개체 ID.

- TG_RELNAME
데이터 유형 name; 트리거 호출을 유발한 테이블의 이름. 이것은 이제 더 이상 사용되지 않으며 향후 릴리스에서 사라질 수 있다. 대신 TG_TABLE_NAME사용 권고

- TG_TABLE_NAME
데이터 유형 name; 트리거 호출을 유발한 테이블의 이름.

- TG_TABLE_SCHEMA
데이터 유형 name; 트리거 호출을 유발한 테이블의 스키마 이름.

- TG_NARGS
데이터 유형 integer; CREATE TRIGGER명령문 에서 트리거 함수에 제공된 인수의 수.

- TG_ARGV[]
의 데이터 유형 배열 text;  CREATE TRIGGER문의 인수. 인덱스는 0부터 계산합니다. 잘못된 인덱스(0보다 작거나 크거나 같음 tg_nargs)는 null 값이 됩니다.

1. 테이블 생성

CREATE TABLE emp (
    empname text PRIMARY KEY,
    salary integer
);

--변경이력저장테이블
CREATE TABLE emp_audit (
    operation char(1) NOT NULL,
    last_date timestamp NOT NULL,
    userid text NOT NULL,
    empname text,
    salary integer,
    newEmpname text
);

 

2. 트리거 함수 생성

CREATE FUNCTION emp_trigger() RETURNS trigger AS $emp_trigger_test$
    BEGIN
        IF (TG_OP = 'DELETE') THEN
        	INSERT INTO emp_audit VALUES('D',now(), user,OLD.*);
        ELSIF (TG_OP = 'UPDATE') THEN
           INSERT INTO emp_audit VALUES('U' now(), user, OLD.empname, new.salary, new.empname);
        ELSIF (TG_OP = 'INSERT') THEN
        	INSERT INTO emp_audit VALUES('I' now(), user, NEW.*);  
     -- emp테이블 등록시 emp_audit테이블에 변경이력 저장후 emp테이블에 등록한 내용 삭제하는 것
     -- ELSIF (TG_OP = 'INSERT') THEN
       -- 	INSERT INTO emp_audit VALUES('I' now(), user, NEW.*);
       --   DELETE FROM emp where empname = new.empname;
        END IF;
        RETURN NULL;
    END;
$emp_trigger_test$ LANGUAGE plpgsql;

 

3. 트리거 생성

CREATE TRIGGER emp_trigger_test
 AFTER INSERT OR UPDATE OR DELETE ON emp_audit
   FOR EACH ROW EXEUTE FUNCTION emp_trigger();

 

4. 확인

INSERT INTO emp values ('hong', 10);

--테이블 내용삭제
TRUNCATE TABLE emp;
TRUNCATE TABLE emp_audit;

---

[ 참고 사이트 ]

https://www.postgresql.org/docs/current/plpgsql-trigger.html

43.10. Trigger Functions

https://sas-study.tistory.com/182

[Postgresql] 트리거 생성 및 확인하기