help for the stored procedure

Question

help for the stored procedure

isay

SSC Enthusiast

Points: 192
More actions
March 5, 2007 at 7:38 pm

#119083

Hello,
Can anybody show me how to do this. I have these tblUser and tblProjectName that will keep on updating whenever there are new users and project names. I want in the tblProjectMembers will also update everytime there are new users and project names has been added. UserID must have all the ProjectID in tblProjectmembers in order to have the rights in all projects. Kindly show me how to make a stored procedure for this one. Thanks in advance.
tblUser
UserId
UserName
1
Ab
2
Cd

tblProjectName
ProjectId
ProjName
1
Project1
2
Project2
3
Projcet3

tblProjectMembers
UserId
ProjectId
1
1
1
2
1
3
2
1
2
2
2
3

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004177 More actions · Answer 1

I can't really imagine assigning all users to every new project and vice versa... but, if you must, put a trigger on the User and Project tables that does it for you. Each would basically do a cross-join of the INSERTED table with the opposite table.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

isay SSC Enthusiast Points: 192 More actions · Answer 2

Yes, I need to give access all users to all projects so they can have rights to put all their concern in each projects.

I really appreciate if you can show me how to do it. Thanks.

supriy SSC Enthusiast Points: 198 More actions · Answer 3

Hi,

You can try the following code for insert in the user table. Similarly you can do for the project table.

Here is the code...Hope this helps

create trigger usr_update on tbluser after insert

as

declare

@usr_ident smallint

select @usr_ident = @@identity from inserted

insert

into tblProjectMembers ( userid,projectid) select @usr_ident as userid, projectid from tblProjectName

Regards,

isay SSC Enthusiast Points: 192 More actions · Answer 4

Thanks a lot. It is now working I really appreciate your help. Thanks again.

Jeff Moden SSC Guru Points: 1004177 More actions · Answer 5

Oh, be careful... I could be wrong but I'm thinking that if you have an insert of more than one user, you'll only get one value for @user_ident for that code... it would be better to use the INSERTED table as part of the INSERT in the code above.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)