SQL 触发器

触发器：当发生某一个动作（增、删、改）将会触发的代码

什么时候需要：比如删除某列的时候，可能因为是别的表的外键而删除失败

 1 /*删除新闻类别后的动作*/
 2 ALTER TRIGGER [dbo].[trigCategoryDelete]
 3     ON [dbo].[category]
 4     AFTER DELETE
 5 AS
 6 BEGIN
 7     ......
 8 END
 9 
10 /*删除新闻类别以及类别下的全部新闻*/
11 ALTER TRIGGER [dbo].[trigCategoryDelete]
12     ON [dbo].[category]
13     instead of DELETE
14 AS
15 BEGIN
16     declare @id int
17     select @id=id from deleted
18     delete news where caId=@id
19     delete category where id=@id        
20 END

/*删除新闻类别以及类别下的全部新闻以及新闻的评论*/
 ALTER TRIGGER [dbo].[trigCategoryDelete]
    ON [dbo].[category]
     instead of DELETE
 AS
 BEGIN
     declare @caId int
     select @caId=id from deleted
     delete comment where newsId in (select newsId from news where caId=@caId)
     delete news where caId=@id
     delete category where id=@id        
 END

posted on 2015-01-27 21:44 aoun 阅读(172) 评论(0) 收藏举报