Changes between Initial Version and Version 1 of DatabaseProgramming


Ignore:
Timestamp:
05/23/26 15:39:14 (3 days ago)
Author:
231159
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • DatabaseProgramming

    v1 v1  
     1= Database Programming
     2
     3This page describes six database triggers built for the article/comment platform. Each trigger has an original version and a revised version. The revisions add input validation, convert some triggers into stored procedures for better control flow, and improve edge-case handling. For each trigger, the document explains what it does, how it works, and why the changes were made.
     4
     5== Trigger 1: Notify Author on Comment
     6
     7||= Table =||   comment   ||
     8||= Event =||   AFTER INSERT   ||
     9||= Revised? =||   yes - validation added   ||
     10
     11** What it does **
     12
     13When a new row is inserted into the comment table, this trigger notifies the article author that someone has commented or replied to a comment on their article. It distinguishes between a top-level comment and a reply (based on whether parent_comment_id is set) and inserts an appropriate message into the notification table.
     14
     15** How it works **
     16
     17* Reads the article's author_id and title using the article_id from the new comment row.
     18* If the commenter is the article's own author, no notification is sent — authors don't need to be notified about their own comments.
     19* Builds a message: either 'Someone commented on your article' or 'Someone replied to a comment on your article', appending the article title.
     20* Inserts a row into the notification table with type = 'comment'.
     21
     22** Changes made and why **
     23
     24Null checks on article_id and user_id
     25{{{
     26RAISE EXCEPTION added if article_id or user_id is NULL on the incoming row.
     27}}}
     28Without these checks, the SELECT on the article table would silently return no rows or behave unpredictably if a NULL was passed. Making this explicit gives a clear error message instead of a silent failure or a misleading 'article not found' exception.
     29
     30Validate that the article actually exists
     31{{{
     32IF NOT FOUND check added after the SELECT on the article table.
     33}}}
     34
     35If a comment is inserted with an article_id that references a non-existent article, the author lookup would return nothing. Without the check, v_author_id and v_article_title would be NULL and the trigger would either insert a broken notification or fail silently. The explicit check raises a clear exception.
     36
     37WHEN clause on the trigger definition
     38{{{WHEN (NEW.article_id IS NOT NULL AND NEW.user_id IS NOT NULL) added to the CREATE TRIGGER statement.}}}
     39
     40This means the trigger function is only called when the row already satisfies the minimum requirements. It avoids invoking the function at all for obviously invalid rows, which is slightly more efficient and separates database-level filtering from application-level logic.
     41
     42== Trigger 2: Notify Author on Fact Check Verdict Change
     43
     44
     45||= Table =||   fact_check   ||
     46||= Event =||   AFTER UPDATE   ||
     47||= Revised? =||   yes - validation added   ||
     48
     49** What it does **
     50
     51When a fact-checker updates a row in the fact_checks table and the verdict column changes, this trigger notifies the article's author about the new verdict. It only fires if the fact check record is active (is_active = 1) to avoid sending notifications for archived or inactive checks.
     52
     53** How it works **
     54
     55* Checks if the verdict has actually changed using IS NOT DISTINCT FROM (which handles NULL comparisons correctly).
     56* Skips rows where is_active != 1. inactive fact checks should not generate notifications.
     57* Looks up the article's author and title using the article_id on the fact_checks row.
     58* Constructs a notification message that includes both the article title and the new verdict, then inserts it into the notification table.
     59
     60** Changes made and why **
     61
     62Null checks on article_id and verdict
     63
     64{{{RAISE EXCEPTION added for NULL article_id or NULL verdict before any other logic runs.}}}
     65
     66A NULL verdict change would pass the IS NOT DISTINCT FROM check and potentially generate a garbled notification. A NULL article_id would cause the author lookup to fail silently. Early validation makes both of these cases explicit errors.
     67
     68** Validate that the article exists **
     69
     70{{{IF NOT FOUND check added after the SELECT on the article table.}}}
     71
     72Same reasoning as Trigger 1. If the article_id on the fact_check row is stale or incorrect, the notification should not be created at all, and the caller should know why.
     73
     74** WHEN clause on the trigger definition **
     75
     76{{{WHEN (NEW.article_id IS NOT NULL AND NEW.verdict IS NOT NULL) added to the CREATE TRIGGER statement.}}}
     77
     78Mirrors the same pattern as Trigger 1. The database-level guard filters out rows that would definitely fail before the function is even called.
     79
     80== Trigger 3: Cascade Soft Delete to Comments
     81
     82||= Table =||   article   ||
     83||= Event =||   AFTER UPDATE OF deleted_at   ||
     84||= Revised? =||   yes - validation and targeted column watch added   ||
     85
     86** What it does **
     87
     88When an article is soft-deleted (deleted_at changes from NULL to a non-NULL timestamp), this trigger soft-deletes all active comments on that article by setting their deleted_at to the same timestamp. Comments that are already soft-deleted are not touched.
     89
     90How it works
     91
     92* Checks whether deleted_at has transitioned from NULL to a value. This is the soft-delete signal.
     93* Runs an UPDATE on the comment table, setting deleted_at = NEW.deleted_at for all comments where article_id matches and deleted_at is still NULL.
     94* Replies and top-level comments are both handled in the same UPDATE since they both share the article_id column.
     95
     96** Changes made and why **
     97
     98Null check on article ID
     99
     100{{{RAISE EXCEPTION added if NEW.id is NULL.}}}
     101
     102An article row with a NULL primary key should never exist, but explicitly checking it prevents the UPDATE on comment from matching every row (if id were NULL, WHERE article_id = NULL would match nothing in standard SQL, but it is still better to fail loudly than to rely on that behavior implicitly).
     103
     104Trigger now fires only on UPDATE OF deleted_at
     105
     106{{{CREATE TRIGGER changed from AFTER UPDATE to AFTER UPDATE OF deleted_at}}}
     107
     108The original trigger fired on any update to the article table and then checked the condition inside the function. The revised version tells PostgreSQL to only invoke the trigger when the deleted_at column is specifically modified. This reduces unnecessary function invocations. For example, updating an article's title or body would previously invoke the trigger even though it was guaranteed to do nothing.
     109
     110WHEN clause on the trigger
     111
     112{{{WHEN (NEW.id IS NOT NULL) added to the trigger definition.}}}
     113
     114A lightweight guard consistent with the pattern used across all revised triggers.
     115
     116== Trigger 4: Prevent Self-Follow
     117
     118||= Original form =||   BEFORE INSERT trigger on journalist_followers   ||
     119||= Revised form =||   Stored procedure: sp_follow_journalist   ||
     120||= Revised? =||   yes - converted from trigger to procedure   ||
     121
     122** What it does **
     123
     124Prevents a user from following themselves as a journalist, and enforces several other integrity rules around the follow action. In the original version this was a simple trigger. In the revised version it became a stored procedure that is called explicitly from the application layer.
     125
     126** Original trigger behaviour **
     127
     128* Fired BEFORE INSERT on journalist_followers.
     129* Compared journalist_id and follower_id — if they matched, it raised an exception.
     130* Allowed the insert if they were different.
     131
     132** Why it was converted to a stored procedure **
     133
     134{{{fn_prevent_self_follow trigger replaced with sp_follow_journalist stored procedure.}}}
     135
     136The trigger only did one thing: prevent self-follows. The stored procedure consolidates all follow-related validation in one place, making the rules easy to find, read, and modify. Specific reasons for the conversion:
     137
     138* A trigger fires after the row is already being inserted. A procedure can reject NULL inputs before any database work starts, giving a clearer error message to the caller. NULL safety:
     139* The trigger had no way to verify that the journalist being followed actually exists in the journalists table. The procedure does this explicitly. Journalist existence check:
     140* Similarly, the procedure verifies the follower exists in the users table. Follower existence check:
     141* The procedure checks whether the follow relationship already exists and raises a clear exception if so, rather than letting the insert fail with a generic constraint violation. Duplicate follow prevention:
     142* All callers use CALL sp_follow_journalist(journalist_id, follower_id), which makes the validation rules consistent and centrally maintained. Single entry point:
     143
     144== Trigger 5: Auto-Set published_at on Status Change
     145
     146||= Original form =||   BEFORE UPDATE trigger on article   ||
     147||= Revised form =||   Stored procedure: sp_update_article_status   ||
     148||= Revised? =||   yes - converted from trigger to procedure   ||
     149
     150** What it does **
     151
     152Manages the published_at timestamp on articles based on status transitions. When an article is published, published_at is set automatically. When it is moved out of the published status, published_at is cleared. For all other status transitions it is left unchanged.
     153
     154** Original trigger behaviour **
     155
     156* Fired BEFORE UPDATE on any article update.
     157* If status changed to 'published' and published_at was NULL, it set published_at to CURRENT_DATE.
     158* If status changed away from 'published', it set published_at to NULL.
     159* Used ELSE IF — note: in PL/pgSQL this is ELSIF, and the original code had a syntax issue with ELSE IF (two separate keywords) that would fail at runtime.
     160
     161** Why it was converted to a stored procedure **
     162
     163{{{fn_auto_set_published_at trigger replaced with sp_update_article_status stored procedure.}}}
     164
     165The trigger fired on any article update, but its logic was only meaningful when the status column changed. Converting it to a procedure provides several improvements:
     166
     167* The procedure checks that the new status is one of 'draft', 'published', or 'archived'. The trigger had no such check — any string could be written into the status column without validation. Valid status enforcement:
     168* If the article is already in the requested status, the procedure raises an exception immediately. The trigger would silently do nothing in this case, which could mask bugs in the calling code. No-op detection:
     169* The procedure verifies the article exists before attempting the update. Article existence check:
     170* When transitioning to published, COALESCE(v_current_published_at, CURRENT_DATE) preserves any existing published_at value rather than overwriting it. The trigger only checked IS NULL, which is functionally similar but less explicit. COALESCE for published_at:
     171* The ELSE IF in the original trigger is a PL/pgSQL syntax error. The procedure uses correct ELSIF and ELSE branching. Syntax fix:
     172
     173== Trigger 6: Block Comments on Invalid Articles
     174
     175||= Table =||   comment   ||
     176||= Event =||   BEFORE INSERT   ||
     177||= Revised? =||   no - original kept as-is   ||
     178
     179** What it does **
     180
     181Prevents comments from being inserted on articles that do not exist, have been soft-deleted, or are not in the 'published' status. This is a guard trigger. It raises an exception and cancels the insert if any condition is violated.
     182
     183** How it works **
     184
     185* Looks up the article using the article_id on the incoming comment row.
     186* If no matching article is found, raises an exception: 'Cannot comment: article does not exist.'
     187* If the article status is not 'published', raises an exception including the current status for context.
     188* If all checks pass, RETURN NEW allows the insert to proceed.
     189
     190** Why this trigger was not changed **
     191
     192This trigger is self-contained and already covers the necessary cases clearly. It does not need NULL input validation on article_id because a missing article_id would cause the NOT FOUND check to trigger immediately. The logic is straightforward and does not benefit from being moved into a procedure. It is a pure blocking guard with no side effects.
     193
     194== Summary of Changes
     195
     196||= Trigger =||= Original Form =||= Revised Form =||= Key Changes =||
     197||   Trigger 1   ||   Trigger on comment   ||   Trigger on comment   ||   Added null checks, article existence check, WHEN clause   ||
     198||   Trigger 2   ||   Trigger on fact_checks   ||   Trigger on fact_checks   ||   Added null checks, article existence check, WHEN clause   ||
     199||   Trigger 3   ||   Trigger on article UPDATE   ||   Trigger on UPDATE OF deleted_at   ||   Added null check, targeted column watch   ||
     200||   Trigger 4   ||   Trigger on journalist_followers   ||   Stored procedure   ||   Added entity existence checks, duplicate check, null safety   ||
     201||   Trigger 5   ||   Trigger on article UPDATE   ||   Stored procedure   ||   Added valid status check, no-op detection, syntax fix   ||
     202||   Trigger 6   ||   Trigger on comment   ||   Unchanged   ||   No changes needed   ||