CREATE TRIGGER name { BEFORE | AFTER } { event [ OR ...]}
ON table FOR EACH { ROW | STATEMENT }
EXECUTE PROCEDURE func_name ( )
event : INPUT , UPDATE , DELETE 등이 올 수 있다.
func_name : 사용자 정의 함수이다. plpgsql 을 권장하고 싶다.
또한 이 함수에는 인자가 들어올 수 없다.
또한 이 함수의 RETURN TYPE 로는 OPAQUE 이어야 한다.
인수를 위해 TRIGGER 의 자체 변수를 사용하여야 한다.
(new,old,...)
TRIGGER 를 촌스럽게 표현하자면 방아쇠를 당기면 총알이 나가는 것에 비유할 수 있다.
TRIGGER 은 TUPLE(ROW or RECORD) 에 대한 INSERT, UPDATE, DELETE 를 사건(event) 로
보고 이 사건에 정의된 어떤 행동으로 반응하는 것이다.
CREATE TRIGGER trg_test
BEFORE DELETE OR UPDATE ON supplier FOR EACH ROW
EXECUTE PROCEDURE check_sup();
supplier 테이블에 DELETE, UPDATE 가 발생하면 이 동작이 행해지기 전에
check_sup() 를 실행하라. 이 TRIGGER 의 이름은 trg_test 이다.
CREATE FUNCTION func_name() RETURNS type
AS '
[DECLARE declarations]
BEGIN
statements
END;
'LANGUAGE 'plpgsql';
CREATE FUNCTION pgsql_test() RETURNS datetime
AS '
DECLARE curtime datetime;
BEGIN
curtime:= ''now'';
return curtime;
END;
' LANGUAGE 'plpgsql';
다음은 실행 예제이다. 우선 다음 예제를 적절히 복사를 해서 파일로 만든다.(예 : trg.sql) 다음 예제를 실행에 앞서 먼저 해야할 일이 있는데 그것은 Procedural Languages 를 등록하는 것이다. 다음예제에는 PL/pgSQL 을 이용한 함수를 사용하므로 이것을 등록하는 것은 필수이다.
방법은 두가지가 있다.
1. template1 데이타 베이스에 등록하는 것이다. 이 데이타베이스에 등록이 된후 template1 데이타베이스에서 create database 명령으로 데이타베이스를 생성하면 자동적으로 생성된 데이타베이스에 PL/pgSQL이 등록이 되므로 편리하다.
등록에 앞서 다음을 확인하라.
postgreSQL의 PATH : 여기서의 PATH 는 /usr/local/pgsql 이다. 또한 pgsql 디렉토리 밑의 lib 디렉토리에서 plpgsql.so 를 확인하라. 아마도 이 화일은 다 존재할 것이다.
등록 과정
[postgres@nogadax postgres]# psql template1
template1=>
template1=> CREATE FUNCTION plpgsql_call_handler() RETURNS OPAQUE AS
template1-> '/usr/local/pgsql/lib/plpgsql.so' LANGUAGE 'c';
template1=>
template1=> CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql'
template1-> HANDLER plpgsql_call_handler
template1-> LANCOMPILER 'PL/pgSQL';
template1=> CREATE DATABASE nogadax;
template1=> \q
[postgres@nogadax postgres]#
2. 다음 방법은 생성한 데이타베이스마다 하나하나 다 등록을 하는 것이다.
등록 과정
[postgres@nogadax postgres]# psql nogadax
nogadax=>
nogadax=> CREATE FUNCTION plpgsql_call_handler() RETURNS OPAQUE AS
nogadax-> '/usr/local/pgsql/lib/plpgsql.so' LANGUAGE 'c';
nogadax=>
nogadax=> CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql'
nogadax-> HANDLER plpgsql_call_handler
nogadax-> LANCOMPILER 'PL/pgSQL';
nogadax=>
nogadax=> \q
[postgres@nogadax postgres]#
이제는 위의 두가지 방법중 하나를 선택하여 등록을 하면 된다.
다음은 아래 예제를 실행하는 방법이다.
[podtgres@nogadax postgres]$ psql nogadax
nogadax=> \i /home/postgres/trg.sql
.....
.....
-------------------------------------------------------Cut here!!
--coded BY NoGaDa-X 2000/02/19
--DROP all Object for safe_test
DROP FUNCTION ins_row();
DROP TRIGGER trg_test ON test1;
DROP TABLE test1;
DROP TABLE test2;
--Create Table
CREATE TABLE test1(
tab1_id int4,
tab1_name text
);
CREATE TABLE test2(
tab2_id int4,
tab2_memo text DEFAULT 'None'
);
--Create Function
CREATE FUNCTION ins_row() RETURNS OPAQUE
AS '
BEGIN
INSERT INTO test2(tab2_id) VALUES(new.tab1_id);
RETURN new;
END;
' LANGUAGE 'plpgsql';
--Create Trigger
CREATE TRIGGER trg_test
AFTER INSERT ON test1 FOR EACH ROW
EXECUTE PROCEDURE ins_row();
--INSERT Transaction
BEGIN;
INSERT INTO test1 values(1,'nogadax');
INSERT INTO test1 values(2,'winob');
INSERT INTO test1 values(3,'diver708');
INSERT INTO test1 values(4,'jini');
INSERT INTO test1 values(5,'opensys');
INSERT INTO test1 values(6,'Linuz');
END;
--SELECT TRACTION
BEGIN;
SELECT * FROM test1;
SELECT * FROM test2;
END;
----------------------------------------------------------End !!
CREATE TRIGGER trigger_name { BEFORE | AFTER } { event [OR,...] }
ON table FOR EACH { ROW | STATEMENT }
EXECUTE PROCEDURE funcname ();
trigger_name : TRIGGER 의 이름
table : Table 의 이름
event : INSERT , DELETE , UPDATE 들 중에서 하나 혹은 두세개를
TRIGGER 를 기동시키기 위한 event 로 봄
( 예 : INSERT OR UPDATE )
func_name : user 가 제공한 함수. 이 함수는 트리거가 생성되기 전에 만들어져야 한다.
또한, 이 함수의 return 형은 opaque이며 인수가 없어야 한다.
(이 부분은 PostgreSQL Programmer's Guide 에 나와 있는 부분인데 function
에서 왜 인수가 쓰이면 안되는지 그리고 opaque 형의 리턴 값만 되는지 를
정확히 설명한 부분이 없다.)
"CREATE TRIGGER"로 인해 TRIGGER 가 성공적으로 생성되면 CREATE 라는
메시지가 출력된다.
CREATE TRIGGER은 현재의 데이타베이스에 새로운 TRIGGER을 등록할 것이다.Trigger
은 테이블(릴레이션)과 연계되어서 미리 규정된 함수 func_name을 실행한다.
트리거는 트리거의 생성시 BEFORE키를 사용하여 Tuple(row,record)에 어떤 event가
발생하기 전에 기동되어 질수 있도록 규정되어질수 있으며 반대로 AFTER키를 사용하
여 event가 완료 후에 기동되게 할수도 있다.
(다음은 부분적으로 Postgres data changes visibility rule 이 참조되었다.)
트리거가 BEFORE에 의해 event전에 기동되어 진다면, 트리거는 현재의 Tuple에 대한
event를 건너뛰게 한다. 특히 INSERT나 UPDATE의 event에 대해서는 삽입되어질 튜플
의 변화를 인지할 수 없다. 즉, BEFORE성격의 트리거는 변경되어질 튜플들에 대해서
"invisible"한 상태이다. 단지, 처리되어질 event 만 인식할 수 있다.
또한, 트리거가 AFTER 키에 의해 event후에 기동되어지면, 최근의 삽입,UPDATE,삭제
등이 트리거에 "visible" 이다. 즉, 변경된 부분을 트리거가 인지할 수 있다.
event는 다중의 event 를 OR 에 의해 규정할 수 있다. 또한 동일한 릴레이션에 동일
한 event 를 지정하는 하나 이상의 트리거를 정의할 수 있으나, 이는 트리거의 기동
순서를 예측할 수 없게 된다.
트리거가 SQL 쿼리를 실행할때 다른 트리거들을 기동시킬 수 있으며 이를 CASCADE
트리거라 한다. 이러한 캐스캐이드 트리거의 레벨에는 제한이 없으므로 한번의 트리
거로 여러개의 다중의 트리거를 기동시킬 수 있다.
동일한 릴레이션에 대한 INSERT 트리거가 있다면 이 트리거는 다시 동일한 릴레이션
에 대한 트리거가 기동되어질 수 있다. 하지만 아직 PostgreSQL은 이런 트리거에 대
한 튜플의 동기화가 지원되지 않으므로 주의를 하여야 할 것이다.
CREATE TRIGGER 은 PostgreSQL의 확장된 기능이다.
단지 릴레이션(Table) 의 소유자만이 그 릴레이션에 트리거를 생성할 수 있다.
버젼 6.4에서 STATEMENT 는 구현되지 않았다.
CASCADING TRIGGER SAMPLE
----------------------------------------------------Cut here !!
--coded by NoGaDa-X
--cascading tigger
DROP TRIGGER tab1_trg ON test1;
DROP TRIGGER tab2_trg ON test2;
DROP TRIGGER tab3_trg ON test3;
DROP FUNCTION tab1_func();
DROP FUNCTION tab2_func();
DROP FUNCTION tab3_func();
DROP TABLE test1;
DROP TABLE test2;
DROP TABLE test3;
DROP TABLE test4;
--Create Table
CREATE TABLE test1(
tab1_id INT4
);
CREATE TABLE test2(
tab2_id INT4
);
CREATE TABLE test3(
tab3_id INT4
);
CREATE TABLE test4(
tab4_id INT4
);
--Create Function
CREATE FUNCTION tab1_func() RETURNS opaque
AS '
BEGIN
INSERT INTO test2 values( new.tab1_id);
RETURN new;
END;
' LANGUAGE 'plpgsql';
CREATE FUNCTION tab2_func() RETURNS opaque
AS '
BEGIN
INSERT INTO test3 values( new.tab2_id);
RETURN new;
END;
' LANGUAGE 'plpgsql';
CREATE FUNCTION tab3_func() RETURNS opaque
AS '
BEGIN
INSERT INTO test4 values( new.tab3_id);
RETURN new;
END;
' LANGUAGE 'plpgsql';
--Create Trigger
CREATE TRIGGER tab1_trg AFTER
INSERT OR UPDATE ON test1 FOR EACH ROW
EXECUTE PROCEDURE tab1_func();
CREATE TRIGGER tab2_trg AFTER
INSERT OR UPDATE ON test2 FOR EACH ROW
EXECUTE PROCEDURE tab2_func();
CREATE TRIGGER tab3_trg AFTER
INSERT OR UPDATE ON test3 FOR EACH ROW
EXECUTE PROCEDURE tab3_func();
--transaction
BEGIN;
INSERT INTO test1 VALUES (1);
SELECT * from test1;
INSERT INTO test1 VALUES (2);
SELECT * from test2;
INSERT INTO test1 VALUES (3);
SELECT * from test3;
INSERT INTO test1 VALUES (4);
SELECT * from test4;
END;
-----------------------------------------------End !!