1 -- @tag: requirement_spec_delete_trigger_fix2
2 -- @description: Fixes für Delete-Trigger bei Pflichtenheften
3 -- @depends: requirement_spec_delete_trigger_fix
5 -- requirement_spec_id: link to requirement specs (the versioned
6 -- document) working_copy_id: link to requirement spec working copy
7 -- (only set if working copy is currently at a version level)
8 ALTER TABLE requirement_spec_versions ADD COLUMN requirement_spec_id INTEGER;
9 ALTER TABLE requirement_spec_versions ADD COLUMN working_copy_id INTEGER;
11 UPDATE requirement_spec_versions ver
12 SET requirement_spec_id = (
14 FROM requirement_specs rs
15 WHERE rs.version_id = ver.id
18 UPDATE requirement_spec_versions ver
19 SET working_copy_id = (
21 FROM requirement_specs rs
22 WHERE (rs.version_id = ver.id)
23 AND (rs.working_copy_id IS NULL)
26 ALTER TABLE requirement_spec_versions ALTER COLUMN requirement_spec_id SET NOT NULL;
27 ALTER TABLE requirement_spec_versions ADD FOREIGN KEY (requirement_spec_id) REFERENCES requirement_specs (id) ON DELETE CASCADE;
28 ALTER TABLE requirement_spec_versions ADD FOREIGN KEY (working_copy_id) REFERENCES requirement_specs (id) ON DELETE CASCADE;
30 ALTER TABLE requirement_specs DROP COLUMN version_id;
31 ALTER TABLE requirement_specs DROP CONSTRAINT requirement_specs_working_copy_id_fkey;
32 ALTER TABLE requirement_specs ADD FOREIGN KEY (working_copy_id) REFERENCES requirement_specs (id) ON DELETE CASCADE;
34 ALTER TABLE requirement_spec_items DROP CONSTRAINT requirement_spec_items_requirement_spec_id_fkey;
35 ALTER TABLE requirement_spec_items ADD FOREIGN KEY (requirement_spec_id) REFERENCES requirement_specs (id) ON DELETE CASCADE;
37 ALTER TABLE requirement_spec_item_dependencies DROP CONSTRAINT requirement_spec_item_dependencies_depended_item_id_fkey;
38 ALTER TABLE requirement_spec_item_dependencies ADD FOREIGN KEY (depended_item_id) REFERENCES requirement_spec_items (id) ON DELETE CASCADE;
39 ALTER TABLE requirement_spec_item_dependencies DROP CONSTRAINT requirement_spec_item_dependencies_depending_item_id_fkey;
40 ALTER TABLE requirement_spec_item_dependencies ADD FOREIGN KEY (depending_item_id) REFERENCES requirement_spec_items (id) ON DELETE CASCADE;
42 ALTER TABLE requirement_spec_text_blocks DROP CONSTRAINT requirement_spec_text_blocks_requirement_spec_id_fkey;
43 ALTER TABLE requirement_spec_text_blocks ADD FOREIGN KEY (requirement_spec_id) REFERENCES requirement_specs (id) ON DELETE CASCADE;
45 -- Trigger for deleting depending stuff if a requirement spec is deleted.
46 CREATE OR REPLACE FUNCTION requirement_spec_delete_trigger() RETURNS trigger AS $$
48 IF TG_WHEN = 'AFTER' THEN
49 DELETE FROM trigger_information WHERE (key = 'deleting_requirement_spec') AND (value = CAST(OLD.id AS TEXT));
54 RAISE DEBUG 'before delete trigger on %', OLD.id;
56 INSERT INTO trigger_information (key, value) VALUES ('deleting_requirement_spec', CAST(OLD.id AS TEXT));
58 RAISE DEBUG ' Converting items into sections items for %', OLD.id;
59 UPDATE requirement_spec_items SET item_type = 'section', parent_id = NULL WHERE requirement_spec_id = OLD.id;
61 RAISE DEBUG ' And we out for %', OLD.id;
67 -- Trigger for deleting depending stuff if a requirement spec item is deleted.
68 CREATE OR REPLACE FUNCTION requirement_spec_item_before_delete_trigger() RETURNS trigger AS $$
70 RAISE DEBUG 'delete trig RSitem old id %', OLD.id;
71 INSERT INTO trigger_information (key, value) VALUES ('deleting_requirement_spec_item', CAST(OLD.id AS TEXT));
72 DELETE FROM requirement_spec_items WHERE (parent_id = OLD.id);
73 DELETE FROM trigger_information WHERE (key = 'deleting_requirement_spec_item') AND (value = CAST(OLD.id AS TEXT));
74 RAISE DEBUG 'delete trig END %', OLD.id;