《数据库原理及应用》课件第10章

第10章触发器10.1触发器概述10.2DML触发器10.3DDL触发器10.4管理触发器10.5触发器的应用

【技能目标】

理解触发器的功能；学会创建和管理触发器；学会根据实际需要设计触发器。

【知识目标】

掌握触发器的概念；掌握触发器的分类；掌握inserted表和deleted表的用法；掌握创建、修改和删除触发器的方法；掌握查看触发器的方法；掌握禁用或启用触发器的方法和步骤；掌握触发器执行顺序设置的方法；掌握触发器的应用。

10.1.1触发器的概念

触发器是一种特殊类型的存储过程。一般存储过程通过使用存储过程名称即可直接调用，而触发器不需使用EXEC命令调用，而是通过事件触发被激活的。10.1触 发 器 概 述触发器的主要作用是能够实现复杂的完整性约束。当触发器所保护的数据发生改变时，触发器会自动被激活，从而防止对数据的不正确修改。例如，在学生成绩管理系统数据库SCMS中，当从学生表中删除一名同学的信息时，触发器可以自动修改学生选课表，将该同学的选课信息全部删除。又如，为了防止数据表结构更改或数据表被删除，保护已经建好的数据表，触发器可以在接收到以DROP和ALTER开头的SQL语句时，不进行对数据表的操作。

10.1.2触发器的分类

在SQLServer2005中，包括两大类触发器：DML触发器和DDL触发器。DDL触发器是SQLServer2005中新增的，以前的版本只有DML触发器。

1. DML触发器

当数据库中发生数据操作语言(DML)事件时DML触发器将被激活。DML事件包括在指定表或视图中修改数据的INSERT、UPDATE或DELETE操作。DML触发器不仅可以查询其他表，还可以包含复杂的Transact-SQL语句。DML触发器有助于在表或视图中修改数据时强制业务规则，扩展数据的完整性。

DML触发器分为三类，分别是AFTER触发器、INSTEADOF触发器和CLR触发器。

(1)

AFTER触发器。当数据修改完成之后AFTER触发器被激活。它主要用于记录变更后的处理或检查，一旦发现错误，可以用ROLLBACK语句回滚操作。可以为表的同一操作定义多个触发器。对于AFTER触发器，可以定义哪一个触发器被最先激活，哪一个被最后激活，通常调用系统存储过程sp_settriggerorder来设置。

(2)

INSTEADOF触发器。INSTEADOF触发器会取代原来要进行的操作，在记录变更之前发生，它并不去执行原来的操作(INSERT、UPDATE、DELETE)，而去执行触发器本身所定义的操作。既可在表上定义INSTEADOF触发器，也可以在视图上定义，但对同一操作只能定义一个INSTEADOF触发器。

(3)

CLR触发器。CLR触发器不仅可以是AFTER触发器或INSTEADOF触发器，还可以是DDL触发器。CLR触发器将执行在托管代码(在

.NETFramework中创建并在SQLServer中上载的程序集的成员)中编写的方法，而不用执行Transact-SQL存储过程。

2. DDL触发器

DDL触发器在响应数据定义语言(DDL)语句时被激活。它可以用于在数据库中执行管理任务，例如，审核以及规范数据库操作。DDL触发器会为响应多种数据定义语言(DDL)语句而激活。这些语句主要是以CREATE、ALTER和DROP开头的语句。10.1.3插入表和删除表

在SQLServer2005中，为DML触发器定义了两个特殊的表，一个是插入(inserted)表，另一个是删除(deleted)表。这两个表建在数据库服务器内存中，是由系统管理的逻辑表，而不是真正存储在数据库中的物理表。对于inserted表和deleted表，用户只有读取的权限，没有修改的权限，这两个表的结构总是与触发器所在数据表的结构完全一致的。inserted表和deleted表是动态驻留在内存中的，当触发器的工作完成之后，它们也将从内存中删除。inserted表和deleted表的功能如表10-1所示。表10-1inserted表和deleted表的功能从表10-1中可以看出，对具有触发器的表进行INSERT、DELETE或者UPDATE操作时，过程分别如下。

