SQL Server 触发器

来源：互联网发布：lg v20 跳过网络验证编辑：程序博客网时间：2024/06/10 04:15

在学习牛腩的时候我第一次正式在SQL Server 实现了触发器，他与存储过程很相似，可以说触发器是一种特殊类型的存储过程，但触发器主要是通过事件进行触发被自动调用执行的，而存储过程可以通过存储过程的名称被调用。

1，创建触发器

<span style="font-size:18px;">-- ================================================-- Template generated from Template Explorer using:-- Create Trigger (New Menu).SQL---- Use the Specify Values for Template Parameters -- command (Ctrl-Shift-M) to fill in the parameter -- values below.---- See additional Create Trigger templates for more-- examples of different Trigger statements.---- This block of comments will not be included in-- the definition of the function.-- ================================================SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================-- Author:<Author,,Name>-- Create date: <Create Date,,>-- Description:<Description,,>-- =============================================CREATE TRIGGER trigCategoryDelete   ON  category   instead of DELETEAS BEGINdeclare @caID intselect @caID=id from deleted--删除评论delete comment where newsId in (select newsId from news where caID =@caID )--删除新闻delete news where caID=@caID --删除新闻类别delete category where id=@caID ENDGO</span>

2，修改触发器

<span style="font-size:18px;">USE [newssystem]GO/****** Object:  Trigger [dbo].[trigCategoryDelete]    Script Date: 06/10/2015 16:48:45 ******/SET ANSI_NULLS ONGOSETQUOTED_IDENTIFIER ONGO--=============================================--Author:                tsj-- Create date: 2015年6月9日16:40:00--Description:        删除类别触发器--=============================================ALTER TRIGGER[dbo].[trigCategoryDelete]   ON [dbo].[category]   AFTER deleteAS BEGIN--select* from deleteddeclare @caId int ---定义变量select @caId=id from deleted -----从 deleted中取出id--删除评论deletecomment where newsId=(select newsId from news where caID=@caId) --删除新闻deletenews where caID=@caID --删除类别deletecategory where id=@caID  END</span>

3，删除触发器：

<span style="font-size:18px;">drop trigger trigger_name </span>

1 0