PostgreSQL触发器插入后在另一个表上写入

Question

我正试图为pgsql编写一个触发器，以记录特定表上的插入、更新和删除。

我在创建触发器时在171位置出现了一个错误，但我真的不知道为什么。

日志表

+----------+-----------+---------------------------+
|table_name|column_name|data_type                  |
+----------+-----------+---------------------------+
|logs      |id         |bigint                     |
|logs      |action     |smallint                   |
|logs      |table      |character varying          |
|logs      |primary    |bigint                     |
|logs      |log        |json                       |
|logs      |processed  |boolean                    |
|logs      |created_at |timestamp without time zone|
|logs      |updated_at |timestamp without time zone|
+----------+-----------+---------------------------+

功能和触发

CREATE OR REPLACE FUNCTION insert_log (INT, VARCHAR, INT, VARCHAR) RETURNS INT AS $$
BEGIN
INSERT INTO public.logs ("action", "table", "primary", "log")
VALUES ($1, $2, $3, $4)
RETURNING id;
END
$$ LANGUAGE plpgsql;
CREATE TRIGGER trigger_clientes_insert
AFTER INSERT ON xxx.legacy_clientes
FOR EACH ROW
EXECUTE FUNCTION insert_log (1, "clientes", id, row_to_json(NEW));

Answer 1

您不应该将参数传递给触发器函数：它会自动接收new，这在这里就足够了。此外，触发器函数应该返回一个TRIGGER。

考虑：

CREATE OR REPLACE FUNCTION insert_log () 
RETURNS TRIGGER AS $$
BEGIN
INSERT INTO public.logs ("action", "table", "primary", "log")
VALUES (1, NEW.clientes, NEW.id, row_to_json(NEW);
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trigger_clientes_insert
AFTER INSERT ON xxx.legacy_clientes
FOR EACH ROW
EXECUTE PROCEDURE insert_log();