(1)

INSERT操作。对一个定义了插入类型触发器的表来讲，一旦对该表执行了插入操作，那么就将向该表插入的所有行复制到inserted表中。inserted表就是用来存储向表中插入的记录行的。

(2)

DELETE操作。对一个定义了删除类型触发器的表来讲，一旦对该表执行了删除操作，则将删除的所有行存放至deleted表中。deleted表就是用来存储表中删除的记录行的。这样做的目的是，一旦触发器遇到了强迫它中止的语句被执行时，删除的那些行可以从deleted表中得以恢复。

(3)

UPDATE操作。更新操作分两步完成，先将要更新的旧行删除，然后向表中插入新的记录。对一个定义了更新类型触发器的表来讲，执行更新操作时，将删除后的行转移到deleted表中，插入到表中的新行被复制到inserted表中。

10.2.1创建DML触发器

在SQLServer2005中可以使用CREATETRIGGER语句或使用SQLServerManagementStudio创建DML触发器。10.2DML触发器

1.使用CREATETRIGGER语句创建DML触发器使用CREATETRIGGER语句创建触发器时需要指定定义触发器的基表、触发器执行的时间和触发器被激活后执行的所有指令。其语法格式如下：

CREATETRIGGERtrigger_name

ONtable_name|view_name

[WITHENCRYPTION]

{FOR|AFTER|INSTEADOF}

{[INSERT][,][UPDATE][,][DELETE]}

AS

[IFUPDATE(column_name)…n]

[{AND|OR}UPDATE(column_name)…n]

sql_statement参数说明如下：

●

trigger_name：触发器的名称。该名称必须遵循标识符命名规则。

●

table_name|view_name：触发器表名或视图名。视图只能被INSTEADOF触发器

引用。

●

WITHENCRYPTION：对CREATETRIGGER语句的文本进行加密。不能为CLR触发器指定WITHENCRYPTION。

●

AFTER：指定DML触发器仅在触发SQL语句中指定的所有操作都已成功执行时才被激活。所有的引用级联操作和约束检查也必须在激活此触发器之前成功完成。如果仅指定FOR关键字，则AFTER为默认值。不能对视图定义AFTER触发器。

●

INSTEADOF：指定DML触发器是“代替”SQL语句执行的，因此其优先级高于触发语句的操作。可以为表或视图的每个INSERT、DELETE或UPDATE语句定义一个INSTEADOF触发器。不能为DDL触发器指定INSTEADOF触发器。●

INSERT，UPDATE，DELETE：指定数据修改语句，这些语句可在DML触发器对此表或视图进行尝试时激活该触发器。必须至少指定一个选项。在触发器定义中允许使用上述选项的任意顺序组合。如果指定的选项多于一个，需要用逗号分隔。

●

IFUPDATE：指定对表中的某一列或多列内容修改时，触发器才起作用。它可以指定多列，列名前可以不加表名。

●

sql_statement：定义触发器被触发后将执行的数据库操作。

说明只有下列用户才具有创建触发器的权利：表的所有者、databaseowner(db_owner)角色成员、systemadministrators(sysadmin)角色成员。

1)

INSERT触发器

INSERT触发器在向创建触发器的表进行插入操作时被激活。对于AFTER触发器，当插入语句正确完成后，触发器执行；对于INSTEADOF触发器，当检测到插入操作满足插入条件后，触发器操作“代替”SQL语句执行。

【例10-1】创建一个触发器，当用户试图向student表中添加数据时，该触发器向客户端显示一条消息。

分析：要求创建的触发器是在用户做INSERT操作时发挥作用，所以应该创建的触发器属于INSERT类型触发器。触发器执行时主要操作是向用户显示信息，可以用PRINT语句完成。下面创建两个INSERT触发器，一个为AFTER类型触发器，另一个为INSTEADOF类型触发器，比较两个触发器的区别。

(1)创建AFTER触发器。在查询编辑器窗口中输入以下代码：

