时间戳

首页 > 解决方案 > Erro gatilhos Postgresql 关闭

sql - Erro gatilhos Postgresql 关闭

问题描述

我有 2 个表（“reports”和“RA_log”），一个用于用户记录聚集点，另一个用于记录日志。每次对“报告”表进行任何更改时，我都创建了一个审核功能。

CREATE TABLE Reports
(
report_id SERIAL PRIMARY KEY,
report_user_name VARCHAR(20) NOT NULL,
    report_location_name VARCHAR(50) NOT NULL,
    report_number_people INT NOT NULL,
    report_mask VARCHAR(20) NOT NULL,
    report_distance BOOLEAN NOT NULL,
report_observations VARCHAR(255),
report_date_time timestamp NOT NULL,
report_latitude VARCHAR(100) NOT NULL,
report_longitude VARCHAR(100) NOT NULL
);
CREATE TABLE RA_log
(
    log_change_type CHAR(1) NOT NULL,
   log_user VARCHAR NOT NULL,
    log_date_occurrence TIMESTAMP NOT NULL,
report_username VARCHAR NOT NULL,
report_quantity_people INTEGER
);

CREATE OR REPLACE FUNCTION audita_RA_log() RETURNS TRIGGER AS $$
BEGIN
IF (TG_OP = 'DELETE') THEN
INSERT INTO RA_log VALUES ('D', USER, now(), OLD.*);
RETURN OLD;
ELSIF (TG_OP = 'UPDATE') THEN
INSERT INTO RA_log VALUES ('U', USER, now(), NEW.*);
RETURN NEW;
ELSIF (TG_OP = 'INSERT') THEN
INSERT INTO RA_log VALUES ('I', USER, now(), NEW.*);
RETURN NEW;
END IF;
RETURN NULL; --the result is ignored because the trigger is AFTER
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trig_log_RA
AFTER INSERT OR UPDATE OR DELETE ON reports
FOR EACH ROW EXECUTE PROCEDURE audita_RA_log();

INSERT INTO reports (report_username, report_location_name, report_quantity_people, report_mask, report_distance, report_observacoes, report_date_time, report_latitude, report_longitude)
VALUES ('Scooby', 'Lucky Lottery', 20, 'No', '1', 'Lots of people', '2021-06-22 4:10:25-07','-6.47926', '-35.4348' );

但是当我要输入一些数据时。正在返回以下错误：错误：插入的表达式比目标列多第 1 行：插入 RA_log 值（'D'，用户，现在（），旧。）^ 查询：插入 RA_log 值（'D'，用户， now(), OLD. ) CONTEXT: função PL/pgSQL audita_ra_log() linha 4 em comando SQL SQL state: 42601

标签： sqlpostgresqltriggers

解决方案

您的表ra_log有 5 列，但您的插入语句试图插入 13 列：类型、用户、日期和 10 列Reports。这是因为 old.* 和 new.* 实际上意味着触发器被触发的表中的每一列。在这种情况下，所有 10 列来自Reports. 您有 2 个选项。

  1. 直接参考 中定义的列reports。所以 old.report_user_name, old.report_number_people （或新的。视情况而定）你想要的ra_log。
create or replace function audita_ra_log() 
 returns trigger 
 language plpgsql 
as $$ 
begin
    if (tg_op = 'delete') then
        insert into ra_log values ('d', user, now(), old.report_id, old.report_user_name);
    else
         insert into ra_log values (lower(substr(tg_op,1,1)), user, now(), new.report_id, new.report_user_name);
    end if;
end;    
$$;
  1. 如果您想要所有列，Reports则停止列 report_username、report_quantity_people 并将它们替换为 hstore 或 json 的单个列。相应地更改触发器。
CREATE TABLE ra_log( 
       log_change_type CHAR(1) NOT NULL,
     , log_user VARCHAR NOT NULL,
     , log_date_occurrence TIMESTAMP NOT NULL,
     , reports_value json
);

create or replace function audita_ra_log() 
 returns trigger 
 language plpgsql 
as $$ 
begin
    if (tg_op = 'delete') then
        insert into ra_log values ('d', user, now(), row_to_json(old.*));
    else
         insert into ra_log values (lower(substr(tg_op,1,1)), user, now(), row_to_json(new.*));
    end if;
end;    
$$; 

If keeping the complete row I prefer [hstore][1] as it keeps actual column names, but json is probably the more common.

推荐阅读