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 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49
| -- Table d'injection centre de cout
CREATE TABLE calcmi.inj_centre_cout
(
mi character varying(10) NOT NULL, -- périmètre
reseau character(25), -- Réseau
dept character varying(4), -- Département
c_cout character varying(15), -- Centre de cout
ccout_lib character varying(250) NOT NULL, -- Libelle du centre de cout
Creation character varying(4),
inactive character varying(4),
lib_modifier character varying(25),
CONSTRAINT pk_calcmi.inj_centre_cout PRIMARY KEY (c_cout, ccout_lib)
)
WITH (
OIDS=FALSE
);
ALTER TABLE calcmi.inj_centre_cout OWNER TO postgres;
GRANT ALL ON TABLE calcmi.inj_centre_cout TO lorrainng;
GRANT ALL ON TABLE calcmi.inj_centre_cout TO postgres;
COMMENT ON TABLE calcmi.inj_centre_cout IS 'Table d injection des centres de cout';
COMMENT ON COLUMN calcmi.inj_centre_cout.mi IS 'Perimetre';
COMMENT ON COLUMN calcmi.inj_centre_cout.reseau IS 'Reseau';
COMMENT ON COLUMN calcmi.inj_centre_cout.dept IS 'Departement';
COMMENT ON COLUMN calcmi.inj_centre_cout.ccout_lib IS 'Libelle du centre de cout';
COMMENT ON COLUMN calcmi.inj_centre_cout.c_cout IS 'Centre de cout';
Create Function calcmi.Injection_OK() Returns Trigger
As $$
Begin
If Exists (
Select
*
From
calcmi.inj_centre_cout AS injcc
Where
injcc.creation > 0 AND injcc.inactive > 0
OR injcc.inactive > 0 AND injcc.lib_modifier > 0
OR injcc.lib_modifier > 0 AND injcc.creation > 0
) Then
Return NULL; -- indique à insert d'ignorer la ligne
End If;
Return NEW; -- indique à insert de traiter la ligne
End;
$$ Language plpgsql;
Create Trigger Injection_OK
Before Insert On calcmi.inj_centre_cout
For Each Row
Execute Procedure calcmi.Injection_OK(); |
Partager