CREATETRIGGERtr_insertFor --触发器的名字为tr_insertFor

ONstudent --触发器的基表是student

FORINSERT --触发器为AFTER类型的INSERT触发器

AS

PRINT'向学生表中插入了一条记录！' --触发器主要操作执行代码，tr_insertFor触发器创建成功。

为了测试触发器是否有效，向student表插入一条数据，语句如下：

INSERTINTOstudent(sno,sname,classno)

VALUES('200903102105','王萍萍','09031021')

执行结果如图10-1所示。

“消息”窗格中显示了“向学生表中插入了一条记录！”信息，说明插入操作激活tr_insertFor触发器，输出了该字符串。查看student表，如图10-2所示，刚才的插入操作已成功完成。

图10-1AFTER触发器示例

图10-2查看王萍萍信息

结论：对于AFTER触发器而言，要求引起触发器激活的语句正确完成后该触发器被激活。

(2)创建INSTEADOF触发器。为了验证INSTEADOF触发器，将前面建立的AFTER触发器删除。在查询编辑器窗口中输入以下代码：

CREATETRIGGERtr_insertInstead

ONstudent

INSTEADOFINSERT

AS

PRINT'向学生表中插入了一条记录！'

执行代码，tr_insertInstead触发器创建成功。

为了测试触发器是否有效，向student表插入一条数据，语句如下：

INSERTINTOstudent(sno,sname,classno)

VALUES('200903102107','李莉','09031021')

图10-3INSTEADOF触发器示例

“消息”窗格中显示了“向学生表中插入了一条记录！”字符串，说明插入操作激活了tr_insertInstead触发器，输出了该字符串。查看student表，如图10-4所示，并没有李莉同学的相关数据。

图10-4验证INSTEADOF触发器

2)

DELETE触发器

DELETE触发器在对创建触发器的表进行删除操作时被激活。

【例10-2】建立一个触发器，当从班级表中删除某一班级时，学生表中对应班级的所有学生信息也被删除。

分析：删除班级信息将其班级中的学生信息也删除，这就是表的级联更新。前面提到，触发器工作时有两个特殊的表，其中deleted表的功能就是存放被删除的旧记录。我们可以通过查看deleted表，得到用户删除的班级号，利用连接操作将student表中相应班级的学生记录删除。在查询编辑器窗口中输入以下代码：

CREATETRIGGERtr_deleteClass

ONclass

FORDELETE

AS

DELETEstudent

FROMstudent,deleted

WHEREstudent.classno=deleted.classno

执行代码，tr_deleteClass触发器创建成功。

测试触发器是否有效，将class表中的一条记录删除，如图10-5所示。

图10-5删除09011012班信息

在删除语句前后都加上了对student表的查询，主要是为了对比触发器激活前后表中数据的变化。可以看出，结果中删除了09011012班级记录，相应的学生记录也被删除了。

【例10-3】用户可以使用DELETE触发器，将从成绩表中删除的行存入一个命名为scoreBackup的表中。

分析：因为数据库中信息的删除是不可恢复的，所以在删除数据时可以对重要数据做备份。使用DELETE触发器，在删除数据的同时将数据备份到其他表中。

在查询编辑器窗口中输入以下代码：

CREATETABLEscoreBackup--创建存放备份数据的表scoreBackup

(snochar(12)NOTNULL,cnochar(5)NOTNULL,resultintNULL)

GO

CREATETRIGGERtr_scoreBackup

ONsc

FORDELETE

AS

INSERTINTOscoreBackup

SELECT*FROMdeleted

执行代码，tr_scoreBackup触发器创建成功。

为了测试触发器是否有效，删除sc表中所有课程号为03105的选课信息，如图10-6所示。

在删除语句后面利用SELECT查询显示scoreBackup表中的数据，可以看到sc表中删除的满足条件的记录都存放在scoreBackup表中以备使用。

图10-6查看scoreBackup表

3)

UPDATE触发器

