CODEWITHSUNDEEP
sqlpostgresql
KILIBIBI
KILIBIBI

Reputation: 25

TRIGGER BEFORE DELETE, doesn't delete data in table

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)

Upvotes: 0

Views: 2209

Answers (2)

Haleemur Ali
Haleemur Ali

Reputation: 28243

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

Upvotes: 2

JGH
JGH

Reputation: 17836

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.

Upvotes: 1

Related Questions