SQL触发器

目录

什么是触发器

触发器的作用：

触发器的类型：

Inserted和Deleted表

触发器的分类

      “Instead of”触发器

      After触发器

---

什么是触发器

        在了解一个新的知识点的时候，首先需要知道它的定义，那么什么是触发器呢？触发器（trigger）是SQL server 提供给程序员和数据分析员来保证数据完整性的一种方法，它是与表事件相关的特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发，比如当对一个表进行操作（ insert，delete， update）时就会激活它执行。触发器经常用于加强数据的完整性约束和业务规则等。

触发器的作用：

        触发器的主要作用就是其能够实现由主键和外键所不能保证的复杂参照完整性和数据的一致性，它能够对数据库中的相关表进行级联修改，提高比CHECK约束更复杂的的数据完整性，并自定义错误消息。触发器的主要作用主要有以下接个方面：
        1. 强制数据库间的引用完整性
        2. 级联修改数据库中所有相关的表，自动触发其它与之相关的操作
        3. 跟踪变化，撤销或回滚违法操作，防止非法修改数据
        4. 返回自定义的错误消息，约束无法返回信息，而触发器可以
        5. 触发器可以调用更多的存储过程

触发器的类型：

        触发器一般分为前触发器和后触发器 。（还有一个行级触发器，但是在sql server中不存在【行级触发器链接】）

        前触发器：先执行对应语句，然后执行触发器中的语句。

        后触发器：并没有真正的执行触发语句（insert，delete和update），而是执行触发后的语句。

        触发器创建：Create，修改：Alter 删除：Drop

Inserted和Deleted表

        在SQL server中，每个触发器都会创建两个专用的表：Inserted和Deleted表。这两个表示虚拟的表，不存在的表，两个表的结构与触发器的结构相同。在触发器执行完毕之后，两个表也就随之删除了。

• Deleted表存放由于执行Delete或Update语句而要从表中删除的所有行。
• Inserted表存放由于执行Insert或Update语句而要向表中插入的所有行。

  • 逻辑表

    对表的操作	Inserted逻辑表	Deleted逻辑表
    增加记录（insert）	存放增加的记录	无
    删除记录(delete)	无	存放被删除的记录
    修改记录(update)	存放更新后的记录	存放更新前的记录

触发器的分类

        SQL server中提供了两种触发器：“Instead of”和“After”触发器。一个表或视图的每一个修改动作(Insert、Update和Delete)都可以有一个“Instead of” 触发器，一个表的每个修改动作都可以有多个“After”触发器。

      “Instead of”触发器

        此触发器在执行真正的“插入”之前被执行。除表之外，“Instead of” 触发器也可以用于视图，用来扩展视图可以支持的更新操作。也就是说，使用“Instead of"触发器会替代所执行的SQL语句，即SQL语句不执行， 执行的是替代后的语句。

        例如下面的触发器：此触发器在表Catetory内进行删除操作时触发，执行的不是删除操作，而是Begin……End语句块，

ALTER TRIGGER [dbo].[trigCategoryDelete]
   ON [dbo].[category]
   --AFTER DELETE--表示删除之后触发
   instead of DELETE--表示触发器代替了删除操作 
AS 
BEGIN
	--select * from deleted --将删除之后的结果选择出来，放置在一个临时表中
	--delete news where caId=(select * from deleted) 
	declare @caId int
	--在已删除的表中查询内容
	select @caId=id from deleted
	--删除评论
    delete comment where newId in(select newId from news where caId=@caId)
	--删除新闻
	delete news where caId=@caId
	--删除类别
	delete category where id=@caId
END

当在category表中进行删除操作的时候，如下表示并未真正执行如下的操作，而是执行了而是Begin……End语句块中的内容：

delete from category where id='6'

运行结果如下：

（个人理解，希望大家指正）

在如下：

ALTER TRIGGER [dbo].[trigCategoryDelete]
   ON [dbo].[category]
   --AFTER DELETE--表示删除之后触发
   instead of DELETE--表示触发器代替了删除操作 
AS 
BEGIN
	--select * from deleted --将删除之后的结果选择出来，放置在一个临时表中
	--delete news where caId=(select * from deleted) 
	declare @caId int
	select @caId=id from deletedND
End

如果此时在执行上面的语句（delete from category where id ='7'），就会显示如下结果

但是表中的数据并没有删除，很明显说明并未执行删除操作，而是执行的select操作。

      After触发器

        此触发器在insert ，update和Deleted语句执行之后被触发，此触发器只用于表。主要用于在表在修改后修改其他的表。

如下面的触发器：（表示在对category表进行删除操作时候然后进行一个添加项目）

ALTER TRIGGER [dbo].[trigCategoryDelete]
   ON [dbo].[category]
   AFTER DELETE--表示删除之后触发
   --instead of DELETE--表示触发器代替了删除操作 
AS 
BEGIN
	--select * from deleted --将删除之后的结果选择出来，放置在一个临时表中
	--delete news where caId=(select * from deleted) 
	declare @caId int
	Insert into category (name) values('社会体育新闻')
END

        当对category表进行如下删除操作的时候：

delete from category where id='1'

原数据库数据：如下：

此语句的运行结果：

表示删除了id=“1”的数据，在执行触发器之后有添加了一条数据。即运行后的结果显示如下：

很好的验证了此触发器的执行步骤和结果。

        当然，如果SQL语句违反了主键操作，那么SQL语句就会提示如下错误，语句也就不会执行成功，相应After触发器也就 不会被激活（此触发器在SQL语句之后触发， 所以SQL语句没有被执行，触发器也就不会被激活）。

        “Instead of” 触发器可以取代激发它的操作来执行。它在Inserted表和Deleted表刚刚建立，其它任何操作还没有发生时被执行。因为“Instead of” 触发器在约束之前执行，所以它可以对约束进行一些预处理。

注：Inserted表和Deleted表示逻辑上存在的表，物理上不存在的一个表。

参考链接：SQL语句大全

                                         欢迎大家斧正！！！