sql server 数据库之触发器

下面将从下面几个方面介绍触发器：

    1.触发器的概念；

    2.触发器的优缺点；

    3.触发器的作用；

    附加：触发器功能与约束功能的比较

    4.触发器的分类；

    5.利用t-sql语句创建并运行图书管理系统数据库的触发器；

    6.利用t-sql语句修改图书管理系统数据库的触发器；

    7.利用t-sql语句删除图书管理系统数据库的触发器；

    8.利用t-sql语句禁用或启动图书管理系统数据库的触发器；

一.触发器的概念

   触发器就是指在对指定数据表进行数据值修改时，所要执行的特殊的存储过程，但是该存储过程不用关键字exec调用执行，而是通过事件触发执行，以保证对不同数据表中具有逻辑相关性数据的引用的一致性和完整性。

   触发器是一种强大的使用工具，通过执行t-sql语句实现对数据库的复杂操作及对数据完整性的约束等，只有数据更改的站点都要接受有触发器所设置的强制性自动业务规则检查，一般是对约束、默认值及规则的完整性进行检查。例如：当对某一数据表进行UPDATE、INSERT、DELETE等操作时，sql server就会自动执行触发器所定义的sql语句，从而保证了对数据的处理必须符合这些sql语句所定义的规则。

二、触发器的优点

  触发器的使用可以确保数据信息引用的完整性和一致性，其突出的有点表现为：

    1.触发器是被自动执行的，不需要额外的 执行语句，当对数据表中的任何数据信息进行更改操作时，触发器被自动激活，立即执行。

    2.触发器可以实施比CHECK约束命令更为复杂的强制性约束限制，不仅可以引用本表中的数据列，而且还可以引用其他数据表中的数据列。

    3.触发器可以实现对数据的层叠修改和数据表的级联更改。

三、触发器的作用

   1.触发器乐意实现较为复杂的数据完整性约束

        触发器的只要作用是指能够实现通过主键和外键的使用所不能确保的复杂的参照完整性和数据一致性，以及数据的准确性检查和约束。提供一种比规则和约束更为复杂的限制，即触发器可以引用数据列或数据对象，并对其实施复杂的强制约束。

        具体表现为，可以更改默认值；触发器可以对数据库中相关联的数据表实施整合修改，同时触发与之相关的操作；在对某一张数据表进行修改或删除时，将与之有级联关系的数据表中相匹配的数据行也一同进行修改或删除

   2.确保数据的安全性

        在数据库系统中触发器根据设定的数值不同，分配给用户操作数据的权限不同；触发器依据时间的限制规定用户能进行的不同操作；触发器还可以基于数据表中的具体的数据值限制用户所能进行的操作；触发器还可以返一些用户自定义的错误信息，这些是约束操作所无法实现的；为了保证数据操作的安全性，触发器可以从多个存储过程中调用执行。

  3.启用了审计跟踪制度

        利用触发器可以跟踪用户操纵数据库的各种处理操作，审计用户对数据库操作的每条语句将用户对数据所做的任何更新操作记录到对应的审计数据表中，实施跟数据库的变化，随时准备撤销或回滚对数据库所进行的非法操作，最大限度的防止对信息的非法修改。

附加：触发器功能与约束功能的比较

        触发器与约束功能宏观地看都是保证数据信息引用完整性和操作一致性地手段，由于二者地适用场合不同，优势也不相同。

        触发器地优势是可以执行利用t-sql语句代码编写复杂处理逻辑，约束所规定地功能触发器均可以支持，尤其是针对，某些应用程序地需求约束所不能实现地功能。

        由于实体完整性地实现是应用到较低级别上的，一般通过索引操作强制实现，通常是主键和UNIQUE约束的一部分，也可以在约束之外独立的创建索引来实现，若可以满足实际的功能需求时，对域的完整性的设定一般适用CHECK约束进行强制，对引用完整性则使用外键约束进行强制，而不用触发器，其目的是提高系统的运行性能。

