CODEWITHSUNDEEP
postgresqltriggers
AJcodez
AJcodez

Reputation: 34186

Is there a way to declare postgresql row immutable given a condition?

I'm creating records in a transaction.

begin;
update parent_records where id=5 set closed = now();
insert into child_records ...;
commit;

I want to prevent inserting new child_records once the parent record is closed. It seems like setting a rule on parent_records to blow up on update actions when closed would solve the problem, as the transaction would fail.

I could do the update with where closed is null then check in app code if any rows were updated and rollback, but i would rather the constraint be in the database itself.

How do i mark the parent row immutable (updates fail with error) when a condition is met (closed column not null)?

Upvotes: 5

Views: 3226

Answers (1)

klin
klin

Reputation: 121634

Use a trigger, e.g.:

create function before_update_on_parent_records()
returns trigger language plpgsql as $$
begin
    if old.closed is not null then
        raise exception 'cannot update because the row is closed';
    end if;
    return new;
end $$;

create trigger before_update_on_parent_records
before update on parent_records
for each row execute procedure before_update_on_parent_records();

Upvotes: 9

Related Questions