当表做更新操作时，将激活UPDATE触发器。UPDATE型触发器有两种类型：通常意义上的UPDATE型触发器和用于检查列改变的UPDATE型触发器，这主要是因为更新操作可以涉及数据项。

(1)通常意义上的UPDATE型触发器。使用通常意义上的UPDATE型触发器，更新操作分两步，先将需更新的内容从表中删除掉，然后插入新值。因此，更新型触发器同时涉及deleted表和inserted表。

【例10-4】在sc表上创建UPDATE触发器，当对sc表做更新操作时为用户给出提示信息。

CREATETRIGGERtr_update

ONsc

FORUPDATE

AS

PRINT'SC表中的数据发生了变化！'

执行代码，tr_update触发器创建成功。

测试触发器是否有效，将sc表中学号为200903102103的学生选修的课程号为03203的课程成绩修改为65，如图10-7所示。

图10-7修改学生成绩

执行update语句后，激活了tr_update，在“消息”窗格中显示“SC表中的数据发生了变化！”，并且sc表中数据做了更新操作。

(2)检查列改变的UPDATE型触发器。在有些更新中，需要判断由指定一列或多列字段的更新激活触发器，这时就要用到用于检查列改变的更新型触发器。它与通常意义上的触发器不同之处主要表现在它包括以下保留字：

[IFUPDATE(column_name)…n]

[{AND|OR}UPDATE(column_name)…n]

【例10-5】用户可以设定在sc表中只有成绩列result被更新时，激活触发器。

CREATETRIGGERtr_updateColumn

ONsc

FORUPDATE

AS

IFUPDATE(result)

PRINT'sc表中的成绩列做了更新操作！'

执行代码，tr_updateColumn触发器创建成功。

为了验证检查列改变的更新型触发器，将前面建立的UPDATE触发器删除或禁用。在查询编辑器窗口中输入以下代码：

UPDATEsc

SETcno='03201'

WHEREsno='200903102103'

执行结果如图10-8所示。

图10-8验证tr_updateColumn触发器

结论：对sc表做了更新操作，并且更新操作成功执行了，但这次更新tr_updateColumn触发器没有被激活，因为该触发器的激活条件是更新sc表的result列，而本次更新操作更新的是cno列，不能激活触发器。

2.使用SQLServerManagementStudio创建DML触发器

【例10-6】为SCMS数据库中的teacher表创建INSERT触发器。

具体操作步骤如下：

(1)在“对象资源管理器”窗口中展开“数据库|SCMS|表”节点。

(2)选择并展开teacher节点，选择“触发器”节点。

(3)单击右键，在弹出的快捷菜单中选择“新建触发器”选项，如图10-9所示。

(4)在打开的查询编辑器窗口中编辑触发器创建语句。

(5)单击“执行”按钮。

图10-9创建触发器10.2.2修改DML触发器

使用SQLServerManagementStudio或ALTERTRIGGER语句可以修改一个已经存在的触发器。SQLServer2005可以在保留现有触发器名称的同时，修改触发器的触发动作和执行内容。

1.使用SQLServerManagementStudio修改DML触发器

【例10-7】修改在例10-5中创建的触发器tr_updateColumn，保证对sc表的成绩列更新是在0到100之间。具体操作步骤如下：

(1)在“对象资源管理器”窗口中展开“数据库|SCMS|表”节点。

(2)选择并展开sc节点，选择并展开“触发器”节点。

(3)选择要修改的触发器tr_updateColumn，单击右键，选择“修改”选项，如图10-10所示。

(4)打开查询编辑器窗口，对代码做修改，如图10-11所示。单击“执行”按钮，完成触发器的修改。图10-10修改触发器

图10-11修改触发器定义代码

2.使用ALTERTRIGGER语句修改DML触发器

使用ALTERTRIGGER语句修改DML触发器的语法格式如下：

ALTERTRIGGERtrigger_name

ONtable_name|view_name

[WITHENCRYPTION]

{FOR|AFTER|INSTEADOF}

{[INSERT][,][UPDATE][,][DELETE]}

AS

[IFUPDATE(column_name)…n]

