SqlServer基础之(触发器)

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指定触发器的类型

 准备测试数据:

--创建学生表create table student( stu_id int identity(1,1) primary key, stu_name varchar(10), stu_gender char(2), stu_age int)

1.创建insert触发器

--创建insert触发器create trigger trig_inserton studentafter insertasbegin if object_id(N‘student_sum‘,N‘U‘) is null--判断student_sum表是否存在 create table student_sum(stuCount int default(0));--创建存储学生人数的student_sum表 declare @stuNumber int; select @stuNumber = count(*)from student; if not exists (select * from student_sum)--判断表中是否有记录 insert into student_sum values(0); update student_sum set stuCount =@stuNumber; --把更新后总的学生数插入到student_sum表中end

--测试触发器trig_insert-->功能是向student插入数据的同时级联插入到student_sum表中，更新stuCount--因为是后触发器，所以先插入数据后，才触发触发器trig_insert;insert into student(stu_name,stu_gender,stu_age)values(‘吕布‘,‘男‘,30);select stuCount 学生总人数 from student_sum; insert into student(stu_name,stu_gender,stu_age)values(‘貂蝉‘,‘女‘,30); select stuCount 学生总人数 from student_sum;insert into student(stu_name,stu_gender,stu_age)values(‘曹阿瞒‘,‘男‘,40); select stuCount 学生总人数 from student_sum;

执行上面的语句后，结果如下图所示：

 既然定义了学生总数表student_sum表是向student表中插入数据后才计算学生总数的，所以学生总数表应该禁止用户向其中插入数据

--创建insert_forbidden,禁止用户向student_sum表中插入数据create trigger insert_forbiddenon student_sumafter insertasbegin RAISERROR(‘禁止直接向该表中插入记录，操作被禁止‘,1,1)--raiserror 是用于抛出一个错误rollback transactionend 

--触发触发器insert_forbiddeninsert student_sum (stuCount) values(5);

结果如下：

 2.创建delete触发器

用户执行delete操作，就会激活delete触发器，从而控制用户能够从数据库中删除数据记录，触发delete触发器后，用户删除的记录会被添加到deleted表中，原来表的相应记录被删除，所以在deleted表中查看删除的记录。

--创建delete触发器create trigger trig_deleteon student after deleteasbegin select stu_id as 已删除的学生编号,stu_name stu_gender,stu_age from deletedend;

--执行一一条delete语句触发trig_delete触发器delete from student where stu_id=1;

结果如下：

 3.创建UPDATE触发器

update触发器是当用户在指定表上执行update语句时被调用被调用，这种类型的触发器用来约束用户对数据的修改。update触发器可以执行两种操作：更新前的记录存储在deleted表中，更新后的记录存储在inserted表中。

--创建update触发器create trigger trig_updateon studentafter updateasbegin declare @stuCount int; select @stuCount=count(*) from student; update student_sum set stuCount =@stuCount; select stu_id as 更新前学生编号,stu_name as 更新前学生姓名 from deleted select stu_id as 更新后学生编号,stu_name as 更新后学生姓名 from insertedend

--创建完成，执行一条update语句触发trig_update触发器update student set stu_name=‘张飞‘ where stu_id=2;

 4.创建替代触发器

与前面介绍的三种after触发器不同，SqlServer服务器在执行after触发器的sql代码后，先建立临时的inserted表和deleted表，然后执行代码中对数据库操作，最后才激活触发器中的代码。而对于替代(instead of)触发器,SqlServer服务器在执行触发instead of 触发器的代码时，先建立临时的inserted表和deleted表，然后直接触发instead of触发器，而拒绝执行用户输入的DML操作语句。

--创建instead of 触发器 create trigger trig_insteadOfon student instead of insertas begin declare @stuAge int; select @stuAge=(select stu_age from inserted)if(@stuAge >120) select ‘插入年龄错误‘ as ‘失败原因‘end

创建完成，执行一条insert语句触发触发器trig_insteadOf

5.嵌套触发器介绍

如果一个触发器在执行操作时调用了另外一个触发器，而这个触发器又接着调用了下一个触发器，那么就形成了嵌套触发器。嵌套触发器在安装时就被启用，但是可以使用系统存储过程sp_configure禁用和重新启用嵌套触发器。

