Hi, I have some tables where I would like to store data in history tables on delete and update.

In one example I have a many to many relation, and when deleting a master the children also gets deleted. I want the master record and the children record to be copied to history tables.


Here are the 2 tables...

CREATE TABLE product_option_combination
(
id bigint NOT NULL,
up_price_amount numeric(5,2),
up_price_percentage smallint,
product_id bigint,
name character varying(255) NOT NULL,
CONSTRAINT product_option_combination_pkey PRIMARY KEY (id ),
CONSTRAINT fk_product FOREIGN KEY (product_id)
REFERENCES product (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE CASCADE
)

CREATE TABLE product_option_comb_mtm_product_option
(
product_option_comb_id bigint NOT NULL,
product_option_id bigint NOT NULL,
CONSTRAINT product_option_comb_mtm_product_option_pkey PRIMARY KEY (product_option_comb_id , product_option_id ),
CONSTRAINT product_option_comb_id_fk FOREIGN KEY (product_option_comb_id)
REFERENCES product_option_combination (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT product_option_id_fk FOREIGN KEY (product_option_id)
REFERENCES product_option (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
)

and the 2 history tables...

CREATE TABLE product_option_combination_history
(
id bigint NOT NULL,
product_option_combination_id bigint NOT NULL,
edit_ts timestamp without time zone NOT NULL,
up_price_amount numeric(5,2),
up_price_percentage smallint,
product_id bigint,
name character varying(255) NOT NULL,
CONSTRAINT product_option_combination_history_pkey PRIMARY KEY (id )
)

CREATE TABLE product_option_comb_mtm_product_option_history
(
id bigint NOT NULL,
product_option_history_comb_id bigint NOT NULL,
product_option_id bigint NOT NULL,
edit_ts timestamp without time zone NOT NULL,
CONSTRAINT product_option_comb_mtm_product_option_history_pke y PRIMARY KEY (id )
)

and the trigger + functions

CREATE TRIGGER product_option_combination_update_delete
AFTER DELETE OR UPDATE
ON product_option_combination
FOR EACH ROW
EXECUTE PROCEDURE product_option_combination_history();

CREATE OR REPLACE FUNCTION product_option_combination_history()
RETURNS trigger AS
$BODY$
DECLARE id bigint;
DECLARE r record;
BEGIN

SELECT nextval('product_option_combination_history_sequen ce') INTO id;

INSERT INTO product_option_combination_history (id, product_option_combination_id, edit_ts, up_price_amount, up_price_percentage, product_id, name)
VALUES (id, OLD.id, now(), OLD.up_price_amount, OLD.up_price_percentage, OLD.product_id, OLD.name);

BEGIN
FOR r IN SELECT product_option_id FROM product_option_comb_mtm_product_option WHERE product_option_comb_id = OLD.id
LOOP
INSERT INTO product_option_comb_mtm_product_option_history(id, edit_ts, product_option_history_comb_id, product_option_id)
VALUES (nextval('product_option_comb_mtm_product_option_h istory_sequence'), now(), id, r.product_option_id);
END LOOP;
END;

RETURN NULL;
END;
$BODY$
LANGUAGE plpgsql;

I am using Hibernate as ORM. And when deleting a product_option_combination hibernate deletes the children first.

From sql log file...
delete from product_option_comb_mtm_product_option where product_option_id=3 and product_option_comb_id=3
delete from product_option_comb_mtm_product_option where product_option_id=8 and product_option_comb_id=3
delete from product_option_combination where id=3
So I guess my function does not work cause the product_option_comb_mtm_product_option are deleted before the product_option_combination is deleted.

So I might need a trigger on product_option_comb_mtm_product_option that will store data in history... but here I don't know the id of the product_option_combination_history, cause that is not created yet...

So I am a little stuck here.