我在将值插入多个表时遇到问题 [英] I am having problem inserting values into multiple table
本文介绍了我在将值插入多个表时遇到问题的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有两个名为User和User_Privilege的表
i希望使用来自单个webform页面的相同存储过程将值插入这两个表中
这是我的桌子结构
用户:
user_id(pk)
name
地址
contact_no
电子邮件
date_of birth
User_Privilege:
user_id(pk,fk到用户表)
user_type
密码
recover_ans
我写了一个存储过程,但我很困惑这会有效工作
我尝试过:
创建程序Register_User
@user_id varchar(10),
@name varchar(40) ,
@address varchar(40),
@contact_no varchar(15),
@email varchar(40),
@date_of_birth date,
@user_type varchar( 20),
@password varchar(40),
@recover_ans varchar(40)
as begin
insert into [ ClothStockManagement]。[dbo]。[用户]值(@ user_id,@ name,@ address,@ contact_no,@ email,@ date_of_birth)
插入user_privellage值(SCOPE_IDENTITY(),@ user_type,@ password,@ recover_ans)
end
解决方案
我基本同意解决方案1但我会将更新语句包装在过程中的事务中。
这里讨论为什么以及如何实现它...... 管理SQL Server存储过程中的事务 - 4GuysFromRolla.com [ ^ ]
为什么它不能有效工作。如果逻辑是当注册新用户时,两个表都被插入,那么使用来自客户端的单个调用而不是从客户端进行两次单独的插入或两次单独的过程调用来做它当然更好。
但请记住,正如我之前所写,请确保您不要将密码存储为纯文本。
谢谢我修复了一切:)
I have two tables called User and User_Privilege
i want to insert values into both of these tables using a same stored procedure from a single webform page
this is my table structure
User:
user_id(pk)
name
address
contact_no
email
date_of birth
User_Privilege:
user_id(pk, fk to user table)
user_type
password
recover_ans
I have written a stored procedure but i was confused will this work efficiently
What I have tried:
create procedure Register_User @user_id varchar(10), @name varchar(40), @address varchar(40), @contact_no varchar(15), @email varchar(40), @date_of_birth date, @user_type varchar(20), @password varchar(40), @recover_ans varchar(40) as begin insert into [ClothStockManagement].[dbo].[user] values(@user_id,@name,@address,@contact_no,@email,@date_of_birth) insert into user_privellage values(SCOPE_IDENTITY(), @user_type,@password,@recover_ans) end
解决方案
I largely agree with solution 1 but I would wrap the update statements in a transaction within the procedure.
There is a discussion as to why, and how to achieve it, here...Managing Transactions in SQL Server Stored Procedures - 4GuysFromRolla.com[^]
Why wouldn't it work efficiently. If the logic is that when a new user is registered, both tables get inserted, then it's certainly better to do it using a single call from the client side rather that two separate inserts from the client or two separate procedure calls.
But bear in mind, as I wrote earlier, ensure that you don't store passwords as plain text.
Thanks i fixed everything up :)
这篇关于我在将值插入多个表时遇到问题的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文