Database Programming

This 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.

Trigger 1: Notify Author on Comment

Table	comment
Event	AFTER INSERT
Revised?	yes - validation added

What it does

When 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.

How it works

• Reads the article's author_id and title using the article_id from the new comment row.
• If the commenter is the article's own author, no notification is sent — authors don't need to be notified about their own comments.
• Builds a message: either 'Someone commented on your article' or 'Someone replied to a comment on your article', appending the article title.
• Inserts a row into the notification table with type = 'comment'.

Changes made and why

Null checks on article_id and user_id

RAISE EXCEPTION added if article_id or user_id is NULL on the incoming row.

Without 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.

Validate that the article actually exists

IF NOT FOUND check added after the SELECT on the article table.

If 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.

WHEN clause on the trigger definition WHEN (NEW.article_id IS NOT NULL AND NEW.user_id IS NOT NULL) added to the CREATE TRIGGER statement.

This 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.

Trigger 2: Notify Author on Fact Check Verdict Change

Table	fact_check
Event	AFTER UPDATE
Revised?	yes - validation added

What it does

When 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.

How it works

• Checks if the verdict has actually changed using IS NOT DISTINCT FROM (which handles NULL comparisons correctly).
• Skips rows where is_active != 1. inactive fact checks should not generate notifications.
• Looks up the article's author and title using the article_id on the fact_checks row.
• Constructs a notification message that includes both the article title and the new verdict, then inserts it into the notification table.

Changes made and why

Null checks on article_id and verdict

RAISE EXCEPTION added for NULL article_id or NULL verdict before any other logic runs.

A 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.

Validate that the article exists

IF NOT FOUND check added after the SELECT on the article table.

Same 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.

WHEN clause on the trigger definition

WHEN (NEW.article_id IS NOT NULL AND NEW.verdict IS NOT NULL) added to the CREATE TRIGGER statement.

Mirrors the same pattern as Trigger 1. The database-level guard filters out rows that would definitely fail before the function is even called.

Trigger 3: Cascade Soft Delete to Comments

Table	article
Event	AFTER UPDATE OF deleted_at
Revised?	yes - validation and targeted column watch added

What it does

When 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.

How it works

• Checks whether deleted_at has transitioned from NULL to a value. This is the soft-delete signal.
• 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.
• Replies and top-level comments are both handled in the same UPDATE since they both share the article_id column.

Changes made and why

Null check on article ID

RAISE EXCEPTION added if NEW.id is NULL.

An 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).

Trigger now fires only on UPDATE OF deleted_at

CREATE TRIGGER changed from AFTER UPDATE to AFTER UPDATE OF deleted_at

The 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.

WHEN clause on the trigger

WHEN (NEW.id IS NOT NULL) added to the trigger definition.

A lightweight guard consistent with the pattern used across all revised triggers.

Trigger 4: Prevent Self-Follow

Original form	BEFORE INSERT trigger on journalist_followers
Revised form	Stored procedure: sp_follow_journalist
Revised?	yes - converted from trigger to procedure

What it does

Prevents 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.

Original trigger behaviour

• Fired BEFORE INSERT on journalist_followers.
• Compared journalist_id and follower_id — if they matched, it raised an exception.
• Allowed the insert if they were different.

Why it was converted to a stored procedure

fn_prevent_self_follow trigger replaced with sp_follow_journalist stored procedure.

The 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:

• 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:
• 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:
• Similarly, the procedure verifies the follower exists in the users table. Follower existence check:
• 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:
• All callers use CALL sp_follow_journalist(journalist_id, follower_id), which makes the validation rules consistent and centrally maintained. Single entry point:

Trigger 5: Auto-Set published_at on Status Change

Original form	BEFORE UPDATE trigger on article
Revised form	Stored procedure: sp_update_article_status
Revised?	yes - converted from trigger to procedure

What it does

Manages 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.

Original trigger behaviour

• Fired BEFORE UPDATE on any article update.
• If status changed to 'published' and published_at was NULL, it set published_at to CURRENT_DATE.
• If status changed away from 'published', it set published_at to NULL.
• 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.

Why it was converted to a stored procedure

fn_auto_set_published_at trigger replaced with sp_update_article_status stored procedure.

The 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:

• 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:
• 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:
• The procedure verifies the article exists before attempting the update. Article existence check:
• 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:
• The ELSE IF in the original trigger is a PL/pgSQL syntax error. The procedure uses correct ELSIF and ELSE branching. Syntax fix:

Trigger 6: Block Comments on Invalid Articles

Table	comment
Event	BEFORE INSERT
Revised?	no - original kept as-is

What it does

Prevents 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.

How it works

• Looks up the article using the article_id on the incoming comment row.
• If no matching article is found, raises an exception: 'Cannot comment: article does not exist.'
• If the article status is not 'published', raises an exception including the current status for context.
• If all checks pass, RETURN NEW allows the insert to proceed.

Why this trigger was not changed

This 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.

Summary of Changes

Trigger	Original Form	Revised Form	Key Changes
Trigger 1	Trigger on comment	Trigger on comment	Added null checks, article existence check, WHEN clause
Trigger 2	Trigger on fact_checks	Trigger on fact_checks	Added null checks, article existence check, WHEN clause
Trigger 3	Trigger on article UPDATE	Trigger on UPDATE OF deleted_at	Added null check, targeted column watch
Trigger 4	Trigger on journalist_followers	Stored procedure	Added entity existence checks, duplicate check, null safety
Trigger 5	Trigger on article UPDATE	Stored procedure	Added valid status check, no-op detection, syntax fix
Trigger 6	Trigger on comment	Unchanged	No changes needed