Thread: Automatic insertion on insertion on another table

Automatic insertion on insertion on another table

From
"Owen Jacobson"
Date:
(Preface: this may be directed to the wrong newsgroup; if so, don't hesitate
to let me know.)

I'm fairly new to postgresql (and databases in general); I understand the
basic concepts behind tables and relations and, to some extent, query
optimization, but I have something of an interesting problem in one of my
projects.

I have a table of users for a website, and a table of groups for those users
to be in.  Membership in a group is tracked in a third table, which simply
refers to the user and group tables.  What I would like to do is have
postgresql automatically insert a row into the membership table when a new
row is inserted into the 'user' table that makes that new user a member of a
group called 'Everyone'.

I assume the correct way to do this is through a trigger on the user table
(AFTER INSERT) but have no experience whatsoever writing triggers.  I'm not
necessarily asking for someone to write it for me, although that would be
most kind, but some resources on how to write it myself would be just as
good.

Regards and thanks,
Owen Jacobson
(ojacobson at mx hyphen deus dot net)

Tables:

CREATE SEQUENCE uidseq START 0 MINVALUE 0;
CREATE TABLE users (
  UID  BIGINT
    PRIMARY KEY
    DEFAULT nextval('uidseq'),
  Name  VARCHAR(80)
    UNIQUE
    NOT NULL,
...other columns, irrelevant...
);

CREATE SEQUENCE gidseq START 0 MINVALUE 0;
CREATE TABLE groups (
  GID  BIGINT
    PRIMARY KEY
    DEFAULT nextval('gidseq'),
  Name  VARCHAR(80)
    UNIQUE
    NOT NULL
);

CREATE TABLE members (
  UID  BIGINT
    NOT NULL
    REFERENCES users (UID)
      ON UPDATE CASCADE
      ON DELETE CASCADE,
  GID  BIGINT
    NOT NULL
    REFERENCES groups (GID)
      ON UPDATE CASCADE
      ON DELETE CASCADE
);



Re: Automatic insertion on insertion on another table

From
Andrew Sullivan
Date:
On Thu, Oct 24, 2002 at 05:36:52PM -0400, Owen Jacobson wrote:

> I assume the correct way to do this is through a trigger on the user table
> (AFTER INSERT) but have no experience whatsoever writing triggers.  I'm not
> necessarily asking for someone to write it for me, although that would be
> most kind, but some resources on how to write it myself would be just as
> good.

That's probably what you want (a rule might also work in this case).
The place to start is

<http://www.ca.postgresql.org/users-lounge/docs/7.2/postgres/triggers.html>

A

--
----
Andrew Sullivan                         204-4141 Yonge Street
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M2P 2A8
                                         +1 416 646 3304 x110