[{AND|OR}UPDATE(column_name)…n]

sql_statement

其参数含义与创建触发器语句相同。

以下是修改tr_updateColumn触发器的代码：

ALTERTRIGGERtr_updateColumn

ONsc

FORUPDATE

AS

IFUPDATE(result)

BEGIN

DECLARE@scoreint

SELECT@score=resultFROMinserted --从inserted表中找到修改后的成绩

IF@score<0OR@score>100 --判断成绩是否在1到100之间

BEGIN

ROLLBACK --回滚修改操作

PRINT'成绩必须在1到100之间！'

END

END10.2.3删除DML触发器

删除一个触发器时，该触发器所关联的表和数据不会受到任何影响。删除触发器所在的表时，SQLServer将会自动删除与该表相关的触发器。删除DML触发器可以使用SQLServerManagementStudio，也可以使用DROPTRIGGER语句。

1.使用SQLServerManagementStudio删除DML触发器

【例10-8】删除sc表的tr_updateColumn触发器。

具体操作步骤如下：

(1)在“对象资源管理器”窗口中展开“数据库|SCMS|表”节点。

(2)选择并展开sc节点，选择并展开“触发器”节点。

(3)选择要删除的触发器tr_updateColumn，单击右键，选择“删除”选项。

(4)打开“删除对象”对话框，单击“确定”按钮，完成tr_updateColumn触发器的删除。

2.使用DROPTRIGGER语句删除DML触发器

使用DROPTRIGGER语句删除DML触发器的语法格式如下：

DROPTRIGGERtrigger_name[,...n]

以下是删除sc表的tr_updateColumn触发器的语句：

DROPTRIGGERtr_updateColumn

10.3.1DDL触发器概述

DDL触发器像DML触发器一样，在响应事件时执行触发器相应语句。但与DML触发器不同的是，它们并不响应对表或视图的UPDATE、INSERT或DELETE操作，它们主要响应数据定义语句(DDL)。这些语句包括CREATE、ALTER、DROP、GRANT、DENY及REVOKE等。执行DDL语句操作的系统存储过程也可以激活DDL触发器。10.3DDL触发器

1. DDL触发器的作用

DDL触发器的作用表现在以下几点：

(1)防止对数据库架构进行某些更改。

(2)响应数据库架构中的更改。

(3)记录数据库架构中的更改或事件。

2. DDL触发器的作用域

DDL触发器的作用域包括数据库范围和服务器范围。

(1)数据库范围。数据库范围内的DDL触发器都作为对象存储在创建它们的数据库中。

(2)服务器范围。服务器范围内的DDL触发器作为对象存储在master数据库中。

例如，当数据库中发生CREATE_TABLE事件时，都会激活为响应CREATE_TABLE事件创建的数据库范围的DDL触发器。每当服务器上发生CREATE_INDEX事件时，都会激活为响应CREATE_INDEX事件创建的服务器范围的DDL触发器。

3.与DML触发器的不同之处

DDL触发器与DML触发器的不同之处表现在以下几点：

(1)

DML触发器响应INSERT、UPDATE和DELETE语句操作。

(2)

DDL触发器响应CREATE、ALTER、DROP和其他DDL语句操作。

(3)

只有在完成Transact-SQL语句后才运行DDL触发器。DDL触发器无法作为INSTEADOF触发器使用。

(4)

DDL触发器不会创建inserted表和deleted表。但是可以使用EVENTDATA函数捕获有关信息。10.3.2创建DDL触发器

DDL触发器只能使用CREATETRIGGER语句来创建。语法格式如下：

CREATETRIGGERtrigger_name

ON{ALLSERVER|DATABASE}

[WITHENCRYPTION]

{FOR|AFTER}{event_type}[,...n]

AS

sql_statement

参数说明如下：●

trigger_name：触发器的名称。

●

ALLSERVER：将DDL触发器的作用域应用于当前服务器。如果指定了此参数，则只要当前服务器中的任何位置上出现event_type，就会激活该触发器。

●

