Create a trigger for a logbook in postgresql and get data from 2 distint tables after a delete

Hello my problem is the next I have two tables of which are called connection and this has the following columns

boxnum(pk) | date | partnum

boxnum is the pk

then there is the market table that has the following fields

boxnumm(PK)(FK) | entrydate | exitdate | existence(boolean)

and what I want to do is that every time a record is deleted of the market that is registered in the table called logbook

Logbook table

ID | boxnum | entrydatem | exitdatem | partnum

this is easy using a trigger that is thrown by a delete but the problem I have is that I want the connection boxnum to be linked to the market boxnum so I can get the partnum I had at that time the record removed and what I have is this

CREATE OR REPLACE FUNCTION insertar_trigger() RETURNS TRIGGER AS $insertar$
DECLARE BEGIN
INSERT INTO public.logbook (boxnum, entrydatem, exitdatem, partnum) SELECT old.boxnumm, old.entrydate, old.exitdate, partnum
FROM public.market me INNER JOIN public.connection cp ON me.boxnumm = cp.boxnum
where cp.boxnum = old.boxnumm;
RETURN NULL;
END;
$insertar$ LANGUAGE plpgsql;


CREATE TRIGGER insertar_bitacora BEFORE DELETE 
ON mercado FOR EACH ROW 
EXECUTE PROCEDURE insertar_trigger();

but as you can see I use the before DELETE to do this works very well the trigger saves the data I want but in the market table the record is never erased appears as deleted but if I show the fields in this table again appear those that were apparently deleted, then I changed the before for the after but this made it impossible to fulfill the part of the where, I do not know how to fix it, if you could help me I would appreciate it.

1 answer

  • answered 2018-01-11 21:51 a_horse_with_no_name

    Quote from the manual

    Row-level triggers fired BEFORE can return null to signal the trigger manager to skip the rest of the operation for this row (i.e., subsequent triggers are not fired, and the INSERT/UPDATE/DELETE does not occur for this row)
    [...]
    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

    (emphasis mine)

    You are returning NULL from your BEFORE trigger. So your trigger function insert the row into the logbook table, but the original DELETE is cancelled.

    If you change RETURN NULL; to RETURN OLD; it should work.