Trigger to update hasMergeRequest in VulnerabilityReads

What does this MR do and why?

Adds trigger to update has_merge_request column on vulnerability_reads table when there is a Insert/delete to vulnerability_merge_request_links table.

2nd MR for #420613 (closed)

We also require an additional trigger update to cover the scenario when the create merge request action is performed on a vulnerability finding on a non default branch (similar to !112233 (merged)) and that will be covered in a follow up MR. See related discussion: !128372 (comment 1503506564)

Database review

rake db:migrate:up

main: == [advisory_lock_connection] object_id: 224700, pg_backend_pid: 4467
main: == 20230803125434 AddHasMergeRequestOnVulnerabilityReadsTrigger: migrating ====
main: -- execute("CREATE OR REPLACE FUNCTION set_has_merge_request_on_vulnerability_reads()\nRETURNS TRIGGER AS\n$$\nBEGIN\nUPDATE\n  vulnerability_reads\nSET\n  has_merge_request = true\nWHERE\n  vulnerability_id = NEW.vulnerability_id AND has_merge_request IS FALSE;\nRETURN NULL;\n\nEND\n$$ LANGUAGE PLPGSQL\n")
main:    -> 0.0058s
main: -- execute("CREATE OR REPLACE FUNCTION unset_has_merge_request_on_vulnerability_reads()\nRETURNS TRIGGER\nLANGUAGE plpgsql\nAS $$\nDECLARE\n  has_merge_request_links integer;\nBEGIN\n  PERFORM 1\n  FROM\n    vulnerability_reads\n  WHERE\n    vulnerability_id = OLD.vulnerability_id\n  FOR UPDATE;\n\n  SELECT 1 INTO has_merge_request_links FROM vulnerability_merge_request_links WHERE vulnerability_id = OLD.vulnerability_id LIMIT 1;\n\n  IF (has_merge_request_links = 1) THEN\n    RETURN NULL;\n  END IF;\n\n  UPDATE\n    vulnerability_reads\n  SET\n    has_merge_request = false\n  WHERE\n    vulnerability_id = OLD.vulnerability_id;\n\n  RETURN NULL;\nEND\n$$;\n")
main:    -> 0.0014s
main: -- execute("CREATE TRIGGER trigger_update_has_merge_request_on_vulnerability_mr_links_update\nAFTER INSERT ON vulnerability_merge_request_links\nFOR EACH ROW\nEXECUTE FUNCTION set_has_merge_request_on_vulnerability_reads();\n")
main:    -> 0.0019s
main: -- execute("CREATE TRIGGER trigger_update_has_merge_request_on_vulnerability_mr_links_delete\nAFTER DELETE ON vulnerability_merge_request_links\nFOR EACH ROW\nEXECUTE FUNCTION unset_has_merge_request_on_vulnerability_reads();\n")
main:    -> 0.0007s
main: == 20230803125434 AddHasMergeRequestOnVulnerabilityReadsTrigger: migrated (0.0220s)

main: == [advisory_lock_connection] object_id: 224700, pg_backend_pid: 4467

rake db:migrate:down

main: == [advisory_lock_connection] object_id: 224740, pg_backend_pid: 3974
main: == 20230803125434 AddHasMergeRequestOnVulnerabilityReadsTrigger: reverting ====
main: -- execute("DROP TRIGGER IF EXISTS trigger_update_has_merge_request_on_vulnerability_mr_links_update ON vulnerability_merge_request_links")
main:    -> 0.0019s
main: -- execute("DROP FUNCTION IF EXISTS set_has_merge_request_on_vulnerability_reads()")
main:    -> 0.0008s
main: -- execute("DROP TRIGGER IF EXISTS trigger_update_has_merge_request_on_vulnerability_mr_links_delete ON vulnerability_merge_request_links")
main:    -> 0.0002s
main: -- execute("DROP FUNCTION IF EXISTS unset_has_merge_request_on_vulnerability_reads()")
main:    -> 0.0002s
main: == 20230803125434 AddHasMergeRequestOnVulnerabilityReadsTrigger: reverted (0.0064s)

How to set up and validate locally

1. Clone https://gitlab.com/svedova/test-remediations-v2 project into local GDK setupa.
2. Run a pipeline on the main branch of the cloned project in local. Visit the pipeline job page security tab and click on any vulnerability, then choose create Merge Request option.
3. We should observe that vulnerability_merge_request_links record is being created with the merge_request_id set to the above created merge request.
4. Also a vulnerability_read record should be created, with the has_merge_request field value as true.

MR acceptance checklist

This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.

• I have evaluated the MR acceptance checklist for this MR.

Related to #420613 (closed)