Re: Trigger problem

Vladimir Manic wrote:

> I have table anda data like this:
>
> CREATE TABLE "aa" (
> "id" serial,
> "d" bool DEFAULT 'f'::bool NOT NULL,
> "nid" int4
> );
>
> INSERT INTO "aa" ("id", "d", "nid") VALUES(1, 'f', 1);
> INSERT INTO "aa" ("id", "d", "nid") VALUES(2, 'f', 1);
> INSERT INTO "aa" ("id", "d", "nid") VALUES(3, 'f', 2);
> INSERT INTO "aa" ("id", "d", "nid") VALUES(4, 't', 1);
>
> My goal is:
> when I update record id=2 and set d='t',
> I want to set all other records field d to 'f' where field nid contains
the same
> value as updated record.
> In this situation I should set d to 'f' in record id=4

Something like:

create function clear_d() returns trigger
language plpgsql
as '
begin
if new.d then
update aa set d = false where nid = new.nid and d;
end if;
return new;
end
';

create trigger aa_before_insupd
before insert or update on aa
for each row
execute procedure clear_d();

By making the trigger execute before insert as well as before update,
inserting a new record with d=true will also set d=false for any other
records with the same nid.

--
Peter Gibbs
EmKel Systems