Thread: pgsql problem
Hello,
I use postgresql 7.3.2
This is the code to show my problem :
-3 tables
-1 trigger
-2 stocked procedures
-1 view
-----------
DROP TABLE USERS CASCADE;
CREATE TABLE USERS (
U_ID SERIAL not null,
NAME VARCHAR(30) not null,
constraint PK_USERS primary key (u_id));
DROP TABLE PERMISSIONS CASCADE;
CREATE TABLE PERMISSIONS (
P_ID SERIAL not null,
NAME VARCHAR(30) not null,
constraint PK_PERM primary key (p_id));
DROP TABLE USER_PERM;
CREATE TABLE USER_PERM (
U_ID INT4 not null,
P_ID INT4 not null,
STATE BOOL not null default true,
constraint PK_USER_PERM primary key (U_ID, P_ID),
constraint FK_USER_PERM_USER foreign key (U_ID)
references USERS (U_ID)
on delete cascade on update restrict,
constraint FK_USER_PERM_PERM foreign key (P_ID)
references PERMISSIONS (P_ID)
on delete cascade on update restrict);
INSERT INTO permissions(name) VALUES('delete');
INSERT INTO permissions(name) VALUES('update');
INSERT INTO permissions(name) VALUES('create');
DROP TRIGGER OnCreateUser ON users;
DROP FUNCTION CreateUserTrig();
CREATE FUNCTION CreateUserTrig () RETURNS TRIGGER AS '
DECLARE
BEGIN
RAISE NOTICE ''TRIGGER'';
INSERT INTO user_perm(u_id, p_id)
(SELECT u_id, p_id FROM users, permissions
WHERE u_id = NEW.u_id);
RETURN NEW;
END;
' LANGUAGE 'plpgsql';
CREATE TRIGGER OnCreateUser
AFTER INSERT
ON users FOR EACH ROW
EXECUTE PROCEDURE CreateUserTrig();
CREATE OR REPLACE FUNCTION CreateUser(text, bool, bool, bool) RETURNS int AS
'
DECLARE
Vname ALIAS FOR $1;
Vcreate_state ALIAS FOR $2;
Vupdate_state ALIAS FOR $3;
Vdelete_state ALIAS FOR $4;
tmp record;
BEGIN
INSERT INTO users(name) VALUES(Vname);
RAISE NOTICE ''Begin Of Update Permissions'';
UPDATE user_perm SET
state = Vcreate_state
WHERE u_id = currval(''users_u_id_seq'')
AND p_id = (SELECT p_id FROM permissions WHERE name = ''create'');
UPDATE user_perm SET
state = Vupdate_state
WHERE u_id = currval(''users_u_id_seq'')
AND p_id = (SELECT p_id FROM permissions WHERE name = ''update'');
UPDATE user_perm SET
state = Vdelete_state
WHERE u_id = currval(''users_u_id_seq'')
AND p_id = (SELECT p_id FROM permissions WHERE name = ''delete'');
RAISE NOTICE ''End Of Update Permissions'';
RETURN 0;
END;
' LANGUAGE 'plpgsql';
CREATE OR REPLACE VIEW ShowUserPerms AS
SELECT
U.u_id,
U.name,
P.p_id,
P.name AS "permission",
UP.state
FROM users AS U, user_perm AS UP, permissions AS P
WHERE UP.u_id = U.u_id AND P.p_id = UP.p_id ORDER BY name;
------------
test=# SELECT createuser('toto', true, true, true);
NOTICE: Begin Of Update Permissions
NOTICE: End Of Update Permissions
NOTICE: TRIGGERcreateuser
------------ 0
(1 row)
test=#
My problem is that the trigger for the insert is executed at the end of the
procedure, so I can't do the update of the user's permissions.
For me and my co-workers, the NOTICE TRIGGER should be before Begin Of
Update Permissions.
Could you explain to me how is it working ?
--
GRIGNON Etienne
Epitech Promo 2005
egrignon@egrignon.com
http://www.egrignon.com
http://www.hans-zimmer.com
> > CREATE TRIGGER OnCreateUser > AFTER INSERT > ON users FOR EACH ROW > EXECUTE PROCEDURE CreateUserTrig(); > Have you thought of using BEFORE INSERT instead of AFTER INSERT? Regards, Christoph
> > > > CREATE TRIGGER OnCreateUser > > AFTER INSERT > > ON users FOR EACH ROW > > EXECUTE PROCEDURE CreateUserTrig(); > > > Have you thought of using BEFORE INSERT instead of AFTER INSERT? Well, no, but I want to do it only after the insert because I will use the primary key to insert it in an other table, so I have to be sure that it has been inserted. Could you explain to me why it doesn't work ? -- GRIGNON Etienne Epitech Promo 2005 egrignon@egrignon.com http://www.egrignon.com http://www.hans-zimmer.com
On Wed, 26 Feb 2003, Grignon Etienne wrote:
>
> test=# SELECT createuser('toto', true, true, true);
> NOTICE: Begin Of Update Permissions
> NOTICE: End Of Update Permissions
> NOTICE: TRIGGER
> createuser
> ------------
> 0
> (1 row)
> test=#
>
>
> My problem is that the trigger for the insert is executed at the end of the
> procedure, so I can't do the update of the user's permissions.
>
> For me and my co-workers, the NOTICE TRIGGER should be before Begin Of
> Update Permissions.
>
> Could you explain to me how is it working ?
Right now AFAIR after triggers run at the end of the containing statement
(in this case the select of createuser) which is what causes the behavior
you're seeing. I'm not sure of a good work around, off hand, for your
case though.
* Grignon Etienne <egrignon@egrignon.com> [27.02.2003 23:04]: > > > > > > > > CREATE TRIGGER OnCreateUser > > > AFTER INSERT > > > ON users FOR EACH ROW > > > EXECUTE PROCEDURE CreateUserTrig(); > > > > > Have you thought of using BEFORE INSERT instead of AFTER INSERT? > > > Well, no, but I want to do it only after the insert because I will use the > primary key to insert it in an other table, so I have to be sure that it has > been inserted. > Could you explain to me why it doesn't work ? > It seems to me, that trigger will see a newly created row in case it is BEFORE INSERT trigger. I've had a case, when I was inserting data into other tables using the primary key - everything was fine. Just try a BEFORE INSERT trigger. -- Victor Yegorov
> > > > > > > CREATE TRIGGER OnCreateUser > > > AFTER INSERT > > > ON users FOR EACH ROW > > > EXECUTE PROCEDURE CreateUserTrig(); > > > > > Have you thought of using BEFORE INSERT instead of AFTER INSERT? > > > Well, no, but I want to do it only after the insert because I will use the > primary key to insert it in an other table, so I have to be sure that it has > been inserted. > Could you explain to me why it doesn't work ? > Well, I think Stephan did: Right now AFAIR after triggers run at the end of the containing statement (in this case the select of createuser) which is what causes the behavior you're seeing. I'm not sure of a good work around, off hand, for your case though. So, what about just skipping the trigger and move the trigger function to CreateUser? Regards, Christoph