I have tree tables in a database: users (user_id (auto increment), fname, lname), roles (role_id, role_desc) and users_roles (user_id, role_id). What I'd like to do is to have a function create_user_with_role. The function takes 3 arguments: first name, last name and role_id. The function inserts a new row into the users table and a new user_id is created automatically. Now I want to insert a new record to the users_roles table: user_id is the newly created value and the role_id is taken from the function's arguments list. Is it possible to pass the role_id argument to an after insert trigger (defined on users table) so another automatic insert can be performed? Or can you suggest any other solution?
2 Answers
First @Pavel Stehule is right:
Don't try to pass parameters to triggers, ever!
Second, you just have to get the inserted id into a variable.
CREATE FUNCTION create_user_with_role(first_name text, last_name text, new_role_id integer)
RETURNS VOID AS $$
DECLARE
new_user_id integer;
BEGIN
INSERT INTO users (fname, lname) VALUES (first_name, last_name)
RETURNING id INTO new_user_id;
INSERT INTO users_roles (user_id, role_id)
VALUES (new_user_id, new_role_id);
END;$$ LANGUAGE plpgsql;
Obviously, this is completely inefficient if you want to insert multiple rows but that's another question ;)
1 Comment
Jakub Małecki
This is what I was looking for, thank You. Regarding passing arguments to triggers - it was a question rather than forcing a solution. But thanks for your answers, Dan and Pavel. The application design is that only one user will be created at a time, so there are no efficiency issues mentioned by Dan.
When you need to pass any parameter to trigger, then there is clean, so your design is wrong. Usually triggers should to have check or audit functionality. Not more. You can use a function, and call function directly from your application. Don't try to pass parameters to triggers, ever! Another bad sign are artificial columns in table used just only for trigger parametrization. This is pretty bad design!
2 Comments
Jakub Małecki
The design is OK, I'm not using any artificial columns. There is just many-to-many relationship between users and roles tables which is implemented via the third table - users_roles. My goal is to create a new user for which I know his/her role at the moment of creation. But, since the user_id is an autoincrement field I don't specify it directly, the database engine will handle it. The problem is that I want to use the newly created user_id value in order to insert another record into the users_roles table. And I want it to happen automatically. That's what the function is for.
Pavel Stehule
you should to push your code to function, not to trigger.
CREATE FUNCTION create_user_with_role (first_name, last_name, new_role_id) AS $$ BEGIN INSERT INTO users (fname, lname) VALUES (first_name, last_name); INSERT INTO users_roles (user_id, role_id) VALUES (the newly generated user_id, new_role_id); END; $$I'm writing on a smartphone, sorry for the formatting. Hope it's clearer now.