SQL触发器笔记

 

触发器（Trigger）是在对表进行插入、更新、删除等操作时自动执行的存储过程。

触发器是一种特殊的存储过程，它在执行语言事件时自动生效，采用事件驱动机制。当某个触发事件发生时，定义在触发器中的功能将被DBMS自动执行。

触发器可以用于SQL Server约束、默认值和规则的完整性检查，还可以完成难以用普通约束实现的复杂功能。

当一个触发器建立后，它作为一个数据库对象被存储。

 

触发器支持哪些功能？

在触发事件执行之后被触发，方可完成事件本身的功能

可以引用事件中对于行修改前后的值

对于UPDATE事件可以定义对哪个表、或表中的哪一列被修改时，触发器被触发

可以用WHEN子句来指定执行条件

 

可以完成一些复杂的数据检查

 

触发器有哪些优点？

能够实现更复杂的数据完整性检验

和约束相比，触发器更灵活

 

对表的任何修改，触发器都能被激活，对数据实施完整性检查

 

存储过程与触发器有何区别？

触发器与普通存储过程的不同之处在于：触发器的执行是由事件触发的，而普通存储过程是由命令调用执行的。

存储过程必须由用户、应用程序或者触发器来显示的调用并执行。

 

触发器是一种特殊类型的存储过程，它在指定的表中数据发生变化时自动生效。

触发器是当特定时间出现的时候，自动执行或者激活的，与连接用数据库中的用户、或者应用程序无关。当一行被插入、更新或者删除时触发器才执行，同时还取决于触发器是怎样创建的，

当UPDATE发生时使用一个更新触发器，当INSERT发生时使用一个插入触发器，当DELETE发生时使用一个删除触发器。

 

小结

触发器是一种特殊类型的存储过程，它在指定的表中数据发生变化时自动生效。

 

触发器与普通存储过程的不同之处在于：触发器的执行是由事件触发的，而普通存储过程是由命令调用执行的。

 

触发器的类型

(1)DML触发器

AFTER触发器：在执行了插入、修改、删除之后执行的触发器，指定after和指定for是相同的，for存在于早期版本，SqlServer2005之后的版本就大都使用after触发器。

INSTEAD OF触发器：执行instead of 操作，代替引发触发器的数据库操作。

 

(2)DDL触发器：针对数据定义语言来进行触发的，可用于数据库中执行管理任务，比如说审核以及规范数据库操作。

 

DML与DDL之间有什么区别：

从简单意义上讲，

DML触发器是针对插入、修改、删除操作进行的触发。

 

DDL是针对create、alter、drop等数据库定义语言的时候进行的触发；目的是禁止对数据库结构的修改；当修改数据库结构时执行一些特定的操作；记录对数据库结构的修改。

 

触发器所影响的表

(1)inserted表

用于存储INSERT和UPDATE语句所影响的行 的最终数据副本。

在一个插入或更新事务处理中，新建行被同时添加到inserted表和触发器表中。

 

inserted表中的行是触发器表中新行的副本。

(2)deleted表

 

用于存储DELETE和UPDATE语句所影响的行的复本。

在执行DELETE或UPDATE语句时，行从触发器表中删除，并传输到deleted表中。

 

deleted表和触发器表通常没有相同的行。

 

临时表：SQL server 2005会自动创建和管理，表结构和被操作的表结构一致

 

inserted表和deleted表存放的信息如下表：

 

 

 

 

当创建一个触发器时必须指定如下选项：

（1）名称；

（2）在其上定义触发器的表；

（3）触发器将何时激发；

（4）激活触发器的数据修改语句，有效选项为 INSERT、UPDATE 或 DELETE，多个数据修改语句可激活同一个触发器；

 

（5）执行触发操作的编程语句。

 

 

创建一个触发器，当对student表里面的学号数据进行插入或者更新时，要检查该数据是否五位数，如果不是，则此次操作取消

use stu
go
create trigger trigger_1
on student
for insert,update
as
declare @sno varchar(10)
set @sno=(select sno from inserted)
if(len(@sno)!=5)
    rollback

 

创建一个触发器，不允许在本服务器上删除任何数据库

 

create trigger database_delete
on all server
for drop_database
as
rollback

 

创建触发器，完成功能：禁止在数据库中对表的所有DDL操作。（慎用）

create trigger tr_all_no
on database
for create_table,alter_table,drop_table
as
rollback

 

 

创建触发器，禁止对Student表中的Sname,Sage进行更新。

CREATE TRIGGER  trig_update_ Student  
ON  Student  
FOR  UPDATE 
AS
 if (update(Sname)or update(Sage))  
begin   
RAISERROR ('安全警告：该信息不能修改',16,1)
        print '事务不能被处理，基本数据不能修改！'  
        rollback transaction  
end   

 

 

创建一个触发器（Trigger_学生性别），用以约束学生表中性别字段值（只可能是“男”或“女”），保证数据的正确性。

CREATE TRIGGER Trigger_学生性别 ON student 
 FOR INSERT 
 AS 
 IF EXISTS(SELECT * FROM student WHERE sgender NOT IN ('男','女')) 
 BEGIN 
    RAISERROR('请输入合法的性别！',16,1) 
    ROLLBACK TRANSACTION 
 END

 

删除一个已有的触发器（Trigger_学生）

IF EXISTS(SELECT name FROM sysobjects 
WHERE name = 'Trigger_学生性别' and type='TR') 
DROP TRIGGER Trigger_学生性别

 

 

使用T-SQL脚本删除触发器
语法：
--声明数据库引用
use 数据库;
go

--判断是否存在，如果存在则删除
if exists(select * from sys.triggers where name=触发器名)

--删除DML触发器
drop trigger 触发器名;

----删除DDL触发器
--drop trigger 触发器名 on database;

--删除登录触发器
--drop trigger 触发器名 on all server;