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 по дате отправления: