1 files changed, 40 insertions, 0 deletions
diff --git a/db/migrate/20220106112043_add_update_vulnerability_reads_trigger.rb b/db/migrate/20220106112043_add_update_vulnerability_reads_trigger.rb
new file mode 100644
index 00000000000..940ec638924
--- /dev/null
+++ b/db/migrate/20220106112043_add_update_vulnerability_reads_trigger.rb
@@ -0,0 +1,40 @@
+# frozen_string_literal: true
+
+class AddUpdateVulnerabilityReadsTrigger < Gitlab::Database::Migration[1.0]
+  include Gitlab::Database::SchemaHelpers
+
+  TRIGGER_NAME = 'trigger_update_vulnerability_reads_on_vulnerability_update'
+  FUNCTION_NAME = 'update_vulnerability_reads_from_vulnerability'
+
+  def up
+    create_trigger_function(FUNCTION_NAME, replace: true) do
+      <<~SQL
+        UPDATE
+          vulnerability_reads
+        SET
+          severity = NEW.severity,
+          state = NEW.state,
+          resolved_on_default_branch = NEW.resolved_on_default_branch
+        WHERE vulnerability_id = NEW.id;
+        RETURN NULL;
+      SQL
+    end
+
+    execute(<<~SQL)
+      CREATE TRIGGER #{TRIGGER_NAME}
+      AFTER UPDATE ON vulnerabilities
+      FOR EACH ROW
+      WHEN (
+        OLD.severity IS DISTINCT FROM NEW.severity OR
+        OLD.state IS DISTINCT FROM NEW.state OR
+        OLD.resolved_on_default_branch IS DISTINCT FROM NEW.resolved_on_default_branch
+      )
+      EXECUTE PROCEDURE #{FUNCTION_NAME}();
+    SQL
+  end
+
+  def down
+    drop_trigger(:vulnerabilities, TRIGGER_NAME)
+    drop_function(FUNCTION_NAME)
+  end
+end