DATABASE：将DDL触发器的作用域应用于当前数据库。如果指定了此参数，则只要当前数据库中出现event_type，就会激活该触发器。●

event_type：用于激活DDL触发器的DDL事件。每个事件都对应一个Transact-SQL语句。DDL事件名称是由SQL语句中的关键字以及在这些关键字之间所加的下划线(_)构成的。例如，删除表事件为DROP_TABLE，修改表事件为ALTER_TABLE，修改索引事件为ALTER_INDEX，删除索引事件为DROP_INDEX。

●

sql_statement：当触发器被触发后执行的语句。

【例10-9】创建触发器，禁止用户删除SCMS学生数据库中的表。

CREATETRIGGERtr_denyDropTable

ONDATABASE

FORDROP_TABLE

AS

PRINT'禁止删除数据表！'

ROLLBACK

BEGINTRAN

GO执行代码，tr_denyDropTable触发器创建成功。

测试触发器是否有效，将SCMS数据库中的scoreBackup表删除，执行结果如图10-12所示。

图10-12删除scoreBackup表

图10-13删除WZGL中的price_table表

结论：tr_denyDropTable触发器是一个数据库范围的触发器，与前面的DML触发器不同，该触发器是由用户的DROPTABLE操作激活的。该触发器的作用域是SCMS数据库，在SCMS数据库中删除数据表时激活。在WZGL数据库中进行表的删除时不会激活此触发器。

在“对象资源管理器”窗口中，可在“数据库|SCMS|可编程性|数据库触发器”节点处查看SCMS数据库中创建的数据库触发器。

【例10-10】创建一个触发器，禁止用户在当前服务器中创建、修改或删除数据库。

CREATETRIGGERtr_denyOperate

ONALLSERVER

FORCREATE_DATABASE,ALTER_DATABASE,DROP_DATABASE

AS

PRINT'不能在当前服务器中新建、修改和删除数据库！'

ROLLBACK

BEGINTRAN

GO

执行代码，tr_denyOperate触发器创建成功。

测试触发器是否有效，新建MyDB数据库，执行结果如图10-14所示。

图10-14创建MyDB数据库

结论：tr_denyOperate触发器是服务器范围触发器，该触发器可由创建数据库、修改数据库或删除数据库操作激活，该触发器的作用域是整个服务器。

在“对象资源管理器”窗口中的“服务器对象|触发器”节点处查看创建的服务器DDL触发器。10.3.3修改DDL触发器

在SQLServer中可以使用ALTERTRIGGER语句修改DDL触发器。DDL触发器的修改和DML触发器相似。

【例10-11】修改tr_denyDropTable触发器，要求禁止用户在SCMS数据库中删除或者新建表。

ALTERTRIGGERtr_denyDropTable

ONDATABASE

FORDROP_TABLE,CREATE_TABLE

AS

PRINT'不能修改数据库的结构！！'

ROLLBACK

BEGINTRAN

GO

执行代码，tr_denyDropTable触发器修改成功。10.3.4删除DDL触发器

在SQLServer中可以使用SQLServerManagementStudio或DROPTRIGGER语句删除DDL触发器。

1.使用SQLServerManagementStudio删除DDL触发器

【例10-12】删除例10-9创建的作用域为数据库的触发器tr_denyDropTable。

具体操作步骤如下：

(1)在“对象资源管理器”窗口中展开“数据库|SCMS|可编程性”节点。

(2)选择并展开“数据库触发器”节点，其中列出了所创建的数据库触发器。

(3)选择tr_denyDropTable，单击右键，选择“删除”选项，如图10-15所示。

(4)打开“删除对象”对话框，单击“确定”按钮即可删除tr_denyDropTable触发器。

【例10-13】删除例10-10创建的作用域为整个服务器的触发器tr_denyOperate。

具体操作步骤如下：

(1)在“对象资源管理器”窗口中，选择并展开“服务器对象|触发器”节点，列出了所创建的服务器触发器。

(2)选择tr_denyOperate，单击右键，选择“删除”选项，如图10-16所示。

