Thread: Trigger problem

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


Any idea?


. Vladimir Manic, dipl. ing
. manix@eunet.yu
. manix@pakom.co.yu
. ICQ:23561543

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