Hello, my problem is as follows I have two tables 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 of the market is deleted, they are recorded in the table called logbook
ID | boxnum | entrydatem | exitdatem | partnum
This is easy using a trigger that is triggered by a delete but the problem I have is that I want the connection boxnum to be linked to the market so that I can get the partnum that had the deleted record at that moment 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 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 deleted, it appears as deleted but if I show the fields in this table they appear again that apparently were erased, then I changed the before for the after but this made it impossible for the part of the where to be fulfilled, I do not know how to solve it, if you could help me I would appreciate it.