(3)打开“删除对象”对话框，单击“确定”按钮即可删除tr_denyOperate触发器。

图10-15删除数据库触发器图10-16删除服务器触发器

2．使用DROPTRIGGER语句删除DDL触发器

使用DROPTRIGGER语句删除DDL触发器的语法格式如下：

DROPTRIGGERtrigger_name[,...n]ON{DATABASE|ALLSERVER}

其参数含义与创建DDL触发器相同。

删除tr_denyOperate触发器语句如下：

DROPTRIGGERtr_denyOperateONALLSERVER

10.4.1查看触发器

调用sp_helptrigger、sp_help、sp_helptext和sp_depends可分别查看触发器的不同信息。

1.调用sp_helptrigger显示DML触发器信息

调用sp_helptrigger显示DML触发器信息的语法格式如下：

sp_helptrigger'table'[,'type']10.4管 理 触 发 器参数说明如下：

●

table：要查看触发器的表的名称

●

type：要查看的触发器类型。其值可以为INSERT、UPDATE和DELETE。该选项为可选项。

其中，sp_helptrigger不能用于DDL触发器。

【例10-14】查看student表的触发器信息。

sp_helptriggerstudent

执行结果如图10-17所示。

图10-17调用sp_helptrigger查看触发器

2.调用sp_help显示触发器信息

调用sp_help显示触发器信息的语法格式如下：

sp_helptrigger_name

【例10-15】查看tr_updateColumn触发器的所有者和创建时间。

sp_helptr_updateColumn

执行结果如图10-18所示。

图10-18调用sp_help查看触发器

3.调用sp_helptext显示触发器的源代码

调用sp_helptext显示触发器的源代码的语法格式如下：

sp_helptexttrigger_name

【例10-16】查看tr_update触发器的创建语句。

sp_helptexttr_update

执行结果如图10-19所示。

图10-19调用sp_helptext查看触发器

4.调用sp_depends显示触发器参考的对象信息

调用sp_depends显示触发器参考的对象信息的语法格式如下：

sp_dependstrigger_name

【例10-17】查看tr_updateColumn触发器参考的对象信息。

sp_dependstr_updateColumn

执行结果如图10-20所示。

图10-20调用sp_depends查看触发器10.4.2禁用或启用触发器

默认情况下，创建触发器后会启用触发器。当不需要触发器工作时，可以删除或禁用触发器。禁用触发器不会删除该触发器，该触发器仍然作为对象存在于当前数据库中。但是，当执行引起触发器执行的相关Transact-SQL语句时，不会激活此触发器；可以使用SQLServerManagementStudio禁用触发器，也可使用DISABLETRIGGER语句禁用触发器；可以使用SQLServerManagementStudio重新启用被禁用的触发器，也可使用ENABLETRIGGER重新启用被禁用的触发器。

1.禁用触发器

1)使用SQLServerManagementStudio禁用触发器

【例10-18】将student表中的tr_insertInstead触发器禁用。

具体操作步骤如下：

(1)在“对象资源管理器”窗口中展开“数据库|SCMS|表”节点。

(2)选择并展开student节点，选择并展开“触发器”节点。

(3)选择tr_insertInstead触发器，单击右键，在弹出的快捷菜单中选择“禁用”选项，如图10-21所示。

(4)弹出“禁用触发器”消息框，单击“关闭”按钮。tr_insertInstead触发器被禁用。图10-21禁用触发器

2)使用DISABLETRIGGER语句禁用触发器

使用DISABLETRIGGER语句禁用触发器的语法格式如下：

DISABLETRIGGER{trigger_name[,...n]|ALL}

ON{object_name|DATABASE|ALLSERVER}

参数说明如下：

●

trigger_name：要禁用的触发器的名称。

●

ALL：指示禁用在ON子句作用域中定义的所有触发器。●

object_name：禁用的DML触发器所属的表或视图的名称。

●

DATABASE：对于DDL触发器，禁用的触发器范围是数据库范围。

●

ALLSERVER：对于DDL触发器，禁用的触发器范围是整个服务器范围。

