Revision 7159
Added by Aaron Marcuse-Kubitza over 11 years ago
| vegbien.sql | ||
|---|---|---|
| 1017 | 1017 |
|
| 1018 | 1018 |
|
| 1019 | 1019 |
-- |
| 1020 |
-- Name: taxondetermination_set_iscurrent(); Type: FUNCTION; Schema: public; Owner: - |
|
| 1020 |
-- Name: taxondetermination_set_iscurrent(integer); Type: FUNCTION; Schema: public; Owner: -
|
|
| 1021 | 1021 |
-- |
| 1022 | 1022 |
|
| 1023 |
CREATE FUNCTION taxondetermination_set_iscurrent() RETURNS trigger
|
|
| 1023 |
CREATE FUNCTION taxondetermination_set_iscurrent(taxonoccurrence_id_ integer) RETURNS void
|
|
| 1024 | 1024 |
LANGUAGE plpgsql |
| 1025 | 1025 |
AS $$ |
| 1026 | 1026 |
BEGIN |
| 1027 | 1027 |
UPDATE taxondetermination SET iscurrent = false |
| 1028 |
WHERE taxonoccurrence_id = new.taxonoccurrence_id AND iscurrent;
|
|
| 1028 |
WHERE taxonoccurrence_id = taxonoccurrence_id_ AND iscurrent;
|
|
| 1029 | 1029 |
|
| 1030 | 1030 |
UPDATE taxondetermination SET iscurrent = true |
| 1031 | 1031 |
WHERE taxondetermination_id = ( |
| 1032 | 1032 |
SELECT taxondetermination_id FROM taxondetermination |
| 1033 |
WHERE taxonoccurrence_id = new.taxonoccurrence_id
|
|
| 1033 |
WHERE taxonoccurrence_id = taxonoccurrence_id_
|
|
| 1034 | 1034 |
ORDER BY |
| 1035 | 1035 |
-- Scrubbed determinations must come first for scrub.make to work |
| 1036 | 1036 |
COALESCE(determinationtype = 'computer', false) DESC/*true first*/ |
| ... | ... | |
| 1038 | 1038 |
, determinationdate DESC NULLS LAST |
| 1039 | 1039 |
LIMIT 1 |
| 1040 | 1040 |
); |
| 1041 |
|
|
| 1041 |
END; |
|
| 1042 |
$$; |
|
| 1043 |
|
|
| 1044 |
|
|
| 1045 |
-- |
|
| 1046 |
-- Name: taxondetermination_set_iscurrent_on_delete(); Type: FUNCTION; Schema: public; Owner: - |
|
| 1047 |
-- |
|
| 1048 |
|
|
| 1049 |
CREATE FUNCTION taxondetermination_set_iscurrent_on_delete() RETURNS trigger |
|
| 1050 |
LANGUAGE plpgsql |
|
| 1051 |
AS $$ |
|
| 1052 |
BEGIN |
|
| 1053 |
PERFORM taxondetermination_set_iscurrent(old.taxonoccurrence_id); |
|
| 1054 |
RETURN old; |
|
| 1055 |
END; |
|
| 1056 |
$$; |
|
| 1057 |
|
|
| 1058 |
|
|
| 1059 |
-- |
|
| 1060 |
-- Name: taxondetermination_set_iscurrent_on_insert(); Type: FUNCTION; Schema: public; Owner: - |
|
| 1061 |
-- |
|
| 1062 |
|
|
| 1063 |
CREATE FUNCTION taxondetermination_set_iscurrent_on_insert() RETURNS trigger |
|
| 1064 |
LANGUAGE plpgsql |
|
| 1065 |
AS $$ |
|
| 1066 |
BEGIN |
|
| 1067 |
PERFORM taxondetermination_set_iscurrent(new.taxonoccurrence_id); |
|
| 1042 | 1068 |
RETURN new; |
| 1043 | 1069 |
END; |
| 1044 | 1070 |
$$; |
| ... | ... | |
| 7350 | 7376 |
|
| 7351 | 7377 |
|
| 7352 | 7378 |
-- |
| 7353 |
-- Name: taxondetermination_set_iscurrent; Type: TRIGGER; Schema: public; Owner: - |
|
| 7379 |
-- Name: taxondetermination_set_iscurrent_on_delete; Type: TRIGGER; Schema: public; Owner: -
|
|
| 7354 | 7380 |
-- |
| 7355 | 7381 |
|
| 7356 |
CREATE TRIGGER taxondetermination_set_iscurrent AFTER INSERT ON taxondetermination FOR EACH ROW EXECUTE PROCEDURE taxondetermination_set_iscurrent();
|
|
| 7382 |
CREATE TRIGGER taxondetermination_set_iscurrent_on_delete AFTER DELETE ON taxondetermination FOR EACH ROW EXECUTE PROCEDURE taxondetermination_set_iscurrent_on_delete();
|
|
| 7357 | 7383 |
|
| 7358 | 7384 |
|
| 7359 | 7385 |
-- |
| 7386 |
-- Name: taxondetermination_set_iscurrent_on_insert; Type: TRIGGER; Schema: public; Owner: - |
|
| 7387 |
-- |
|
| 7388 |
|
|
| 7389 |
CREATE TRIGGER taxondetermination_set_iscurrent_on_insert AFTER INSERT ON taxondetermination FOR EACH ROW EXECUTE PROCEDURE taxondetermination_set_iscurrent_on_insert(); |
|
| 7390 |
|
|
| 7391 |
|
|
| 7392 |
-- |
|
| 7360 | 7393 |
-- Name: taxonlabel_0_matched_label_id_self_ref; Type: TRIGGER; Schema: public; Owner: - |
| 7361 | 7394 |
-- |
| 7362 | 7395 |
|
Also available in: Unified diff
schemas/vegbien.sql: taxondetermination: taxondetermination_set_iscurrent() trigger: Also run on delete, to mark another taxondetermination as the current one when a current taxondetermination is deleted