Triggers & FunctionsIntermediate
1Question 1 of 1
sqlHow do PL/pgSQL Triggers and Trigger Functions work in PostgreSQL?
Explanation & Concept
In PostgreSQL, creating a trigger is a two-step process: 1. Write a reusable Trigger Function that returns type `trigger` and uses special internal variables like `NEW`, `OLD`, and `TG_OP`. 2. Bind the trigger to a table using `CREATE TRIGGER`, specifying the execution timing (`BEFORE` or `AFTER`) and scope (`FOR EACH ROW` or `FOR EACH STATEMENT`).
Code Example
sql
-- 1. Define the trigger function
CREATE OR REPLACE FUNCTION update_timestamp_column()
RETURNS TRIGGER AS $$
BEGIN
NEW.updated_at = CURRENT_TIMESTAMP;
RETURN NEW; -- For BEFORE triggers, returning NEW applies modifications
END;
$$ LANGUAGE plpgsql;
-- 2. Bind the trigger to the table
CREATE TRIGGER set_timestamp
BEFORE UPDATE ON users
FOR EACH ROW
EXECUTE FUNCTION update_timestamp_column();How It Works & Key Takeaways
A `BEFORE UPDATE` trigger function intercepts the incoming `NEW` record, attaches the current server timestamp to `updated_at`, and returns it so Postgres writes the updated timestamp automatically.
Mock Test · 1 Questions · Optional
Ready for Partitioning?
Take a quick test to evaluate yourself, or move straight to the next module.
Intermediate1 questions