changeset 1002:8ec5ee1c2dc6 schema-update

Updated trigger and functions.
author Raimund Renkert <raimund.renkert@intevation.de>
date Fri, 01 Jul 2016 19:46:11 +0200
parents 1c4aa7ea63aa
children 552215760ba8
files db_schema/lada_schema.sql
diffstat 1 files changed, 18 insertions(+), 67 deletions(-) [+]
line wrap: on
line diff
--- a/db_schema/lada_schema.sql	Fri Jul 01 19:04:49 2016 +0200
+++ b/db_schema/lada_schema.sql	Fri Jul 01 19:46:11 2016 +0200
@@ -18,18 +18,6 @@
 
 SET search_path = land, pg_catalog;
 
---
--- Name: update_time_status(); Type: FUNCTION; Schema: land; Owner: -
---
-
-CREATE FUNCTION update_time_status() RETURNS trigger
-    LANGUAGE plpgsql
-    AS $$
-    BEGIN
-        NEW.tree_modified = now();
-        RETURN NEW;
-    END;
-$$;
 
 CREATE FUNCTION update_letzte_aenderung() RETURNS trigger
     LANGUAGE plpgsql
@@ -42,25 +30,24 @@
 
 
 --
--- Name: is_probe_fertig(integer); Type: FUNCTION; Schema: land; Owner: -
+-- Name: update_time_status(); Type: FUNCTION; Schema: land; Owner: -
 --
 
-CREATE FUNCTION is_probe_fertig(i1 integer) RETURNS boolean
-    LANGUAGE plpgsql STABLE SECURITY DEFINER
-    AS $_$
-DECLARE result BOOLEAN;
-BEGIN
-        SELECT (count(id) > 0) INTO result from land.messung where probe_id = $1 and fertig = TRUE;
-        RETURN result;
-END;
-$_$;
+CREATE FUNCTION update_tree_modified() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+    BEGIN
+        NEW.tree_modified = now();
+        RETURN NEW;
+    END;
+$$;
 
 
 --
 -- Name: update_time_messung(); Type: FUNCTION; Schema: land; Owner: -
 --
 
-CREATE FUNCTION update_time_messung() RETURNS trigger
+CREATE FUNCTION update_tree_modified_messung() RETURNS trigger
     LANGUAGE plpgsql
     AS $$
     BEGIN
@@ -74,38 +61,10 @@
 
 
 --
--- Name: update_time_messwert(); Type: FUNCTION; Schema: land; Owner: -
---
-
-CREATE FUNCTION update_time_messwert() RETURNS trigger
-    LANGUAGE plpgsql
-    AS $$
-    BEGIN
-        NEW.tree_modified = now();
-        RETURN NEW;
-    END;
-$$;
-
-
---
--- Name: update_time_ort(); Type: FUNCTION; Schema: land; Owner: -
---
-
-CREATE FUNCTION update_time_ort() RETURNS trigger
-    LANGUAGE plpgsql
-    AS $$
-    BEGIN
-        NEW.tree_modified = now();
-        RETURN NEW;
-    END;
-$$;
-
-
---
 -- Name: update_time_probe(); Type: FUNCTION; Schema: land; Owner: -
 --
 
-CREATE FUNCTION update_time_probe() RETURNS trigger
+CREATE FUNCTION update_tree_modified_probe() RETURNS trigger
     LANGUAGE plpgsql
     AS $$
     BEGIN
@@ -120,20 +79,6 @@
 $$;
 
 
---
--- Name: update_time_zusatzwert(); Type: FUNCTION; Schema: land; Owner: -
---
-
-CREATE FUNCTION update_time_zusatzwert() RETURNS trigger
-    LANGUAGE plpgsql
-    AS $$
-    BEGIN
-        NEW.tree_modified = now();
-        RETURN NEW;
-    END;
-$$;
-
-
 SET default_tablespace = '';
 
 SET default_with_oids = false;
@@ -252,7 +197,7 @@
     UNIQUE (mst_id, hauptproben_nr)
 );
 CREATE TRIGGER letzte_aenderung_probe BEFORE UPDATE ON probe FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung();
-CREATE TRIGGER tree_timestamp_probe BEFORE UPDATE ON probe FOR EACH ROW EXECUTE PROCEDURE update_time_probe();
+CREATE TRIGGER tree_modified_probe BEFORE UPDATE ON probe FOR EACH ROW EXECUTE PROCEDURE update_tree_modified_probe();
 
 
 --
@@ -282,7 +227,7 @@
     tree_modified timestamp without time zone DEFAULT now()
 );
 CREATE TRIGGER letzte_aenderung_ortszuordnung BEFORE UPDATE ON ortszuordnung FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung();
-CREATE TRIGGER tree_timestamp_ort BEFORE UPDATE ON ortszuordnung FOR EACH ROW EXECUTE PROCEDURE update_time_ort();
+CREATE TRIGGER tree_modified_ortszuordnung BEFORE UPDATE ON ortszuordnung FOR EACH ROW EXECUTE PROCEDURE update_tree_modified();
 
 
 --
@@ -301,7 +246,7 @@
     UNIQUE (probe_id, pzs_id)
 );
 CREATE TRIGGER letzte_aenderung_zusatzwert BEFORE UPDATE ON zusatz_wert FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung();
-CREATE TRIGGER tree_timestamp_zusatzwert BEFORE UPDATE ON zusatz_wert FOR EACH ROW EXECUTE PROCEDURE update_time_zusatzwert();
+CREATE TRIGGER tree_modified_zusatzwert BEFORE UPDATE ON zusatz_wert FOR EACH ROW EXECUTE PROCEDURE update_tree_modified();
 
 
 --
@@ -399,7 +344,8 @@
     geplant boolean DEFAULT false NOT NULL,
     tree_modified timestamp without time zone DEFAULT now()
 );
-CREATE TRIGGER tree_timestamp_messung BEFORE UPDATE ON messung FOR EACH ROW EXECUTE PROCEDURE update_time_messung();
+CREATE TRIGGER letzte_aenderung_messung BEFORE UPDATE ON messung FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung();
+CREATE TRIGGER tree_modified_messung BEFORE UPDATE ON messung FOR EACH ROW EXECUTE PROCEDURE update_tree_modified_messung();
 
 
 --
@@ -434,7 +380,7 @@
     UNIQUE (messungs_id, messgroesse_id)
 );
 CREATE TRIGGER letzte_aenderung_messwert BEFORE UPDATE ON messwert FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung();
-CREATE TRIGGER tree_timestamp_messwert BEFORE UPDATE ON messwert FOR EACH ROW EXECUTE PROCEDURE update_time_messwert();
+CREATE TRIGGER tree_modified_messwert BEFORE UPDATE ON messwert FOR EACH ROW EXECUTE PROCEDURE update_tree_modified();
 
 
 --
@@ -451,7 +397,7 @@
     status_wert integer NOT NULL REFERENCES stammdaten.status_wert,
     tree_modified timestamp without time zone DEFAULT now()
 );
-CREATE TRIGGER tree_timestamp_status BEFORE UPDATE ON status_protokoll FOR EACH ROW EXECUTE PROCEDURE update_time_status();
+CREATE TRIGGER tree_modified_status_protokoll BEFORE UPDATE ON status_protokoll FOR EACH ROW EXECUTE PROCEDURE update_tree_modified();
 
 
 --
This site is hosted by Intevation GmbH (Datenschutzerklärung und Impressum | Privacy Policy and Imprint)