禁用student表中的tr_insertInstead触发器代码如下：

DISABLETRIGGERtr_insertInsteadonstudent

【例10-19】将创建在SCMS数据库中的DDL触发器tr_denyDropTable禁用。

DISABLETRIGGERtr_denyDropTableON

DATABASE

2.启用触发器

启用触发器并不是要重新创建触发器。虽然禁用的触发器仍以对象形式存在于当前数据库中，但并不激活。启用触发器将导致它在其最初编程所在的任何Transact-SQL语句执行时被激活。

1)使用SQLServerManagementStudio启用被禁用的触发器

具体操作步骤和禁用触发器相似。只不过当选定触发器并单击右键后，在弹出的快捷菜单中选择“启用”选项。

2)使用ENABLETRIGGER语句启用被禁用的触发器

使用ENABLETRIGGER语句启用被禁用的触发器的语法格式如下：

ENABLETRIGGER{trigger_name[,...n]|ALL}

ON{object_name|DATABASE|ALLSERVER}

其参数含义与禁用触发器语法相同。

【例10-20】将禁用的tr_insertInstead触发器启用。

ENABLETRIGGERtr_insertInsteadONstudent

【例10-21】将禁用的tr_denyDropTable触发器启用。

ENABLETRIGGERtr_denyDropTableON

DATABASE10.4.3设置触发器的执行顺序

用户可以针对一张表创建多个触发器，通过调用sp_settriggerorder存储过程指定第一个被激活和最后一个被激活的AFTER触发器(DML或DDL)。其语法格式如下：

sp_settriggerordertrigger_name,'value','statement_type'

[,{'DATABASE'|'SERVER'|NULL}]

参数说明如下：

●

trigger_name：要设置或更改其顺序的DML或DDL触发器的名称。

●

value：触发器的新顺序的设置。可以是First、Last和Null值中的任意一个值。

●

statement_type：指定激活触发器的SQL语句。可以是INSERT、UPDATE、DELETE语句或用于DDL触发器的DDL事件中列出的任何Transact-SQL语句事件。不能指定事件组。

●

'DATABASE'|'SERVER'|NULL：如果trigger_name是DDL触发器，则指定trigger_name创建时的作用域是数据库作用域还是服务器作用域。如果未指定或指定为NULL，则trigger_name为DML触发器。

【例10-22】设置tr_scoreBackup第一个被执行。

sp_settriggerorder'tr_scoreBackup',First,'DELETE'

【例10-23】在SCMS数据库中，为了维护数据库中数据的完整性，当用户从student学生表中删除一行数据时，sc成绩表中对应学生的选课信息也被删除。10.5触发器的应用

分析：根据要求可以使用约束或触发器来实现，本例中选用触发器来实现。为student表创建一个DELETE触发器，SQLServer会为触发器建立一个临时表deleted。对于student表中被删除的数据，系统会将其存放在deleted表中，通过查看该表得到删除学生的学号，再将该学号对应的sc表中的记录删除。下面使用AFTER触发器和INSTEADOF触发器完成相同的操作，以加深对AFTER触发器和INSTEADOF触发器的理解。

(1)使用AFTER触发器。在查询编辑器窗口中输入以下代码：

CREATETRIGGERtr_delestu

ONstudent

FORDELETE

AS

DECLARE@snochar(12),@snamechar(10)

SELECT@sno=sno,@sname=snameFROMdeleted

--被删除的学生信息存放在deleted表中，所以从该表中读取学生的学号、姓名

PRINT'在student表中删除的是'+@sname+'的信息！'

--显示删除学生信息

DELETEFROMscWHEREsno=@sno

--将该学生的选课信息从sc表中删除

执行代码，tr_delestu触发器创建成功。

激活触发器，在student表上删除一条学生记录，代码如下：

DELETEFROMstudentWHEREsno='200903102103'

执行结果如图10-22所示。

图10-22删除student表中的一条记录其中，为了测试触发器的效果，首先查询sc表学号为200903102103的学生的选课信息，查询结果显