1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
|
CREATE OR REPLACE FUNCTION delais_traitement() RETURNS trigger AS $$
BEGIN
-- On verifie si l'entree existe deja
IF EXISTS (SELECT 1 FROM graph_activite WHERE "num_demande" = NEW."num_demande" AND "chapitre" = NEW."chapitre") THEN
RETURN NULL;
END IF;
-- On formate toutes les donnees de type date
NEW.dateheure_val_bio := to_timestamp(NEW.dateheure_val_bio,'DDMMYYHH24MI')::timestamp without time zone;
NEW.heure_val_bio := date_part('hour', NEW.dateheure_val_bio);
NEW.heure_val_tec := date_part('hour', NEW.dateheure_val_tec);
NEW.dateheure_val_pvt := to_timestamp(NEW.dateheure_val_pvt,'DDMMYYHH24MI')::timestamp without time zone;
NEW.heure_val_pvt := date_part('hour', NEW.dateheure_val_pvt);
NEW.jour_pvt := date_part('dow', NEW.dateheure_val_pvt);
-- On recupere le type de chapitre
NEW.chapitre := (SELECT type FROM chapitre WHERE code_chapitre = NEW.chapitre);
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
-- Creation du trigger
CREATE TRIGGER delais_traitement BEFORE INSERT ON graph_activite
FOR EACH ROW EXECUTE PROCEDURE delais_traitement();
-- Test d'une insertion
INSERT INTO graph_activite(num_demande, code_labo, code_corr, chapitre, code_exam, code_pvt, ope_val_bio, dateheure_val_bio, ope_val_tec, dateheure_val_tec, dateheure_val_pvt)
VALUES('B30507O7506', 'O', 'XO', 'BIONI', '1GLYF', 'OGOTH', 'PGOTH', '0705131652', 'BgVal', '0705130730', '2013-05-07 16:52:13'); |
