【基础知识】-SQL触发器

一、概念

    触发器（trigger）是SQL server 提供给程序员和数据分析员来保证数据完整性的一种方法，它是与表事件相关的特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发，比如当对一个表进行操作（ insert，delete， update）时就会激活它执行。触发器经常用于加强数据的完整性约束和业务规则等。 触发器可以从 DBA_TRIGGERS ，USER_TRIGGERS 数据字典中查到。SQL3的触发器是一个能由系统自动执行对数据库修改的语句。

 

触发器和存储过程的区别：

触发器与存储过程的唯一区别是触发器不能执行EXECUTE语句调用，而是在用户执行Transact-SQL语句时自动触发执行。而存储过程需要用户，应用程序或者触发器来显示地调用并执行。

 

二、作用

    触发器的主要作用就是其能够实现由主键和外键所不能保证的复杂参照完整性和数据的一致性，它能够对数据库中的相关表进行级联修改，提高比CHECK约束更复杂的的数据完整性，并自定义错误消息。触发器的主要作用主要有以下几个个方面：

 

• 可在写入数据表前，强制检验或转换数据。
• 触发器发生错误时，异动的结果会被撤销。
• 部分数据库管理系统可以针对数据定义语言（DDL）使用触发器，称为DDL触发器。
• 可依照特定的情况，替换异动的指令 (INSTEAD OF)。

 

三、优点

1. 触发器是自动的。当对表中的数据做了任何修改之后立即被激活。

2. 触发器可以通过数据库中的相关表进行层叠修改。

3. 触发器可以强制限制。这些限制比用CHECK约束所定义的更复杂。与CHECK约束不同的是，触发器可以引用其他表中的列。

 

 

四、分类

    SQL Server 包括三种常规类型的触发器：DML 触发器、DDL 触发器和登录触发器。

DML触发器

当数据库中表中的数据发生变化时，包括insert,update,delete任意操作，如果我们对该表写了对应的DML触发器，那么该触发器自动执行。DML触发器的主要作用在于强制执行业务规则，以及扩展Sql Server约束，默认值等。因为我们知道约束只能约束同一个表中的数据，而触发器中则可以执行任意Sql命令。

DDL触发器

它是Sql Server2005新增的触发器，主要用于审核与规范对数据库中表，触发器，视图等结构上的操作。比如在修改表，修改列，新增表，新增列等。它在数据库结构发生变化时执行，我们主要用它来记录数据库的修改过程，以及限制程序员对数据库的修改，比如不允许删除某些指定表等。

登录触发器

登录触发器将为响应 LOGIN 事件而激发存储过程。与 SQL Server 实例建立用户会话时将引发此事件。登录触发器将在登录的身份验证阶段完成之后且用户会话实际建立之前激发。因此，来自触发器内部且通常将到达用户的所有消息（例如错误消息和来自 PRINT 语句的消息）会传送到 SQL Server 错误日志。如果身份验证失败，将不激发登录触发器。

工作原理：

触发器触发时：

1. 系统自动在内存中创建deleted表或inserted表；

2. 只读，不允许修改，触发器执行完成后，自动删除。

inserted表：

1.  临时保存了插入或更新后的记录行；

2.  可以从inserted表中检查插入的数据是否满足业务需求；

3. 如果不满足，则向用户发送报告错误消息，并回滚插入操作。　

deleted表：

1.  临时保存了删除或更新前的记录行；

2.  可以从deleted表中检查被删除的数据是否满足业务需求；

3.  如果不满足，则向用户报告错误消息，并回滚插入操作。

inserted表和deleted表对照：　

 

 

 

 

Sql语法

 

创建语法：

CREATE TRIGGER trigger_name

 ON table_name

 [WITH ENCRYPTION]

  FOR | AFTER | INSTEAD OF [DELETE, INSERT, UPDATE]

 AS 

  T-SQL语句GO--with encryption 表示加密触发器定义的sql文本

--delete,insert,update指定触发器的类型

insert

1 2 3 4 5 6 7 8 9 10 11 12 13

create trigger tri_insert on student for insert as declare @student_idchar(10) select @student_id=s.student_id from students inner join insertedion s.student_id=i.student_id if @student_id='0000000001' begin raiserror('不能插入1的学号!',16,8) rollbacktran end go

update

1 2 3 4 5 6 7 8 9 10

create trigger tri_update on student for update as if update(student_id) begin raiserror('学号不能修改!',16,8) rollbacktran end go

delete

1 2 3 4 5 6 7 8 9 10 11

create trigger tri_delete on student for delete as declare @student_idvarchar(10) select @student_id=student_id from deleted if @student_id='admin' begin raiserror('错误',16,8) rollbacktran end