PostgreSQL | 트리거(Trigger) 사용 예제

1. 더미 테이블 생성

CREATE TABLE tb_account(uid INT, id TEXT, pw TEXT, register date);

2. UpdateRegister 함수 선언

CREATE FUNCTION UpdateRegister() RETURNS OPAQUE AS
$$
DECLARE
BEGIN
    UPDATE tb_account SET register = now() WHERE uid = new.uid;
    RETURN NULL;
END
$$ LANGUAGE 'plpgsql';

3. UpdateRegisterTrigger 트리거 선언

CREATE TRIGGER UpdateRegisterTrigger
AFTER INSERT on tb_account
FOR EACH ROW EXECUTE PROCEDURE UpdateRegister();

4. 트리거(Trigger) 사용

INSERT INTO tb_account(uid, id, pw) VALUES (1, 'james', '007');
INSERT INTO tb_account(uid, id, pw) VALUES (2, 'tomas', '008');
INSERT INTO tb_account(uid, id, pw) VALUES (3, 'suzan', '008');

5. 결과확인

SELECT * FROM tb_account;