数据表关系图:
数据库中有三张表,新闻类别表(category),新闻表(news),评论表(comment),News表的categoryId和category表中的ID外键关联,comment表中的NewsId和News表中的Id外键关联。
在sql中执行:
delete from category where Id =1
如果news表和comment表中有和category表中Id=1关联,那么就会提示delete语句和外键关系冲突,执行失败。 解决外键关联实现级联修改数据库相关表,就用到了触发器。
很简单,在要删除的Category表中新建一个触发器,设计一段从下到上逐步删除的SQL语句:
USE [newssystem]
GO
/****** 对象: Trigger [dbo].[trigCatgoryDelete] 脚本日期: 07/26/2012 19:44:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Jesse
-- Create date: 2012-7-26
-- Description:删除类别触发器
-- =============================================
ALTER TRIGGER [dbo].[trigCatgoryDelete]
ON [dbo].[Category]
instead of DELETE
AS
BEGIN
--delete news where categoryid=(select id from deleted)
-- declare @Id int
-- select @Id=id from deleted
-- delete news where categoryId=@id
-- delete category where id=@id
declare @caId int
select @caId =id from deleted
--删除评论
delete comment where newsId in(select newsId from news wherer caId=@caId)
--删除新闻
delete news where categoryId=@caId
--删除类别
delete category where id=@caId
END