PostgreSQLでトリガの動作確認

-- テーブル作成 ---------------------------------------
CREATE TABLE trigger_data
(
    id   smallserial,
    data varchar(100)
);
ALTER TABLE trigger_data
    RENAME COLUMN data TO temp_data;


CREATE TABLE update_history
(
    id   smallserial,
    data varchar(100),
    date timestamp(0) without time zone
);
ALTER TABLE update_history
    RENAME COLUMN data TO temp_data;
ALTER TABLE update_history
    RENAME COLUMN date to regist_date;
ALTER TABLE update_history
    ADD COLUMN target_id smallint;


CREATE TABLE insert_history
(
    id   smallserial,
    data varchar(100),
    date timestamp(0) without time zone
);
ALTER TABLE insert_history
    RENAME COLUMN data TO temp_data;
ALTER TABLE insert_history
    RENAME COLUMN date TO regist_date;


CREATE TABLE delete_history
(
    id   smallserial,
    data varchar(100),
    date timestamp(0) without time zone
);
ALTER TABLE delete_history
    RENAME COLUMN data TO temp_data;
ALTER TABLE delete_history
    RENAME COLUMN date TO regist_date;


CREATE TABLE truncate_history
(
    id   smallserial,
    data varchar(100),
    date timestamp(0) without time zone
);
ALTER TABLE truncate_history
    RENAME COLUMN data TO temp_data;
ALTER TABLE truncate_history
    RENAME COLUMN date TO regist_date;
ALTER TABLE truncate_history
    RENAME COLUMN temp_data TO table_name;


-- トリガ用関数作成 -------------------------------------------------
CREATE OR REPLACE FUNCTION func_for_insert() RETURNS trigger AS
$$
BEGIN
    INSERT INTO insert_history(temp_data, regist_date)
    VALUES (NEW.temp_data, now());

    RETURN NULL;
END;
$$ LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION func_for_update() RETURNS trigger AS
$$
BEGIN
    INSERT INTO update_history(temp_data, regist_date, target_id)
    VALUES (OLD.temp_data, now(), OLD.id);

    -- 本関数はBEFOREで実行されるので、NEWを返さないとupdate文の変更が適用されない。
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION func_for_delete() RETURNS trigger AS
$$
BEGIN
    INSERT INTO delete_history(temp_data, regist_date)
    VALUES (OLD.temp_data, now());

    -- NEWを指定すると削除されない。
    -- OLDを返すことで削除対象がはっきりするためと思われる。
    RETURN OLD;
END;
$$ LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION func_for_truncate() RETURNS trigger AS
$$
BEGIN
    INSERT INTO truncate_history(table_name, regist_date)
    VALUES (TG_TABLE_SCHEMA || '.' || TG_TABLE_NAME, now());

    RETURN OLD;
END;
$$ LANGUAGE plpgsql;


-- トリガ作成とデータ操作 ------------------------
-- INSERT
CREATE TRIGGER trg_for_insert
    AFTER INSERT
    ON trigger_data
    FOR EACH ROW
EXECUTE FUNCTION func_for_insert();

INSERT INTO trigger_data(temp_data)
VALUES ('kakikukeko');


-- UPDATE
CREATE TRIGGER trg_for_update
    BEFORE UPDATE
    ON trigger_data
    FOR EACH ROW
EXECUTE FUNCTION func_for_update();

UPDATE trigger_data
SET temp_data = 'updateしました'
WHERE id = 7;


-- DELETE
CREATE TRIGGER trg_for_delete
    BEFORE DELETE
    ON trigger_data
    FOR EACH ROW
EXECUTE FUNCTION func_for_delete();

DELETE
FROM trigger_data
WHERE id = 4;


-- TRUNCATE
CREATE TRIGGER trg_for_truncate
    BEFORE TRUNCATE
    ON trigger_data
    -- TRUNCATEは行を指定することがないので、FOR STATEMENTを指定する必要がある。
    FOR STATEMENT
EXECUTE FUNCTION func_for_truncate();

TRUNCATE trigger_data;