BEFORE row-level trigger functions must return either the modified row (NEW or OLD) or NULL to proceed. This error occurs when a trigger function fails to return a row value explicitly, preventing the database operation from completing.
The "trigger function did not return a row" error occurs when a BEFORE row-level trigger is executed and the trigger function does not return a proper row value. In PostgreSQL, BEFORE INSERT and UPDATE triggers must return the NEW row (potentially modified), while BEFORE DELETE triggers must return the OLD row. Failing to return a row value prevents PostgreSQL from completing the triggering DML operation (INSERT, UPDATE, or DELETE). This is a structural requirement of trigger function design that ensures data integrity by forcing developers to explicitly decide whether to allow or modify the operation.
Check that your trigger function is declared with the correct return type. All trigger functions must return TRIGGER:
CREATE OR REPLACE FUNCTION my_trigger_function()
RETURNS TRIGGER AS $$
-- Function body here
$$ LANGUAGE plpgsql;If the return type is anything other than TRIGGER, the database will reject the trigger creation or execution.
For BEFORE triggers on INSERT or UPDATE operations, explicitly return the NEW row at the end of the function:
CREATE OR REPLACE FUNCTION validate_user_insert()
RETURNS TRIGGER AS $$
BEGIN
-- Validate or modify the NEW row
IF NEW.email IS NULL THEN
RAISE EXCEPTION 'Email cannot be null';
END IF;
-- Always return NEW for INSERT/UPDATE
RETURN NEW;
END;
$$ LANGUAGE plpgsql;The RETURN NEW statement must be reachable from all code paths in the function.
For BEFORE triggers on DELETE operations, explicitly return the OLD row:
CREATE OR REPLACE FUNCTION audit_user_delete()
RETURNS TRIGGER AS $$
BEGIN
-- Log the deletion
INSERT INTO audit_log (action, user_id, old_data)
VALUES ('DELETE', OLD.id, row_to_json(OLD));
-- Always return OLD for DELETE
RETURN OLD;
END;
$$ LANGUAGE plpgsql;Note that NEW is NULL in DELETE triggers, so returning NEW would be incorrect.
If your trigger has conditional branches (IF/ELSE, CASE), ensure every code path includes a RETURN statement:
-- INCORRECT - missing RETURN in one branch
CREATE OR REPLACE FUNCTION bad_trigger()
RETURNS TRIGGER AS $$
BEGIN
IF NEW.status = 'active' THEN
RETURN NEW;
ELSE
-- Missing RETURN here!
END IF;
END;
$$ LANGUAGE plpgsql;
-- CORRECT - all paths return
CREATE OR REPLACE FUNCTION good_trigger()
RETURNS TRIGGER AS $$
BEGIN
IF NEW.status = 'active' THEN
NEW.activated_at := NOW();
RETURN NEW;
ELSE
RAISE EXCEPTION 'Status must be active';
END IF;
END;
$$ LANGUAGE plpgsql;Use RAISE EXCEPTION to reject operations instead of letting them fall through without a RETURN.
Note that AFTER row-level triggers and statement-level triggers (for both BEFORE and AFTER) can return NULL or are not required to return a row at all:
-- AFTER trigger - return value is ignored
CREATE OR REPLACE FUNCTION notify_on_update()
RETURNS TRIGGER AS $$
BEGIN
PERFORM pg_notify('user_updated', json_build_object('id', NEW.id)::text);
RETURN NULL; -- Return value ignored for AFTER triggers
END;
$$ LANGUAGE plpgsql;
-- Statement-level trigger - return NULL
CREATE OR REPLACE FUNCTION log_bulk_update()
RETURNS TRIGGER AS $$
BEGIN
INSERT INTO audit_log (action) VALUES ('bulk_update');
RETURN NULL; -- Required for statement-level triggers
END;
$$ LANGUAGE plpgsql;This error specifically occurs with BEFORE row-level triggers, not AFTER or statement-level triggers.
In complex trigger scenarios, you may want to allow NULL returns from BEFORE triggers to conditionally skip operations. Returning NULL from a BEFORE trigger effectively cancels the DML operation for that specific row (while subsequent triggers are skipped and the INSERT/UPDATE/DELETE does not happen). This is useful for soft deletes or conditional updates:
CREATE OR REPLACE FUNCTION soft_delete_user()
RETURNS TRIGGER AS $$
BEGIN
IF TG_OP = 'DELETE' THEN
UPDATE users SET deleted_at = NOW() WHERE id = OLD.id;
RETURN NULL; -- Cancel the actual DELETE
END IF;
RETURN OLD;
END;
$$ LANGUAGE plpgsql;Also note that trigger execution order matters when multiple BEFORE triggers exist on the same tableβthey execute alphabetically by function name, with each trigger's output becoming the next trigger's input. Always use trigger names that reflect execution order or dependencies.
insufficient columns in unique constraint for partition key
How to fix "insufficient columns in unique constraint for partition key" in PostgreSQL
ERROR 42501: must be owner of table
How to fix "must be owner of table" in PostgreSQL
trigger cannot change partition destination
How to fix "Trigger cannot change partition destination" in PostgreSQL
SSL error: certificate does not match host name
SSL error: certificate does not match host name in PostgreSQL
No SSL connection
No SSL connection to PostgreSQL