Mercurial > lada > lada-server
comparison db_schema/stammdaten_schema.sql @ 926:9121d99a471e
Use databse triggers for update on field 'letzteAenderung'.
| author | Raimund Renkert <raimund.renkert@intevation.de> |
|---|---|
| date | Fri, 29 Apr 2016 16:38:33 +0200 |
| parents | d8c66007fa14 |
| children | 379480a94c81 |
comparison
equal
deleted
inserted
replaced
| 923:20547dfc436e | 926:9121d99a471e |
|---|---|
| 12 | 12 |
| 13 CREATE SCHEMA stammdaten; | 13 CREATE SCHEMA stammdaten; |
| 14 | 14 |
| 15 SET search_path = stammdaten, pg_catalog; | 15 SET search_path = stammdaten, pg_catalog; |
| 16 | 16 |
| 17 CREATE FUNCTION update_letzte_aenderung() RETURNS trigger | |
| 18 LANGUAGE plpgsql | |
| 19 AS $$ | |
| 20 BEGIN | |
| 21 NEW.letzte_aenderung = now(); | |
| 22 RETURN NEW; | |
| 23 END; | |
| 24 $$; | |
| 17 | 25 |
| 18 CREATE FUNCTION get_media_from_media_desk(media_desk character varying) RETURNS character varying | 26 CREATE FUNCTION get_media_from_media_desk(media_desk character varying) RETURNS character varying |
| 19 LANGUAGE plpgsql | 27 LANGUAGE plpgsql |
| 20 AS $$ | 28 AS $$ |
| 21 declare | 29 declare |
| 217 mst_id character varying(5) NOT NULL REFERENCES mess_stelle, | 225 mst_id character varying(5) NOT NULL REFERENCES mess_stelle, |
| 218 bezeichnung character varying(120) NOT NULL, | 226 bezeichnung character varying(120) NOT NULL, |
| 219 letzte_aenderung timestamp without time zone, | 227 letzte_aenderung timestamp without time zone, |
| 220 UNIQUE(da_erzeuger_id, netzbetreiber_id) | 228 UNIQUE(da_erzeuger_id, netzbetreiber_id) |
| 221 ); | 229 ); |
| 230 CREATE TRIGGER letzte_aenderung_datensatz_erzeuger BEFORE UPDATE ON datensatz_erzeuger FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung(); | |
| 222 | 231 |
| 223 ALTER SEQUENCE datensatz_erzeuger_id_seq OWNED BY datensatz_erzeuger.id; | 232 ALTER SEQUENCE datensatz_erzeuger_id_seq OWNED BY datensatz_erzeuger.id; |
| 224 | 233 |
| 225 | 234 |
| 226 CREATE SEQUENCE de_vg_id_seq | 235 CREATE SEQUENCE de_vg_id_seq |
| 452 mpl_id character varying(3) NOT NULL, | 461 mpl_id character varying(3) NOT NULL, |
| 453 bezeichnung character varying(120) NOT NULL, | 462 bezeichnung character varying(120) NOT NULL, |
| 454 letzte_aenderung timestamp without time zone, | 463 letzte_aenderung timestamp without time zone, |
| 455 UNIQUE(mpl_id, netzbetreiber_id) | 464 UNIQUE(mpl_id, netzbetreiber_id) |
| 456 ); | 465 ); |
| 466 CREATE TRIGGER letzte_aenderung_messprogramm_kategorie BEFORE UPDATE ON messprogramm_kategorie FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung(); | |
| 457 | 467 |
| 458 ALTER SEQUENCE messprogramm_kategorie_id_seq | 468 ALTER SEQUENCE messprogramm_kategorie_id_seq |
| 459 OWNED BY messprogramm_kategorie.id; | 469 OWNED BY messprogramm_kategorie.id; |
| 460 | 470 |
| 461 | 471 |
| 520 aktiv character(1), | 530 aktiv character(1), |
| 521 anlage_id integer, | 531 anlage_id integer, |
| 522 oz_id integer, | 532 oz_id integer, |
| 523 UNIQUE(ort_id, netzbetreiber_id) | 533 UNIQUE(ort_id, netzbetreiber_id) |
| 524 ); | 534 ); |
| 535 CREATE TRIGGER letzte_aenderung_ort BEFORE UPDATE ON ort FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung(); | |
| 525 | 536 |
| 526 ALTER TABLE ONLY ort | 537 ALTER TABLE ONLY ort |
| 527 ADD CONSTRAINT ort_anlage_fkey FOREIGN KEY (anlage_id) REFERENCES ort(id); | 538 ADD CONSTRAINT ort_anlage_fkey FOREIGN KEY (anlage_id) REFERENCES ort(id); |
| 528 | 539 |
| 529 ALTER TABLE ONLY ort | 540 ALTER TABLE ONLY ort |
| 606 tp character varying(3), | 617 tp character varying(3), |
| 607 typ character(1), | 618 typ character(1), |
| 608 letzte_aenderung timestamp without time zone, | 619 letzte_aenderung timestamp without time zone, |
| 609 UNIQUE(prn_id, netzbetreiber_id) | 620 UNIQUE(prn_id, netzbetreiber_id) |
| 610 ); | 621 ); |
| 622 CREATE TRIGGER letzte_aenderung_probenehmer BEFORE UPDATE ON probenehmer FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung(); | |
| 611 | 623 |
| 612 ALTER SEQUENCE probenehmer_id_seq OWNED BY probenehmer.id; | 624 ALTER SEQUENCE probenehmer_id_seq OWNED BY probenehmer.id; |
| 613 | 625 |
| 614 | 626 |
| 615 CREATE SEQUENCE query_id_seq | 627 CREATE SEQUENCE query_id_seq |