CODEWITHSUNDEEP
postgresqltriggersplpgsqldatabase-trigger
Ricardo Pérez
Ricardo Pérez

Reputation: 215

PostgreSQL trigger prevents another one to work

Suppose we have:

create table T
(
    id bigserial primary key
  , a  int
  , b  int
  , c  int
);

I want c to be always equals to a+b and I want to prevent manual changes of c. So I create two triggers:

-- Prevents manual changes of c:

create or replace function no_changes() returns trigger as $$
begin
    if old.c is distinct from new.c then
        raise exception 'Can''t change c manually.';
    end if;
    return new;
end;
$$ language plpgsql;

create trigger no_changes
before update of c  -- 'before' so prevents changes in 'c'
on T
for each row
execute procedure no_changes();

-- Do c = a + b:

create or replace function change() returns trigger as $$
begin
    update T
    set c = a + b
    where id = new.id;
    return new;
end;
$$ language plpgsql;

create trigger change
after insert or update of a, b  -- 'after' so I'm sure the row is inserted/updated
on T
for each row
execute procedure change();

If I do:

update T set c = 247;

I see the error message "Can't change c manually" and the column doesn't changes. Great.

However, If I do:

insert into T (a, b) values (4, 3);

Or:

update T set a = 3 where id = 2 -- suppose id = 2 exists

Then I get the same error message as before. Obviously, The update command from change trigger fires no_changes trigger preventing the update in the c column.

Any ideas? Thanks!

Upvotes: 3

Views: 75

Answers (1)

Abelisto
Abelisto

Reputation: 15614

The whole logic could be placed in single function:

create or replace function change() returns trigger as $$
begin
    if old.c is distinct from new.c then
        raise exception 'Can''t change c manually.';
    end if;
    new.c := new.a + new.b;
    return new;
end;
$$ language plpgsql;

create trigger change
before insert or update on T
for each row
execute procedure change();

But if you prefer to split the logic, then
1) Create both triggers on before event
2) "If multiple triggers of the same kind are defined for the same event, they will be fired in alphabetical order by name." So name triggers to get the desired order, for example trg1_check to prevent changes of c and trg2_change to calculate c value.

Upvotes: 4

Related Questions