You are viewing a free preview of this lesson.
Subscribe to unlock all 10 lessons in this course and every other course on LearningBro.
A trigger is a function that PostgreSQL calls automatically when a specified event (INSERT, UPDATE, DELETE, or TRUNCATE) occurs on a table. Triggers are written in PL/pgSQL and are one of the most powerful features of PostgreSQL.
TRIGGERCREATE OR REPLACE FUNCTION function_name()
RETURNS TRIGGER
LANGUAGE plpgsql AS $$
BEGIN
-- use NEW and OLD records
RETURN NEW; -- or RETURN OLD, or RETURN NULL
END;
$$;
Trigger functions take no parameters and return TRIGGER.
| Variable | Available on | Description |
|---|---|---|
NEW | INSERT, UPDATE | The new row being inserted or updated |
OLD | UPDATE, DELETE | The old row before the change |
TG_OP | All | Operation: 'INSERT', 'UPDATE', 'DELETE' |
TG_TABLE_NAME | All | Name of the table that fired the trigger |
TG_WHEN | All | 'BEFORE' or 'AFTER' |
CREATE TRIGGER trigger_name
{ BEFORE | AFTER | INSTEAD OF }
{ INSERT | UPDATE | DELETE } [ OR ... ]
ON table_name
[ FOR EACH ROW | FOR EACH STATEMENT ]
EXECUTE FUNCTION trigger_function_name();
A common use case — automatically update a updated_at column:
CREATE OR REPLACE FUNCTION set_updated_at()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
NEW.updated_at := NOW();
RETURN NEW;
END;
$$;
CREATE TRIGGER trg_employees_updated_at
BEFORE UPDATE ON employees
FOR EACH ROW
EXECUTE FUNCTION set_updated_at();
Now every UPDATE on employees automatically sets updated_at.
CREATE OR REPLACE FUNCTION audit_employee_changes()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
IF TG_OP = 'DELETE' THEN
INSERT INTO employee_audit(employee_id, action, changed_at)
VALUES (OLD.id, 'DELETE', NOW());
RETURN OLD;
ELSIF TG_OP = 'UPDATE' THEN
INSERT INTO employee_audit(employee_id, action, changed_at)
VALUES (NEW.id, 'UPDATE', NOW());
RETURN NEW;
END IF;
RETURN NEW;
END;
$$;
CREATE TRIGGER trg_employee_audit
AFTER INSERT OR UPDATE OR DELETE ON employees
FOR EACH ROW EXECUTE FUNCTION audit_employee_changes();
| Type | Runs | Can modify NEW? |
|---|---|---|
| BEFORE | Before the operation | Yes — return the modified NEW |
| AFTER | After the operation | No — data already written |
Subscribe to continue reading
Get full access to this lesson and all 10 lessons in this course.