四、触发器的分类

   在sql server系统中，触发器可以大致划分成两种类型，DML触发器(数据操作语言触发器)和DDL触发器(数据定义语言触发器)

  1.DML触发器

        该触发器应用的环境是数据库中有数据操作发生，是附加到特定的数据表或视图上的t-sql代码，在服务器上对数据信息实施数据操作语言事件时执行DML触发器所设定的语句。

         1)根据执行的不同操作分类：INSERT触发器，UPDATE触发器，DELETE触发器。

        对于上面三种触发器的使用过程中，系统对每个被创建的触发器都设定看两张特殊的逻辑表，即DELETETED和INSERTTED，从结构上看与定义触发器的表相似存放在内存中，由系统进行创建和维护，用户无权修改这两张表，当触发器的操作结束时，这两张逻辑表也会被自动删除。实施插入操作时，新增数据行同时被添加到触发器表和INSERTTED表中,删除也是同理。

       在系统使用中遇到以下情形时，通常应用DML触发器：第一，利用数据表实现级联更改；第二，考核数据修改前后的状态，以它们的差异为依据决定将要实施的操作；第三，预防恶意的插入、更新、删除操作，需要强制的检测和复杂的约束条件。

         2)根据不同的执行时机分类：AFTER后触发器和INSTEAD OF 前触发器

       所谓AFTER后触发器，是指在执行某一操作如：INSERT、UPDATE、DELETE之后该触发器被引发，该类触发器适合定义在数据表上；此外，该触发器可以规定触发器的执行顺序通过，系统存储过程sp_settriggerorder实现哪个触发器先被触发。

      所谓INSTEAD OF前触发器，是指在没有执行任何定义的具体操作就启动触发器既可以在表上定义，又可在视图上定义。

   2.DDL触发器

         该触发器是在数据服务器或数据库系统中有数据定义语言事件发生时激发，DDL触发器可以定义在整个服务器或服务器某一规定范围内，也可以被某一单个DDL语句或一组语句创建DDL触发器，用于该类触发器的语句是以CREATE、ALTER和DROP等开头的数据定义语句。因而，DDL触发器主要用于任务管理、控制数据库操作、防止对数据库架构的不良修改及记录对数据库架构的更改操作和执行事件等。

五.利用t-sql语句创建并运行图书管理系统数据库的触发器

创建语句：

USE LibraryManagement
IF EXISTS(SELECT name FROM SYSOBJECTS
          WHERE name='tri_borrowinsert' AND type='tr')
   DROP TRIGGER tri_borrowinsert
GO
CREATE TRIGGER tri_borrowinsert
ON Borrowreturninfo
FOR INSERT
AS
DECLARE @BorrowDate datetime
DECLARE @Readerregisterdate datetime
SELECT @BorrowDate=BorrowReturninfo.Borrow_Date FROM BorrowReturninfo,inserted
WHERE BorrowReturninfo.Book_ID=inserted.Borrow_ID 
SELECT @Readerregisterdate=Readerinfo.Reader_registerDate
FROM Readerinfo,BorrowReturninfo
WHERE Readerinfo.Reader_ID=BorrowReturninfo.Reader_ID
IF @BorrowDate<@Readerregisterdate
BEGIN
	RAISERROR('没有注册登录的读者是不能借阅图书的，请删除该行数据！',16,10)
END

触发执行：

INSERT INTO BorrowReturninfo
(Borrow_ID,Book_ID,Reader_ID,Borrow_Date,Borrow_Clerk_ID,Return_Date,Return_Clerk_ID,Book_State)
VALUES(140724001,'10301004','12010702','2010-10-10','C0003',NULL,NULL,'借出')

利用T-SQL创建DML触发器的格式：

CREATE TRIGGER <触发器名称>
ON<表名>|<视图名>
[WITH ENCRYPTION]           --该选项是指加密syscomments表中包含的CREATE TRIGGER语句文本的条目，使用该选项能够防止将触发器作为sql server系统复制的一
                            --部分发布
