sql - TRIGGER BEFORE DELETE，不删除表中的数据

Question

当一行被修改/删除时，我正在处理我的数据库的历史记录。我的主表是“bati”和历史表“bati_history”，当删除或修改一行时，触发器假设将所有旧数据插入到 bati_history 中，然后在主表(bati)中删除。但是使用我的代码，该行被插入到历史记录中但没有在主表中删除，我不知道为什么。

我使用的是 PostgreSQL 11.2 64 位

代码:

主表:

CREATE TABLE IF NOT EXISTS bati(
    id_bati BIGSERIAL NOT NULL UNIQUE,
    code_bati VARCHAR(25) NOT NULL,
    code_parcelle CHAR(50) NOT NULL,
    ...);

历史表:

CREATE TABLE IF NOT EXISTS bati_history(
    id_history BIGSERIAL NOT NULL PRIMARY KEY,
    event CHAR(10) NOT NULL,
    date_save_history TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    id_bati BIGINT NOT NULL,
    code_bati VARCHAR(25) NOT NULL,
    code_parcelle CHAR(50) NOT NULL,
        ...);

函数

CREATE FUNCTION archive_bati() RETURNS TRIGGER AS $BODY$
  BEGIN
    IF (TG_OP = 'DELETE') THEN
      INSERT INTO bati_history (event,id_bati,code_bati,code_parcelle,...)
      VALUES ('DELETE',OLD.id_bati,OLD.code_bati,OLD.code_parcelle,OLD....);
    ELSIF (TG_OP = 'UPDATE') THEN
      INSERT INTO bati_history (event,id_bati,code_bati,code_parcelle,...)
      VALUES ('UPDATE',OLD.id_bati,OLD.code_bati,OLD.code_parcelle,OLD....);
    END IF;
    RETURN NEW;
  END;
$BODY$
LANGUAGE 'plpgsql';

触发器:

CREATE TRIGGER bati_trigger_before_delete BEFORE DELETE
ON bati FOR EACH ROW
EXECUTE PROCEDURE archive_bati();

CREATE TRIGGER bati_trigger_before_update BEFORE UPDATE
ON bati FOR EACH ROW
EXECUTE PROCEDURE archive_bati();

当我尝试 DELETE FROM bati; 时，我希望复制 bati_history 中的每一行，然后从 bati 中删除它们，但它们并没有从 bati 中删除，而且我的输出没有错误:

test=# INSERT INTO bati (id_bati,code_bati,code_parcelle,id_interne) VALUES (5,'CODEBATI001','CODEPARC001',02);
INSERT 0 1
test=# INSERT INTO bati (id_bati,code_bati,code_parcelle,id_interne) VALUES (6,'CODEBATI002','CODEPARC002',02);
INSERT 0 1
test=#  DELETE FROM bati;
DELETE 0

(对不起我的英语我是法国人)

Answer 1

您应该使用 if-else 分支来返回 NEW 或 OLD，具体取决于触发器操作。变量 TG_OP 具有文本类型，可以直接在插入查询中使用。

所以，函数定义变成:

CREATE FUNCTION archive_bati() 
    RETURNS TRIGGER AS $$
BEGIN
INSERT INTO bati_history (event,id_bati,code_bati,code_parcelle) 
VALUES (TG_OP, OLD.id_bati, OLD.code_bati, OLD.code_parcelle);
IF TG_OP = 'DELETE' 
    THEN RETURN OLD; 
    ELSE RETURN NEW;
END IF;
END;
$$ LANGUAGE PLPGSQL;

此外，当 1 个触发器就足够时，我似乎没有必要定义两个触发器:

CREATE TRIGGER bati_trigger_before_update BEFORE UPDATE OR DELETE
ON bati FOR EACH ROW
EXECUTE PROCEDURE archive_bati();