Changes between Version 2 and Version 3 of P7

Timestamp:

04/25/26 22:58:36 (7 days ago)

Author:

211099

Comment:

--

P7

@@ -107 +107 @@
 }}}
 
-== Stored Procedures / Functions
+== Stored Procedures and Functions
 {{{
 -- Table for daily top story
@@ -200 +200 @@
 $$;
 }}}
+
+== Triggers
+==== Automatically updates the story timestamp on every update
+{{{
+CREATE OR REPLACE FUNCTION update_timestamp()
+RETURNS TRIGGER
+LANGUAGE plpgsql
+AS $$
+BEGIN
+    NEW.story_updated_at := CURRENT_TIMESTAMP;
+    RETURN NEW;
+END;
+$$;
+
+CREATE OR REPLACE TRIGGER story_update_timestamp
+    BEFORE UPDATE ON story
+    FOR EACH ROW
+EXECUTE FUNCTION update_timestamp();
+}}}
+==== Sends a notification to the writer when someone likes their story
+{{{
+CREATE OR REPLACE FUNCTION notify_writer_on_like()
+RETURNS TRIGGER
+LANGUAGE plpgsql
+AS $$
+DECLARE
+    v_writer_id      INTEGER;
+    v_liker_username VARCHAR;
+    v_story_desc     VARCHAR;
+BEGIN
+    SELECT s.user_id, s.short_description
+    INTO v_writer_id, v_story_desc
+    FROM story s
+    WHERE s.story_id = NEW.story_id;
+
+    SELECT username INTO v_liker_username
+    FROM users
+    WHERE user_id = NEW.user_id;
+
+    -- Do not notify if the writer likes their own story
+    IF v_writer_id != NEW.user_id THEN
+        INSERT INTO notification (
+            notification_content,
+            is_read,
+            recipient_user_id,
+            type,
+            link,
+            notification_created_at
+        )
+        VALUES (
+            v_liker_username || ' liked your story "' || v_story_desc || '"',
+            FALSE,
+            v_writer_id,
+            'like',
+            '/story/' || NEW.story_id::VARCHAR,
+            CURRENT_TIMESTAMP
+        );
+    END IF;
+
+    RETURN NEW;
+END;
+$$;
+
+CREATE OR REPLACE TRIGGER on_story_liked
+    AFTER INSERT ON likes
+    FOR EACH ROW
+EXECUTE FUNCTION notify_writer_on_like();
+}}}
+
+==== Prevents adding a story to a reading list if it is not published
+{{{
+CREATE OR REPLACE FUNCTION check_story_published_before_adding()
+RETURNS TRIGGER
+LANGUAGE plpgsql
+AS $$
+BEGIN
+    IF NOT EXISTS (
+        SELECT 1 FROM "status"
+        WHERE story_id = NEW.story_id
+          AND status   = 'published'
+    ) THEN
+        RAISE EXCEPTION
+            'story_id=% is not published and cannot be added to a reading list.',
+            NEW.story_id;
+    END IF;
+    RETURN NEW;
+END;
+$$;
+
+CREATE OR REPLACE TRIGGER enforce_published_before_list_add
+    BEFORE INSERT ON reading_list_items
+    FOR EACH ROW
+EXECUTE FUNCTION check_story_published_before_adding();
+}}}
+==== Trigger: prevents a writer from collaborating on their own story
+{{{
+CREATE OR REPLACE FUNCTION check_not_own_story()
+RETURNS TRIGGER
+LANGUAGE plpgsql
+AS $$
+BEGIN
+    IF EXISTS (
+        SELECT 1 FROM story
+        WHERE story_id = NEW.story_id
+          AND user_id  = NEW.user_id
+    ) THEN
+        RAISE EXCEPTION
+            'user_id=% is the owner of story_id=% and cannot be a collaborator.',
+            NEW.user_id,
+            NEW.story_id;
+    END IF;
+    RETURN NEW;
+END;
+$$;
+
+CREATE OR REPLACE TRIGGER prevent_self_collaboration
+    BEFORE INSERT ON collaboration
+    FOR EACH ROW
+EXECUTE FUNCTION check_not_own_story();
+}}}