tnBcBcMt

1CREATE TABLE IF NOT EXISTS bati(
2    id_bati BIGSERIAL NOT NULL UNIQUE,
3    code_bati VARCHAR(25) NOT NULL,
4    code_parcelle CHAR(50) NOT NULL,
5    ...); 
6     
7CREATE TABLE IF NOT EXISTS bati_history(
8    id_history BIGSERIAL NOT NULL PRIMARY KEY,
9    event CHAR(10) NOT NULL,
10    date_save_history TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
11    id_bati BIGINT NOT NULL,
12    code_bati VARCHAR(25) NOT NULL,
13    code_parcelle CHAR(50) NOT NULL,
14        ...); 
15     
16CREATE FUNCTION archive_bati() RETURNS TRIGGER AS $BODY$
17  BEGIN
18    IF (TG_OP = 'DELETE') THEN
19      INSERT INTO bati_history (event,id_bati,code_bati,code_parcelle,...)
20      VALUES ('DELETE',OLD.id_bati,OLD.code_bati,OLD.code_parcelle,OLD....);
21    ELSIF (TG_OP = 'UPDATE') THEN
22      INSERT INTO bati_history (event,id_bati,code_bati,code_parcelle,...)
23      VALUES ('UPDATE',OLD.id_bati,OLD.code_bati,OLD.code_parcelle,OLD....);
24    END IF;
25    RETURN NEW;
26  END;
27$BODY$
28LANGUAGE 'plpgsql'; 
29     
30CREATE TRIGGER bati_trigger_before_delete BEFORE DELETE
31ON bati FOR EACH ROW
32EXECUTE PROCEDURE archive_bati();
33
34CREATE TRIGGER bati_trigger_before_update BEFORE UPDATE
35ON bati FOR EACH ROW
36EXECUTE PROCEDURE archive_bati(); 
37     
38test=# INSERT INTO bati (id_bati,code_bati,code_parcelle,id_interne) VALUES (5,'CODEBATI001','CODEPARC001',02);
39INSERT 0 1
40test=# INSERT INTO bati (id_bati,code_bati,code_parcelle,id_interne) VALUES (6,'CODEBATI002','CODEPARC002',02);
41INSERT 0 1
42test=#  DELETE FROM bati;
43DELETE 0