嵌套触发器不一定要形成一个环，它可以 T1->T2->T3...这样一直触发下去，最多允许嵌套 32 层。如果嵌套的次数超过限制，那么该触发器将被终止，并回滚整个事务，使用嵌套触发器需要注意以下几点：

嵌套是用来保持整个数据库的完整性的重要功能，但有时可能需要禁用嵌套，如果禁用了嵌套，那么修改一个触发器的实现不会再触发该表上的任何触发器。在下述情况下，需要禁用嵌套触发器：

使用下列语句禁用嵌套和再次启用嵌套：

--禁用嵌套exce sp_configure ‘nested triggers‘,0;--启用嵌套exce sp_configure ‘nested triggers‘,1;

6.递归触发器

触发器的递归是指一个触发器从其内部再一次激活该触发器，例如update操作激活的触发器内部还有一条数据表的更新语句，那么这个更新语句就有可能激活这个触发器本身，当然，这种递归的触发器内部还会有判断语句，只有一定情况下才会执行那个T_SQL语句，否则就成为无线调用的死循环了。

SqlServer中的递归触发器包括两种：直接递归和间接递归。

默认情况下，递归触发器选项是禁用的。递归触发器最多只能递归16层，如果递归中的第16个触发器激活了第17个触发器，则结果与发布的rollback命令一样，所有数据都将回滚。 

我们举例解释如下，假如有表1、表2名称分别为 T1、T2，在 T1、T2 上分别有触发器 G1、G2。

设置直接递归：

默认情况下是禁止直接递归的，要设置为允许有两种方法：

1.查看触发器

(1).查看数据库中所有的触发器

--查看数据库中所有的触发器use 数据库名goselect * from sysobjects where xtype=‘TR‘

sysobjects 保存着数据库的对象，其中 xtype 为 TR 的记录即为触发器对象。在 name 一列，我们可以看到触发器名称。

(2).sp_helptext 查看触发器内容

use 数据库名goexec sp_helptext ‘触发器名称‘

 将会以表的样式显示触发器内容。 

 除了触发器外，sp_helptext 还可以显示 规则、默认值、未加密的存储过程、用户定义函数、视图的文本。

(3).sp_helptrigger 用于查看触发器的属性

sp_helptrigger 有两个参数：第一个参数为表名；第二个为触发器类型，为 char(6) 类型，可以是 INSERT、UPDATE、DELETE，如果省略则显示指定表中所有类型触发器的属性。

use 数据库名goexec sp_helptrigger tableName

2.禁用启用触发器

禁用:alter table 表名 disable trigger 触发器名称　　启用:alter table 表名 enable trigger 触发器名称

如果有多个触发器，则各个触发器名称之间用英文逗号隔开。

如果把“触发器名称”换成“ALL”，则表示禁用或启用该表的全部触发器。

3修改触发器

--修改触发器语法ALTER TRIGGER trigger_name ON table_name [ WITH ENCRYPTION ] FOR {[DELETE][,][INSERT][,][UPDATE]} AS sql_statement;

4.删除触发器

 --语法格式: DROP TRIGGER { trigger } [ ,...n ]参数: trigger: 要删除的触发器名称 n:表示可以删除多个触发器的占位符 

SqlServer基础之(触发器)

标签：做了   开头   alter   对象   通过   table   发送   sel   介绍   

小编还为您整理了以下内容，可能对您也有帮助：

SQL中，触发器是什么？

1. 基本概念

触发器是特殊的存储过程，基于一个表创建，主要作用就是实现由主键和外键所不能保证的复杂的参照完整性和数据一致性。

当触发器所保护的数据发生变化（update,insert,delete）后，自动运行以保证数据的完整性和正确性。通俗的说：通过一个动作（update,insert,delete）调用一个存储过程（触发器）。

2. 类型

(1)DML触发器

在数据库中发生数据操作语言（DML）事件时将启用。DML 事件包括在指定表或视图中修改数据的 INSERT 语句、UPDATE 语句或 DELETE 语句。DML 触发器可以查询其他表，还可以包含复杂的 T-SQL 语句。系统将触发器和触发它的语句作为可在触发器内回滚的单个事务对待，如果检测到错误（例如，磁盘空间不足），则整个事务即自动回滚。

(2)DDL 触发器

