From b6d633011163002c2923f3809cd5ead2c53e2aa3 Mon Sep 17 00:00:00 2001
From: Masaya Tojo <masaya@tojo.tokyo>
Date: Wed, 30 Aug 2023 00:19:17 +0900
Subject: Add update_timestamp trigger to photos table

---
 deploy/photos_update_timestamp.sql | 15 +++++++++++++++
 revert/photos_update_timestamp.sql |  9 +++++++++
 sqitch.plan                        |  1 +
 verify/photos_update_timestamp.sql | 14 ++++++++++++++
 4 files changed, 39 insertions(+)
 create mode 100644 deploy/photos_update_timestamp.sql
 create mode 100644 revert/photos_update_timestamp.sql
 create mode 100644 verify/photos_update_timestamp.sql

diff --git a/deploy/photos_update_timestamp.sql b/deploy/photos_update_timestamp.sql
new file mode 100644
index 0000000..366d3c2
--- /dev/null
+++ b/deploy/photos_update_timestamp.sql
@@ -0,0 +1,15 @@
+-- Deploy photos:photos_update_timestamp to pg
+-- requires: schema
+-- requires: photos
+-- requires: update_timestamp
+
+BEGIN;
+
+SET search_path to photos;
+
+CREATE OR REPLACE TRIGGER update_timestamp
+  BEFORE UPDATE ON photos
+  FOR EACH ROW
+  EXECUTE FUNCTION update_timestamp();
+
+COMMIT;
diff --git a/revert/photos_update_timestamp.sql b/revert/photos_update_timestamp.sql
new file mode 100644
index 0000000..9e5561d
--- /dev/null
+++ b/revert/photos_update_timestamp.sql
@@ -0,0 +1,9 @@
+-- Revert photos:photos_update_timestamp from pg
+
+BEGIN;
+
+SET search_path to photos;
+
+DROP TRIGGER update_timestamp ON photos;
+
+COMMIT;
diff --git a/sqitch.plan b/sqitch.plan
index 7362c06..eb3f8f2 100644
--- a/sqitch.plan
+++ b/sqitch.plan
@@ -5,3 +5,4 @@
 schema 2023-08-27T13:52:33Z Masaya Tojo <masaya@tojo.tokyo> # Add schema for all photos objects.
 photos [schema] 2023-08-29T13:12:25Z Masaya Tojo <masaya@tojo.tokyo> # Create table to manage my photos.
 update_timestamp [schema] 2023-08-29T15:03:35Z Masaya Tojo <masaya@tojo.tokyo> # Add function to update timestamp
+photos_update_timestamp [schema photos update_timestamp] 2023-08-29T15:15:40Z Masaya Tojo <masaya@tojo.tokyo> # Add update_timestamp trigger to photos table
diff --git a/verify/photos_update_timestamp.sql b/verify/photos_update_timestamp.sql
new file mode 100644
index 0000000..92aaa87
--- /dev/null
+++ b/verify/photos_update_timestamp.sql
@@ -0,0 +1,14 @@
+-- Verify photos:photos_update_timestamp on pg
+
+BEGIN;
+
+DO $$
+  BEGIN
+    ASSERT (SELECT 1 from information_schema.triggers
+             WHERE trigger_schema = 'photos'
+               AND event_object_table = 'photos'
+               AND trigger_name = 'update_timestamp');
+  END
+$$;
+
+ROLLBACK;
-- 
cgit v1.2.3