sql语句如何删除有主外键关系的数据。
举个例子,比如一个新闻发布系统,有类别表,新闻表和评论表,分别设立主外键关系。
如图:
两种方法,第一种很简单,当在创建主外键时,如图所示:
类别表和新闻表,新闻表和评论表都要设为层叠。
第二种就是写触发器了!
这是类别表的触发器:
set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go -- ============================================= -- Author: <zxj> -- Create date: <2012> -- Description: <删除类触发器> -- ============================================= ALTER TRIGGER [trigCategoryDelete] ON [dbo].[cateGory] instead of DELETE AS BEGIN --select * from deleted declare @caid int select @caid=id from deleted --delete news where caId=(select id from deleted) --select * from comment where newsid in(select id from news where caid=@caid) --删除评论 delete comment where newsID in (select Id from news where caid=@caid) --删除新闻 delete news where caId=@caid --删除类别 delete category where id=@caid END