SQLServer数据库之触发器如何创建

前言：更新了这么多关于数据库的文章，数据库基础篇就差不多快要接近尾声了，今天给大家带来的这篇课程是有关于数据库触发器的创建，以及数据库触发器如何使用，和数据库触发器的作用的一个总结。

本篇文章一共会讲到三个重要点，分别为：

1.数据库的概念

• 定义

触发器（Trigger）是针对某个表或视图所编写的特殊存储过程，它不能被显式地调用，而是当该表或视图中的数据发生添加INSERT、更新UPDATE或删除DELETE等事件时自动被执行。主要作用是实现由主键和外键所不能保证的复杂的参照完整性和数据一致性。

• 功能

触发器可以用来对表实施复杂的完整性约束，保持数据的一致性。当触发器所保护的数据发生改变时，触发器会自动被激活，执行某种的操作，从而保证对数据的不完整性约束或不正确的修改不会执行。

触发器可以查询其他表，同时也可以执行复杂的T-SQL语句。触发器执行的命令被当作一次事务处理，因此就具备了事务的所有特征。如果发现引起触发器执行的T-SQL语句执行失败，就会回滚该事务。

触发器的分类：

SQL Server 包括两大类触发器：DML 触发器和 DDL 触发器。

• DML 触发器

当数据库中发生数据操作语言 (DML) 事件时将调用 DML 触发器。DML 事件包括在指定表或视图中修改数据的 INSERT 语句、UPDATE 语句或 DELETE 语句。

• AFTER 触发器 ：又称后置触发器（可使用for），在执行了 INSERT、UPDATE 或 DELETE 语句操作之后执行 AFTER 触发器。
• INSTEAD OF 触发器：又称前置触发器，INSTEAD OF 触发器用以代替通常的触发动作。还可为带有一个或多个基表的视图定义 INSTEAD OF 触发器，而这些触发器能够扩展视图可支持的更新类型。
  • DDL 触发器

  为响应多种数据定义语言 (DDL) 语句而激发。这些语句主要是以 CREATE、ALTER 和 DROP 开头的语句。DDL 触发器可用于管理任务，例如审核和控制数据库操作。

  • CLR 触发器

  CLR 触发器可以是DML触发器，也可以是DDl触发器,将执行在托管代码（在 .NET Framework 中创建并在 SQL Server 中上载的程序集的成员）中编写的方法，而不用执行 Transact-SQL 存储过程。

2.DML触发器的工作原理

触发器语句中使用了两种特殊的表：DELETED和INSERTED，由SQL Server自动创建和管理这两张表，在触发执行时存在，在触发结束时消失。可以使用这两个临时的驻留内存的表测试某些数据修改的效果及设置触发器操作的条件；然而，不能直接对表中的数据进行更改。IT技术

插入数据时，被插入的数据会写入Inserted表，deleted表为空；

更新数据时，更新前的数据会写入deleted表，更新后的新数据（整行，含未修改的列数据）或写入inserted表。

删除数据时，被删除的数据会写入deleted表，inserted表为空。IT技术网站

1 后置触发器的工作原理

dml触发器

注意：后置触发器将在DML语句执行之后再触发

2 前置触发器的工作原理

前置触发器的工作原理

注意：前置触发器将替代DML语句的实际操作，拦截DML的操作。

3.DML触发器如何使用

1 创建触发器

一般形式：

CREATE TRIGGER 触发器

ON 表名

Instead of （前置）/（后置）for 或 after [ update,insert,delete ]

AS SQL语句

2 修改或删除触发器IT技术论坛

可使用Alter trigger 或Drop trigger修改或删除触发器。

3 触发

触发器的触发有相应事件触发，不能单独调用。IT技术包含哪些技术

【例1】 创建后置触发器：当向schoolInfo表插入数据后，如果areaId无效(在areaInfo表不存在)的，则拒绝插入，提示错误信息。IT技术学习

create trigger SchoolInfo_After_Insert
 on schoolInfo after insert
as
begin
 if not exists(select * from areaInfo where areaId in (select areaId from inserted))
 begin
 print '后置触发器：插入数据的areaId是无效的，本次操作没有成功！'
 rollback –撤销插入
 end
 else
 print '后置触发器：插入成功！'
end

测试(先删除外键约束)：如何学好IT技术

insert into schoolInfo(schId,areaId,schName)
 values('12321','1000','aaa')
select * from schoolInfo where schID=‘123321‘
insert into schoolInfo(schId,areaId,schName)
 values('123321','5800','aaaa')

注意：如果再次执行该insert，会报主键重复错误，不会报触发器提示，说明表本身的约束优先于触发器执行，而且只要约束没通过，则不再执行后续触发器。

【例2】 使用前置触发器改写例1。

create trigger SchoolInfo_Before_Insert
 on schoolInfo instead of insert
as
begin
 if not exists(select * from areaInfo where areaId in (select areaId from inserted))
 begin
 print '前置触发器：插入数据的areaId是无效的，本次操作没有成功！'
 end
 else
 begin
 insert into schoolInfo(schId,areaID,schName) select * from inserted
 print '前置触发器：插入成功！'
 end
end

测试：

insert into schoolInfo(schId,areaId,schName)
 values('12321','1000','aaa')
select * from schoolInfo where schID=‘123321‘
insert into schoolInfo(schId,areaId,schName)
 values('123321','5800','aaaa')

注：触发器中，如果else分支没有insert还原原有的操作的话，则永远无法往schoolInfo表插入数据，因为所有插入操作都被替代为instead of触发器功能。当然，插入成功后会再触发后置触发器（如果在该表创建了后置触发器）。IT技术博客

【例3】创建后置触发器：利用第14讲例6创建的生成指定学校指定班级新学号的函数fun_getNewStuNo实现：当向stuInfo表插入一条学生数据时，自动为该生生成学号。

创建触发器：IT技术支持

create trigger stuInfo_after_Insert
on stuInfo
after insert
as
begin
 declare @classId char(9)
 select @classId=schID+classID from inserted --获取完整的班级号
 update stuInfo set stuid=dbo.fun_getNewStuNo(@classId)
 where autoID =(select autoID from inserted)
end
测试：
insert into stuInfo(stuName,stuSex,schID,classID)
values(‘李霞’,‘男',‘58010101') go
Select * from stuInfo where schId+classId=‘58010101’) --观察有李霞学号没

想看更多有关IT技术或者数据库技术就请大家关注本网站首页：IT技术网站