[FOR|AFTER|INSTEAD OF]
[DELETE][,][INSERT][,][UPDATE]  --此选项是指定在数据表或视图上执行哪些数据修改语句时，将激活触发器的关键字，至少要选中一个选项
[WITH APPEND]               --该选项用于添加可用额其他类型触发器，仅适用于明确指定FOR关键字且位向后兼容的类型，该项不允许与INSTEAD OF类型的触发器同时
                            --使用，也不能与显示生命的AFTER触发器同时使用；

[NOT FOR REPLICATION]       --该选项表示系统中有复制进行修改触发器所关联的数据表时，该触发器不能被执行
AS
[IF UPDATE(<列名>)][{AND|OR}UPDATE(<列名>)][...n]
                            --UPDATE(列名)该函数表明在指定的数据列上检测是否有INSERT或UPDATE操作，但对DELETE操作不能进行检测应当使用单独的UPDATE子句，
                            --如果所制定的数据列执行了此操作则返回TRUE,此时若插入的是NULL值，触发器同样可以被触发
|IF(COLUMNS_UPDATED(){<位操作>}<更新位标识>){<比较运算符>}<列的位标志>[...n]
                            --COLUMN_UPDATE()该函数表明对进行插入或更新操作的数据列进行检测，只能用在INSERT或UPDATE触发器中，起返回值是一个VARBINARY
                            --类型的二进制数，该值是以位模式-进行心事，表明那些数据列进行了插入或更新操作。
<T-SQL语句>[,...n]          --规定触发器的条件和执行的具体操作

利用T-SQL语句创建DDL触发器的语法格式：

CREATE TRIGGER <触发器名>
ON {ALL SERVER|DATABASE        --ALL SERVER 表示应用DDL触发器或登录触发器的当前服务器
                               --DATABASE表示应用DDL触发器的当前数据库
[WITH<DDL触发器选项>[,...n]]
{FOR|AFTER}{<事件类型或事件组>} --表示引发DDL触发器t-sql语言的事件名称
[,...n]
AS
{SQL查询语句}[;][...n]|EXTERNAL NAME<方法描述>[;]}

创建触发器的注意事项：

    1.创建触发器的语句CREATE TRIGGER必须放置在批处理中第一条语句的位置。

    2.数据表所有者具有默认的创建触发器的权限，但是该权限不可以转移给其他用户。

    3.如果触发器是数据库对象，其名称要遵循标识符的命名规则。

    4.触发器只可以在当前数据库中创建，但可以引用其他数据库对象。

    5.触发器不允许在临时表或系统表上建立，但可以引用临时表。

使用触发器的注意事项：

      触发器不能返回运行结果集，因而在触发器中不允许使用自由格式的SELECT语句进行编码，规范编码应使用标量子查询给设定的变量赋值；此外，在触发器编码的开始处应使用SET NoCountON语句，防止数据库发送信息，以便说明每一条语句所影响的数据行数。

编辑带有事务回滚功能的INSERT触发器：

USE LibraryManagement
IF EXISTS(SELECT name FORM SYSOBJECTS
          WHERE name='tri_Rinsertinstead' AND TYPE='tr')
		  DROP TRIGGER tri_Rinsertinstead
GO
CREATE TRIGGER tri_Rinsertinstead
ON Borrowreturninfo
INSTEAD OF INSERT
AS
   RAISERROR('您无权插入或插入操作有误！',16,10)
GO
INSERT INTO BorrowReturninfo
(Borrow_ID,Book_ID,Reader_ID,Borrow_Date,Borrow_Clerk_ID,
Return_Date,Return_Clerk_ID,Book_State)
VALUES(140724002,'10301004','12010702','2010-10-10','C0003',NULL,NULL,'借出')

执行：

SELECT * 
FROM Borrowreturninfo WHERE Borrow_ID='140724002'

利用t-sql创建并执行一个名为tri_readerupdate的UPDATE触发器：

