TRIGGER BEFORE DELETE, doesn't delete data in table

Question

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.

I'm on PostgreSQL 11.2 64-bit

The code :

Main table:

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

History table:

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,
        ...);

The function

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';

TRIGGERS:

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();

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)

Haleemur Ali · Accepted Answer · 2019-06-11 14:01:45Z

2

You should use the if-else branching to either return NEW OR OLD depending on the trigger operation. The variable TG_OP has a text type & could be used in the insert query directly.

So, the function definition becomes:

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;

Also, it seems unnecessary to me to define two triggers when 1 will suffice:

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

answered Jun 11, 2019 at 14:01

Haleemur Ali

28.6k6 gold badges67 silver badges89 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

JGH · Accepted Answer · 2019-06-11 13:29:39Z

1

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.

From the doc:

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.

answered Jun 11, 2019 at 13:29

JGH

18.2k5 gold badges40 silver badges57 bronze badges

Collectives™ on Stack Overflow

TRIGGER BEFORE DELETE, doesn't delete data in table

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related