如何编写存储过程以将值插入具有外键关系的两个表中？ [英] How to write a stored procedure to insert values into two tables with a foreign key relationship?
问题描述
我创建了两个表， Employeeinfo 和 Employeerequests 。
表 Employeeinfo 可以有一个唯一的具有列的用户：
Table Employeeinfo can have one unique user with columns:
- id（主键，自动递增）
- 名称
- dep
- 地址
- id (primary key, auto increment)
- name
- dep
- address
和表 Employeerequests 可以针对一个具有列的唯一用户ID进行多个请求
and table Employeerequests can have multiple requests against one unique user ID with columns
- id（主键，自动递增）
- CustomerID（雇员信息的外键（ID列））
- 类别
- 请求。
- id (primary key, auto increment)
- CustomerID(foreign key to Employeeinfo(ID column))
- category
- requests.
现在我要设计一个存储过程这样我就可以同时将值插入两个表中。请帮忙。我对SQL非常陌生。预先感谢。
Now I want to design a stored procedure in such a way so that I can insert values into both tables at the same time. Please help. I am very new to SQL. Thanks in advance.
推荐答案
发表评论的时间过长。
SQL Server仅允许您在单个查询中插入一个表。您大概想同时提供员工和请求信息。因此，对插入的限制是一个真正的问题。
SQL Server only allows you to insert into one table in a single query. You presumably want to provide both employee and request information. So that limitation on insert is a real problem.
您可以绕过限制，方法是创建一个结合了两个表的视图，然后在该视图上定义一个而不是insert 触发器。 文档。
You can get around the limitation by creating a view combining the two table and then defining an instead of insert trigger on the view. This is explained in the documentation.
也就是说，您似乎不具备丰富的SQL知识。因此，我建议仅使用两个单独的语句，每个表一个。如果发现方便，可以将它们包装在存储过程中。
That said, you seem to not have extensive SQL knowledge. So, I would recommend simply using two separate statements, one for each table. You can wrap them in a stored procedure, if you find that convenient.
这篇关于如何编写存储过程以将值插入具有外键关系的两个表中？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
