TRIGGER BEFORE DELETE，不删除表中的数据 [英] TRIGGER BEFORE DELETE, doesn't delete data in table

问题描述

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

I'm working on history of my database when a row is modify/delete. My main table is "bati" and history table "bati_history", when a row is delete or modify, the trigger is suppose to insert into bati_history all the old data, then delete in the main table (bati). But with my code, the row is insert into the history but not delete in the main table and I don't know why.

我使用的是 PostgreSQL 11.2 64 位

I'm on PostgreSQL 11.2 64-bit

代码:

主表:

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 中删除，并且我的输出没有错误:

When I try DELETE FROM bati;, I expect to copy every row in bati_history, then delete them from bati, but they are not delete from bati, and I have this output without error :

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

(抱歉我的英语我是法国人)

(sorry for my english I'm french)

推荐答案

删除一行时，NEW 为空.如果 before 触发器返回空值，则表示该操作应该被取消.然后，您应该返回 OLD 进行删除，并返回 NEW 进行更新.

When you delete a row, NEW is null. If the before trigger returns a null, it means the operation should be cancelled. You should then return OLD for deletions, and NEW for updates.

来自 doc:

在 DELETE 上的前触发的情况下，返回的值没有直接效果，但它必须为非空以允许触发动作继续.请注意，在 DELETE 触发器中 NEW 为空，因此返回通常是不明智的.DELETE 触发器中的惯用语是返回旧的.

In the case of a before-trigger on DELETE, the returned value has no direct effect, but it has to be nonnull to allow the trigger action to proceed. Note that NEW is null in DELETE triggers, so returning that is usually not sensible. The usual idiom in DELETE triggers is to return OLD.

这篇关于TRIGGER BEFORE DELETE，不删除表中的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！