Mercurial > lada > lada-server
comparison db_schema/lada_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 | 54e83373bfae |
| children | 690b6506e0a2 |
comparison
equal
deleted
inserted
replaced
| 923:20547dfc436e | 926:9121d99a471e |
|---|---|
| 37 NEW.tree_modified = now(); | 37 NEW.tree_modified = now(); |
| 38 RETURN NEW; | 38 RETURN NEW; |
| 39 END; | 39 END; |
| 40 $$; | 40 $$; |
| 41 | 41 |
| 42 CREATE FUNCTION update_letzte_aenderung() RETURNS trigger | |
| 43 LANGUAGE plpgsql | |
| 44 AS $$ | |
| 45 BEGIN | |
| 46 NEW.letzte_aenderung = now(); | |
| 47 RETURN NEW; | |
| 48 END; | |
| 49 $$; | |
| 42 | 50 |
| 43 SET search_path = land, pg_catalog; | 51 SET search_path = land, pg_catalog; |
| 52 | |
| 53 CREATE FUNCTION update_letzte_aenderung() RETURNS trigger | |
| 54 LANGUAGE plpgsql | |
| 55 AS $$ | |
| 56 BEGIN | |
| 57 NEW.letzte_aenderung = now(); | |
| 58 RETURN NEW; | |
| 59 END; | |
| 60 $$; | |
| 44 | 61 |
| 45 -- | 62 -- |
| 46 -- Name: is_probe_fertig(integer); Type: FUNCTION; Schema: land; Owner: - | 63 -- Name: is_probe_fertig(integer); Type: FUNCTION; Schema: land; Owner: - |
| 47 -- | 64 -- |
| 48 | 65 |
| 195 bezeichnung character varying(20) NOT NULL, | 212 bezeichnung character varying(20) NOT NULL, |
| 196 beschreibuing character varying(512), | 213 beschreibuing character varying(512), |
| 197 letzte_aenderung timestamp with time zone NOT NULL, | 214 letzte_aenderung timestamp with time zone NOT NULL, |
| 198 gueltig_bis timestamp with time zone | 215 gueltig_bis timestamp with time zone |
| 199 ); | 216 ); |
| 217 CREATE TRIGGER letzte_aenderung_list BEFORE UPDATE ON list FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung(); | |
| 200 | 218 |
| 201 | 219 |
| 202 -- | 220 -- |
| 203 -- Name: list_id_seq; Type: SEQUENCE; Schema: bund; Owner: - | 221 -- Name: list_id_seq; Type: SEQUENCE; Schema: bund; Owner: - |
| 204 -- | 222 -- |
| 253 messzeitpunkt timestamp with time zone, | 271 messzeitpunkt timestamp with time zone, |
| 254 fertig boolean DEFAULT false NOT NULL, | 272 fertig boolean DEFAULT false NOT NULL, |
| 255 status integer, | 273 status integer, |
| 256 letzte_aenderung timestamp without time zone DEFAULT now() | 274 letzte_aenderung timestamp without time zone DEFAULT now() |
| 257 ); | 275 ); |
| 276 CREATE TRIGGER letzte_aenderung_messung BEFORE UPDATE ON messung FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung(); | |
| 258 | 277 |
| 259 | 278 |
| 260 -- | 279 -- |
| 261 -- Name: messung_messung_id_alt_seq; Type: SEQUENCE; Schema: bund; Owner: - | 280 -- Name: messung_messung_id_alt_seq; Type: SEQUENCE; Schema: bund; Owner: - |
| 262 -- | 281 -- |
| 307 nwg_zu_messwert double precision, | 326 nwg_zu_messwert double precision, |
| 308 meh_id smallint NOT NULL, | 327 meh_id smallint NOT NULL, |
| 309 grenzwertueberschreitung boolean DEFAULT false, | 328 grenzwertueberschreitung boolean DEFAULT false, |
| 310 letzte_aenderung timestamp without time zone DEFAULT now() | 329 letzte_aenderung timestamp without time zone DEFAULT now() |
| 311 ); | 330 ); |
| 331 CREATE TRIGGER letzte_aenderung_messwert BEFORE UPDATE ON messwert FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung(); | |
| 312 | 332 |
| 313 | 333 |
| 314 -- | 334 -- |
| 315 -- Name: ort_id_seq; Type: SEQUENCE; Schema: bund; Owner: - | 335 -- Name: ort_id_seq; Type: SEQUENCE; Schema: bund; Owner: - |
| 316 -- | 336 -- |
| 333 ort_id bigint NOT NULL, | 353 ort_id bigint NOT NULL, |
| 334 ortszuordnung_typ character varying(1), | 354 ortszuordnung_typ character varying(1), |
| 335 ortszusatztext character varying(100), | 355 ortszusatztext character varying(100), |
| 336 letzte_aenderung timestamp without time zone DEFAULT now() | 356 letzte_aenderung timestamp without time zone DEFAULT now() |
| 337 ); | 357 ); |
| 358 CREATE TRIGGER letzte_aenderung_ortszuordnung BEFORE UPDATE ON ortszuordnung FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung(); | |
| 338 | 359 |
| 339 | 360 |
| 340 -- | 361 -- |
| 341 -- Name: COLUMN ortszuordnung.ortszuordnung_typ; Type: COMMENT; Schema: bund; Owner: - | 362 -- Name: COLUMN ortszuordnung.ortszuordnung_typ; Type: COMMENT; Schema: bund; Owner: - |
| 342 -- | 363 -- |
| 379 probeentnahme_ende timestamp with time zone, | 400 probeentnahme_ende timestamp with time zone, |
| 380 mittelungsdauer bigint, | 401 mittelungsdauer bigint, |
| 381 letzte_aenderung timestamp without time zone DEFAULT now() | 402 letzte_aenderung timestamp without time zone DEFAULT now() |
| 382 ); | 403 ); |
| 383 | 404 |
| 405 CREATE TRIGGER letzte_aenderung_probe BEFORE UPDATE ON probe FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung(); | |
| 406 | |
| 384 -- | 407 -- |
| 385 -- Name: COLUMN probe.id; Type: COMMENT; Schema: bund; Owner: - | 408 -- Name: COLUMN probe.id; Type: COMMENT; Schema: bund; Owner: - |
| 386 -- | 409 -- |
| 387 | 410 |
| 388 COMMENT ON COLUMN probe.id IS 'interner Probenschlüssel'; | 411 COMMENT ON COLUMN probe.id IS 'interner Probenschlüssel'; |
| 498 messwert_pzs double precision, | 521 messwert_pzs double precision, |
| 499 messfehler real, | 522 messfehler real, |
| 500 letzte_aenderung timestamp without time zone DEFAULT now(), | 523 letzte_aenderung timestamp without time zone DEFAULT now(), |
| 501 nwg_zu_messwert double precision | 524 nwg_zu_messwert double precision |
| 502 ); | 525 ); |
| 526 CREATE TRIGGER letzte_aenderung_zusatzwert BEFORE UPDATE ON zusatz_wert FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung(); | |
| 503 | 527 |
| 504 | 528 |
| 505 SET search_path = land, pg_catalog; | 529 SET search_path = land, pg_catalog; |
| 506 | 530 |
| 507 -- | 531 -- |
| 562 gueltig_bis integer, | 586 gueltig_bis integer, |
| 563 probe_nehmer_id integer, | 587 probe_nehmer_id integer, |
| 564 probe_kommentar character varying(80), | 588 probe_kommentar character varying(80), |
| 565 letzte_aenderung timestamp without time zone DEFAULT now() NOT NULL | 589 letzte_aenderung timestamp without time zone DEFAULT now() NOT NULL |
| 566 ); | 590 ); |
| 591 CREATE TRIGGER letzte_aenderung_messprogramm BEFORE UPDATE ON messprogramm FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung(); | |
| 567 | 592 |
| 568 -- | 593 -- |
| 569 -- Name: messprogramm_id_seq; Type: SEQUENCE OWNED BY; Schema: land; Owner: - | 594 -- Name: messprogramm_id_seq; Type: SEQUENCE OWNED BY; Schema: land; Owner: - |
| 570 -- | 595 -- |
| 571 | 596 |
| 588 messprogramm_id integer NOT NULL, | 613 messprogramm_id integer NOT NULL, |
| 589 mmt_id character varying(2) NOT NULL, | 614 mmt_id character varying(2) NOT NULL, |
| 590 messgroessen integer[], | 615 messgroessen integer[], |
| 591 letzte_aenderung timestamp without time zone DEFAULT now() | 616 letzte_aenderung timestamp without time zone DEFAULT now() |
| 592 ); | 617 ); |
| 618 CREATE TRIGGER letzte_aenderung_messprogramm_mmt BEFORE UPDATE ON messprogramm_mmt FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung(); | |
| 593 | 619 |
| 594 | 620 |
| 595 -- | 621 -- |
| 596 -- Name: messprogramm_mmt_id_seq; Type: SEQUENCE; Schema: land; Owner: - | 622 -- Name: messprogramm_mmt_id_seq; Type: SEQUENCE; Schema: land; Owner: - |
| 597 -- | 623 -- |