Which of the following trigger function definitions correctly achieves this?
hard📝 Application Q15 of 15
PostgreSQL - Triggers in PostgreSQL
You want to create a trigger function that automatically sets a column last_modified to the current timestamp whenever a row is updated. Which of the following trigger function definitions correctly achieves this?
ACREATE FUNCTION set_last_modified() RETURNS trigger AS $$ BEGIN NEW.last_modified := NOW(); RETURN NEW; END; $$ LANGUAGE plpgsql;
BCREATE FUNCTION set_last_modified() RETURNS trigger AS $$ BEGIN OLD.last_modified := NOW(); RETURN OLD; END; $$ LANGUAGE plpgsql;
CCREATE FUNCTION set_last_modified() RETURNS void AS $$ BEGIN NEW.last_modified := NOW(); END; $$ LANGUAGE plpgsql;
DCREATE FUNCTION set_last_modified() RETURNS trigger AS $$ BEGIN NEW.last_modified = NOW(); RETURN NEW; END; $$ LANGUAGE plpgsql;
Step-by-Step Solution
Solution:
Step 1: Identify correct assignment operator in PL/pgSQL
PL/pgSQL uses := for assignment, not =.
Step 2: Check return type and returned row
Trigger functions must return type trigger and return NEW for BEFORE UPDATE triggers.
Step 3: Evaluate each option
CREATE FUNCTION set_last_modified() RETURNS trigger AS $$ BEGIN NEW.last_modified := NOW(); RETURN NEW; END; $$ LANGUAGE plpgsql; uses correct assignment :=, returns NEW, and has correct return type. CREATE FUNCTION set_last_modified() RETURNS trigger AS $$ BEGIN NEW.last_modified = NOW(); RETURN NEW; END; $$ LANGUAGE plpgsql; uses = which is invalid for assignment. CREATE FUNCTION set_last_modified() RETURNS trigger AS $$ BEGIN OLD.last_modified := NOW(); RETURN OLD; END; $$ LANGUAGE plpgsql; assigns to OLD which is read-only. CREATE FUNCTION set_last_modified() RETURNS void AS $$ BEGIN NEW.last_modified := NOW(); END; $$ LANGUAGE plpgsql; returns void which is invalid.
Final Answer:
CREATE FUNCTION set_last_modified() RETURNS trigger AS $$ BEGIN NEW.last_modified := NOW(); RETURN NEW; END; $$ LANGUAGE plpgsql; -> Option A
Quick Check:
Use := for assignment and return NEW [OK]
Quick Trick:Use := for assignment and return NEW in trigger [OK]
Common Mistakes:
Using = instead of := for assignment
Returning OLD instead of NEW
Using void return type for trigger function
Master "Triggers in PostgreSQL" in PostgreSQL
9 interactive learning modes - each teaches the same concept differently