SQL Server 2005 的新增功能。当服务器或数据库中发生数据定义语言（DDL）事件时将调用这些触发器。但与DML触发器不同的是，它们不会为响应针对表或视图的UPDATE、INSERT或DELETE语句而激发，相反，它们会为响应多种数据定义语言（DDL）语句而激发。这些语句主要是以CREATE、ALTER和DROP开头的语句。DDL触发器可用于管理任务，例如审核和控制数据库操作。

创建DML触发器

1. 使用存储过程模板创建存储过程

在【对象资源管理器】窗口中，展开“数据库”节点，再展开所选择的具体数据库节点，再展开“表”节点，右击要创建触发器的“表”，选择“新建触发器”命令，如图所示：

在右侧查询编辑器中出现触发器设计模板，用户可以在此基础上编辑触发器，单击“执行”按钮，即可创建该触发器。

2. 使用T-SQL语句创建表

CREATE TRIGGER 触发器

ON 表名

FOR[update,insert,delete ]

AS SQL语句

例9-6：创建基于表reader ，DELETE操作的触发器。

USE Library

GO

IF EXISTS(SELECT name FROM sysobjects

WHERE name='reader_d' AND type='TR')

DROP TRIGGER reader_d --如果已经存在触发器reader_d则删除

GO

CREATE TRIGGER reader_d --创建触发器

ON reader --基于表

FOR DELETE --删除事件

AS

PRINT '数据被删除!' --执行显示输出

GO

试试吧！

应用：

USE Library

GO

DELETE reader

where Rname='aaa'

执行结果：

数据被删除!

（所影响的行数为 1 行）

例9-7：在表borrow中添加借阅信息记录时，得到该书的应还日期。

说明：在表borrow中增加一个应还日期SReturnDate。

USE Library

IF EXISTS (SELECT name FROM sysobjects

WHERE name ='T_return_date' AND type='TR')

DROP TRIGGER T_return_date

GO

CREATE TRIGGER T_return_date --创建触发器

ON Borrow --基于表borrow

After INSERT --插入操作

AS

--查询插入记录INSERTED中读者的类型

DECLARE @type int,@dzbh char(10),@tsbh char(15)

SET @dzbh=(SELECT RID FROM inserted)

SET @tsbh=(SELECT BID FROM inserted)

SELECT @type= TypeID

FROM reader

WHERE RID=(SELECT RID FROM inserted)--副本

/*把Borrow表中的应还日期改为

当前日期加上各类读者的借阅期限*/

UPDATE Borrow SET SReturnDate=getdate()+

CASE

WHEN @type=1 THEN 90

WHEN @type=2 THEN 60

WHEN @type=3 THEN 30

END

WHERE RID=@dzbh and BID=@tsbh

应用：

USE Library

INSERT INTO borrow(RID,BID) values('2000186010','TP85-08')

查看记录：

例9-8：在数据库Library中，当读者还书时，实际上要修改表brorrowinf中相应记录还期列的值，请计算出是否过期。

USE Library

IF EXISTS(SELECT name FROM sysobjects

WHERE name='T_fine_js' AND type='TR')

DROP TRIGGER T_fine_js

GO

CREATE TRIGGER T_fine_js

ON borrow

After UPDATE

AS

DECLARE @days int,@dzbh char(10),@tsbh char(15)

SET @dzbh=(select RID from inserted)

SET @tsbh=(select BID from inserted)

SELECT @days=DATEDIFF(day, ReturnDate, SReturnDate)

--DATEDIFF函数返回两个日期之差，单位为DAY

FROM borrow

WHERE RID=@dzbh and BID=@tsbh

IF @days>0

PRINT '没有过期！'

ELSE

PRINT '过期'+convert(char(6),@days)+'天'

GO

应用：

USE Library

UPDATE borrow SET ReturnDate='2007-12-12'

WHERE RID='2000186010' and BID='TP85-08'

GO

执行结果：

过期-157 天

(1 行受影响)

例9-9：对Library库中Reader表的 DELETE操作定义触发器。

USE Library

GO

IF EXISTS(SELECT name FROM sysobjects

WHERE name='reader_d' AND type='TR')

DROP TRIGGER reader_d

GO

CREATE TRIGGER reader_d

ON Reader

FOR DELETE

AS

DECLARE @data_yj int

SELECT @data_yj=Lendnum

FROM deleted

IF @data_yj>0

BEGIN

PRINT '该读者不能删除！还有'+convert(char(2),@data_yj)+'本书没还。

ROLLBACK

END

ELSE

PRINT '该读者已被删除！'

GO

应用：

