git » subscription-tool.git » commit 5c69013

diff --git a/src/main/resources/db/migration/V1_1__create_table_deletions.sql b/src/main/resources/db/migration/V1_1__create_table_deletions.sql
new file mode 100644
index 0000000..040dd52
--- /dev/null
+++ b/src/main/resources/db/migration/V1_1__create_table_deletions.sql
@@ -0,0 +1,21 @@
+CREATE TABLE deletions (
+  id BIGINT GENERATED ALWAYS AS IDENTITY UNIQUE,
+  mail VARCHAR(255) NOT NULL,
+  registration TIMESTAMP,
+  confirmation TIMESTAMP,
+  deletion TIMESTAMP
+);
+
+CREATE OR REPLACE FUNCTION archive_deleted_subscription()
+RETURNS TRIGGER AS $$
+BEGIN
+  INSERT INTO deletions(mail, registration, confirmation, deletion)
+    VALUES (OLD.mail, OLD.registration, OLD.confirmation, CURRENT_TIMESTAMP);
+  RETURN OLD;
+END;
+$$ LANGUAGE plpgsql;
+
+CREATE TRIGGER after_delete_subscription_trigger
+AFTER DELETE ON subscriptions
+FOR EACH ROW
+EXECUTE FUNCTION archive_deleted_subscription();