USE LibraryManagement
IF EXISTS(SELECT name FROM SYSOBJECTS
	  WHERE name='tri_readerupdate' AND TYPE='tr')
          DROP TRIGGER tri_readerupdate
GO
CREATE TRIGGER tri_readerupdate
ON Readerinfo
FOR UPDATE
AS
	IF UPDATE(Reader_telephone)
	BEGIN
		SELECT inserted.Reader_name,inserted.Reader_telephone AS new_Readertelephone,
		deleted.Reader_telephone AS old_Readertelephone
		FROM inserted,deleted
		WHERE inserted.Reader_ID=deleted.Reader_ID
	END
GO

激活触发器：

UPDATE Readerinfo SET Reader_telephone='15820787601' WHERE Reader_ID='12110201'

创建一个名为tri_Bookdeleted的DELETE触发器：

USE LibraryManagement
IF EXISTS(SELECT name FROM SYSOBJECTS
          WHERE name='tri_Bookdeleted' AND TYPE='tr')
		  DROP TRIGGER tri_Bookdeleted
GO
CREATE TRIGGER tri_Bookdeleted
ON Bookinfo
FOR DELETE
AS
SELECT SUM(deleted.Book_quantity)AS '被删除图书的总本数',
       SUM(deleted.Book_price) AS '被删除图书总金额'
FROM deleted
GO
DELETE Bookinfo WHERE LEFT(Book_ID,1)='A'

创建一个名为tri_PunishDelsafety的DDL触发器：

CREATE TRIGGER tri_Punishsafety
ON database
FOR drop_TABLE,alter_TABLE
AS
PRINT '要对数据表进行删除或修改操作，必须先要禁用触发器tri_Punishsafety，否则无法操作！'
ROLLBACK
GO

执行：

DROP TABLE Punishinfo

六.利用t-sql语句修改图书管理系统数据库的触发器

修改名为tri_Rinsertinstead的触发器：

USE LibraryManagement
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER dbo.tri_Rinsertinstead
ON dbo.Borrowreturninfo
INSTEAD OF DELETE
AS
RAISERROR('您无权删除或删除操作有误！',16,10)

激活：

DELETE FROM BorrowReturninfo WHERE Borrow_ID=140719008

利用t-sql语句修改触发器的语法格式：

ALTER TRIGGER <触发器名称>
ON {<表名>|<视图名>}
[WITH ENCRYPTION]
[FOR|AFTER|INSTEAD OF][DELETE][,][INSERT][,][UPDATE]
[WITH APPEND][NOT FOR REPLICATION]
AS
[IF UPDATE(<列名>)][{AND|OR}UPDATE(列名)][...n]
|IF(COLUMNS_UPDATED(){<位操作符>}<更新位标志>){<比较运算符>}<列的位标志>[...n])
<T-SQL语句>

七.利用t-sql语句删除图书管理系统数据库的触发器
语法格式：

IF EXISTS(SELECT NAME SYSOBJECTS WHERE NAME=<触发器名> AND XTYPE='TR')
DROP TRIGGER <触发器名称>[,...n]

八.利用t-sql语句禁用或启动图书管理系统数据库的触发器

DISABLE TRIGGER tri_Bookdeleted
ON bookinfo

禁用触发器语法格式：

DISABLE TRIGGER {[<架构名>.]<触发器名>[,...n]|ALL}
ON {<对象名>              --对象名，对DML触发器而言，是该触发器所关联的对象名称
|DATABASE|ALL SERVER}[;] -- 对于DDL触发器而言，是触发器所在的数据库系统所处的所有服务器

启动触发器语法格式：

ENABLE TRIGGER {[<架构名>.]<触发器名>[,...n]|ALL}
ON {<对象名>|DATABASE|ALL SERVER}[;]

管理触发器应包含的内容：

    当触发器被创建之后，在系统的运行过程中应当实时对触发器进行维护与管理，通常包括查看触发器的相关信息，禁用与启用触发器、修改与删除等操作

若哪里错了请指出，谢谢！