USE Library

GO

DELETE Reader WHERE RID='2005216119'

执行结果：

该读者不能删除！还有4 本书没还。

修改触发器

ALTER TRIGGER 触发器

删除触发器

DROP TRIGGER 触发器

查看触发器

sp_helptext trigger_name

sp_helptrigger table_name

SQLSERVER中触发器的触发类型有几种?

SQL SERVER中触发器的触发类型有三种。

1、DML触发器，当数据库中表中的数据发生变化时，包括insert,update,delete任意操作，如果对该表写了对应的DML触发器，那么该触发器自动执行。

2、DDL触发器，是Sql Server2005新增的触发器，主要用于审核与规范对数据库中表，触发器，视图等结构上的操作。比如在修改表，修改列，新增表，新增列等。它在数据库结构发生变化时执行，主要用它来记录数据库的修改过程，以及程序员对数据库的修改。

3、登录触发器，登录触发器将为响应 LOGIN 事件而激发存储过程。与 SQL Server 实例建立用户会话时将引发此事件。登录触发器将在登录的身份验证阶段完成之后且用户会话实际建立之前激发。

扩展资料：

DML触发器的主要作用在于强制执行业 务规则，以及扩展Sql Server约束，默认值等。因为我们知道约束只能约束同一个表中的数据，而触发器中则可以执行任意Sql命令。

来自登录触发器内部且通常将到达用户的所有消息（例如错误消息和来自 PRINT 语句的消息）会传送到 SQL Server 错误日志。如果身份验证失败，将不激发登录触发器。

触发器可在写入数据表前，强制检验或转换数据。触发器发生错误时，异动的结果会被撤销。可依照特定的情况，替换异动的指令 (INSTEAD OF)。

sqlserver如何使用触发器？有什么优势？

首先介绍一下触发器：

触发器是一种特殊类型的存储过程，当使用下面的一种或多种数据修改操作在指定表中对数据进行修改时，触发器会生效：UPDATE、INSERT 或 DELETE。触发器可以查询其它表，而且可以包含复杂的 SQL 语句。它们主要用于强制复杂的业务规则或要求。例如，可以控制是否允许基于顾客的当前帐户状态插入定单。

触发器还有助于强制引用完整性，以便在添加、更新或删除表中的行时保留表之间已定义的关系。然而，强制引用完整性的最好方法是在相关表中定义主键和外键约束。如果使用数据库关系图，则可以在表之间创建关系以自动创建外键约束。有关详细信息，请参见表关系。

使用触发器的优点

触发器的优点如下：

触发器是自动的：它们在对表的数据作了任何修改（比如手工输入或者应用程序采取的操作）之后立即被激活。

触发器可以通过数据库中的相关表进行层叠更改。例如，可以在 titles 表的 title_id 列上写入一个删除触发器，以使其它表中的各匹配行采取删除操作。该触发器用 title_id 列作为唯一键，在 titleauthor、sales 及 roysched 表中对各匹配行进行定位。

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

再介绍存储过程：

存储过程（Stored Procere）是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中。用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象，任何一个设计良好的数据库应用程序都应该用到存储过程。总的来说，存储过程具有以下一些优点：

◆存储过程允许标准组件式编程

◆存储过程能够实现较快的执行速度

◆存储过程能够减少网络流量

◆存储过程可被作为一种安全机制来充分利用

使用 SQL Server 中的存储过程而不使用存储在客户计算机本地的 Transact-SQL 程序的优势有：

允许模块化程序设计。

只需创建过程一次并将其存储在数据库中，以后即可在程序中调用该过程任意次。存储过程可由在数据库编程方面有专长的人员创建，并可于程序源代码而单独修改。

允许更快执行。

如果某操作需要大量 Transact-SQL 代码或需重复执行，存储过程将比 Transact-SQL 批代码的执行要快。将在创建存储过程时对其进行分析和优化，并可在首次执行该过程后使用该过程的内存中版本。每次运行 Transact-SQL 语句时，都要从客户端重复发送，并且在 SQL Server 每次执行这些语句时，都要对其进行编译和优化。

减少网络流量。

一个需要数百行 Transact-SQL 代码的操作由一条执行过程代码的单独语句就可实现，而不需要在网络中发送数百行代码。

可作为安全机制使用。

即使对于没有直接执行存储过程中语句的权限的用户，也可授予他们执行该存储过程的权限