Automatic insertion on insertion on another table

Поиск
Список
Период
Сортировка
От Owen Jacobson
Тема Automatic insertion on insertion on another table
Дата
Msg-id NEZt9.7748$Zy4.1225562@news20.bellglobal.com
обсуждение исходный текст
Ответы Re: Automatic insertion on insertion on another table
Список pgsql-general
(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
);



В списке pgsql-general по дате отправления:

Предыдущее
От: marco ghidinelli
Дата:
Сообщение: cannot add "not null" to an existing table
Следующее
От: terry@greatgulfhomes.com
Дата:
Сообщение: Using the IN clauise