1.1 --- a/update/core-update.v4.0.0-v4.0.1.sql	Sun Mar 04 18:10:07 2018 +0100
     1.2 +++ b/update/core-update.v4.0.0-v4.0.1.sql	Tue Sep 04 14:30:41 2018 +0200
     1.3 @@ -66,4 +66,39 @@
     1.4  COMMENT ON COLUMN "system_application"."base_url"     IS 'Base URL for users';
     1.5  COMMENT ON COLUMN "system_application"."manifest_url" IS 'URL referring to a manifest that can be used for application (type/version) discovery';
     1.6  
     1.7 +CREATE OR REPLACE FUNCTION "write_event_initiative_revoked_trigger"()
     1.8 +  RETURNS TRIGGER
     1.9 +  LANGUAGE 'plpgsql' VOLATILE AS $$
    1.10 +    DECLARE
    1.11 +      "issue_row"  "issue"%ROWTYPE;
    1.12 +      "area_row"   "area"%ROWTYPE;
    1.13 +      "draft_id_v" "draft"."id"%TYPE;
    1.14 +    BEGIN
    1.15 +      IF OLD."revoked" ISNULL AND NEW."revoked" NOTNULL THEN
    1.16 +        -- NOTE: lock for primary key update to avoid new drafts
    1.17 +        PERFORM NULL FROM "initiative" WHERE "id" = NEW."id" FOR UPDATE;
    1.18 +        SELECT * INTO "issue_row" FROM "issue"
    1.19 +          WHERE "id" = NEW."issue_id" FOR SHARE;
    1.20 +        SELECT * INTO "area_row" FROM "area"
    1.21 +          WHERE "id" = "issue_row"."area_id" FOR SHARE;
    1.22 +        -- NOTE: FOR SHARE cannot be used with DISTINCT in view "current_draft"
    1.23 +        PERFORM NULL FROM "draft" WHERE "initiative_id" = NEW."id" FOR SHARE;
    1.24 +        SELECT "id" INTO "draft_id_v" FROM "current_draft"
    1.25 +          WHERE "initiative_id" = NEW."id";
    1.26 +        INSERT INTO "event" (
    1.27 +            "event", "member_id",
    1.28 +            "unit_id", "area_id", "policy_id", "issue_id", "state",
    1.29 +            "initiative_id", "draft_id"
    1.30 +          ) VALUES (
    1.31 +            'initiative_revoked', NEW."revoked_by_member_id",
    1.32 +            "area_row"."unit_id", "issue_row"."area_id",
    1.33 +            "issue_row"."policy_id",
    1.34 +            NEW."issue_id", "issue_row"."state",
    1.35 +            NEW."id", "draft_id_v"
    1.36 +          );
    1.37 +      END IF;
    1.38 +      RETURN NULL;
    1.39 +    END;
    1.40 +  $$;
    1.41 +
    1.42  COMMIT;