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