使用gridview删除命令从多个表中删除
我有一个利用 sql 数据源的网格视图。现在我想使用网格视图的删除命令删除一条记录，问题是单个记录基于从多个表收集的信息，所以我必须从所有这些表中删除部分信息才能完全删除该记录，这是我的表结构。
- 项目
- ProjectArticles
- ArticleStatus
- ProjectAssignments
Projects 是一个表，其中包含有关项目的完整信息，例如项目标题、客户名称等，它具有主键“project_id”
ProjectArticles 是一个表，其中包含有关与一个项目关联的文章数的信息，例如，如果一个项目有 3 篇文章，则该表有 3 行，其中包含以下数据
article_id project_id
1 --------- 1
2 --------- 1
3 --------- 1
，其中“article_id”是主键。
文章状态是一个表，其中包含有关一篇文章的信息，例如
status_id- article_id- filename ---- writer_status- editor_status- sales_status
1 -------- 1 --------- Any filename -- done --------- pending ------ pending
“status_id”是主键
最后 ProjectAssignments 是一个表，其中包含有关将哪个项目分配给哪个作者的数据，它以 assignment_id 作为主键，并使用 project_id 作为外键，类似于上面所示。
有什么想法如何删除包含其他表中所有相关信息的完整项目吗？
我已经做了这个示例查询，但它不起作用，sql Management Studio 说“无法解析查询文本”。
DELETE P, A FROM Projects AS P, ProjectArticles AS A WHERE P.project_id = A.project_id AND P.project_id = @project_id
在上面的查询文本中，我只是使用了两个表来检查它是否工作，但它不工作，任何帮助将不胜感激。
I have a grid view utilizing sql data source. Now I want to delete a record using grid view's delete command, the problem is that a single record is based on information collected from multiple tables, so I have to delete parts of information from all those tables to completely remove the record, here is my table structure.
- Projects
- ProjectArticles
- ArticleStatus
- ProjectAssignments
Projects is a table with complete information about projects like, project title, client name etc, it has primary key "project_id"
ProjectArticles is a table that has information about how many articles are associated with one project for example if a project has 3 articles then this table has 3 rows with the following data
article_id project_id
1 --------- 1
2 --------- 1
3 --------- 1
where "article_id" is the primary key.
Article status is a table with information regarding one article such as
status_id- article_id- filename ---- writer_status- editor_status- sales_status
1 -------- 1 --------- Any filename -- done --------- pending ------ pending
"status_id" is primary key
And finally ProjectAssignments is a table with data about which project is assigned to which writer it has assignment_id as primary key and using project_id as foreign key similar as shown above.
Any ideas how can I delete a complete project with all the relevant information in other tables ?
I have made this example query but it is not working, sql management studio saying "unable to parse query text".
DELETE P, A FROM Projects AS P, ProjectArticles AS A WHERE P.project_id = A.project_id AND P.project_id = @project_id
In the query text above I just used two tables to check if its working or not but its not working, any help will be appreciated.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
当您想要删除与用作外键的特定主键相关的所有行时，级联非常有用
在参考表中。
例如：- 主表或子表
Tab1(Id int (主键) ,名称 varchar(10))
子表
Tab2(Class int, Id int (refrenctial key of Tab1(Id),address varchar(10))
现在，当您想从记录中删除 1 个数字 id 时，“ON DELETE CASCADE”是最好的，但当您只想删除一个时
记录在子表中，则事务就好了。
Cascade is useful when you want to Delete all the row related to that Perticular primary key which is used in as foreign key
in Referenctial table.
Eg :- Primary or Parant Table
Tab1(Id int (primary key) ,Name varchar(10))
Child Table
Tab2(Class int, Id int (refrenctial key of Tab1(Id),address varchar(10))
now when You want to delete 1 number id from your record then "ON DELETE CASCADE" is best but when you only want to delete one
Record in Child Table then the Transaction is good.
有几种方法可以解决这个问题。
您可以在 SQL Server 中声明约束并设置 Cascade。当您根据所使用的 SQL 服务器进行删除时，您可以
从 P where 条件级联删除，这将告诉 SQL 服务器进行删除，然后按照所有引用向下级联此删除。如果内存中包含所有内容，通常您可以在 DAL 中设置这样的条件，如果 DAL 支持级联，则在 DAL 中设置级联。风险在于，您会遇到并发问题，并且会在最终确定加载成功之前将所有可能引用的数据加载到内存中。
请注意，级联通常是关闭的，因为如果出现问题，它可能会变成严重的问题。许多人更愿意编写一个结论性查询，将您的条件按顺序删除作为事务。
这样你就知道将要发生什么，并且可以控制它，而不会陷入循环。
There are a few ways to attack this.
You can declare your constraints in the SQL server and set Cascade on. When you delete depending on the SQL server you are using you can
Delete from P where condition Cascadethis will tell the SQL server to delete and then follow all references to cascade this delete down.Often you can set such a condition in a DAL if you have everything in memory though, setting cascade on in the DAL if it supports it. Risk is that you have a concurrency issue and an issue to load all possible referenced data in memory before you know conclusively that it will succeed.
Note that cascade is usually turned off because it can turn into a serious problem if you get something wrong. Many people would prefer to write a conclusive query that deletes using your condition in order as a transaction.
That way you know what is about to occur and you can control it without ending up with cycles.
首先我想知道你想删除什么。
(1) 是否要删除单个[项目]表的所有详细信息。
如果是，那么这不是一个大问题，因为我认为您已经维护了主键和外键，您只需更改外键约束即可
通过“ON DELETE CASCADE”，项目（project_Id）的所有信息都将被删除。
(2) 如果您想像您提到的那样删除单个状态
“文章状态是一个表，其中包含有关一篇文章的信息，例如
status_id-article_id- filename ---- writer_status- editor_status- sales_status
1 -------- 1 --------- 任何文件名 --- 完成 --------- 待处理 ------ 待处理 "
那么你只需要删除“ArticleStatus”的一个表行，
如果你想知道关于“级联删除”然后告诉我
First i Want to know that what do u want to delete.
(1) Do U Want to delete all the detail of the single [Project] Table.
If yes then its not a big issue because i think that you already have maintain the Primary and Foreign Key You Just have to do is just alter your foreign key constraint with
"ON DELETE CASCADE" by this all the information of the Project(project_Id) will be deleted.
(2) If You want to delete single status like you mentioned
" Article status is a table with information regarding one article such as
status_id- article_id- filename ---- writer_status- editor_status- sales_status
1 -------- 1 --------- Any filename -- done --------- pending ------ pending "
Then you just need to delete one table row of "ArticleStatus"
If you want to know about the "ON CASCADE DELETE" then tell me