SQL Server 2005教案之第八章触发器和数据操作

《SQL Server 2005教案之第八章触发器和数据操作》由会员分享，可在线阅读，更多相关《SQL Server 2005教案之第八章触发器和数据操作（28页珍藏版）》请在金锄头文库上搜索。

1、第第8章章 SQL Server 2005的的 触发器和数据操作触发器和数据操作 本章要点：本章要点：本章主要介绍了本章主要介绍了SQL Server 2005的触的触发器的概述、创建、修改和删除，触发器的概述、创建、修改和删除，触发器的工作原理以及数据的导入和导发器的工作原理以及数据的导入和导出等。触发器简介出等。触发器简介第第8章章 SQL Server 2005的的 触发器和数据操作触发器和数据操作 8.1触发器简介触发器简介8.2创建触发器创建触发器 8.3修改和删除触发器修改和删除触发器 8.4触发器的工作原理触发器的工作原理 8.5数据的导入和导出数据的导入和导出 8.1触发器简介

2、触发器简介8.1.1触发器概述触发器概述8.1.2触发器的触发方式触发器的触发方式 8.1.1触发器概述触发器概述触发器是一种特殊字段的存储过程，当表中数据被修改时，SQL Server自动执行触发器，使用触发器可以实施更为复杂的数据完整性约束。SQL Server中的触发器可以分为INSERT触发器、UPDATE触发器、DELETE触发器三种，分别由INSERT、UPDATE、DELETE语句触发。 8.1.2触发器的触发方式 SQL Server 2005 支持两种类型的触发方式：（1）AFTER触发器：只能在表上定义该类触发器，并且只有执行某些操作之后，才会触发。可以使用系统过程sp_s

3、ettriggerorder定义AFTER触发器的被触发顺序。（2）INSTEAD OF触发器：该类型触发器不执行INSERT、UPDATE、DELETE操作，而只是执行触发器本身。 8.2创建触发器 在创建触发器以前必须考虑到以下几个方面： （1）CREATE TRIGGER 语句必须是批处理的第一个语句。（2）表的所有者具有创建触发器的缺省权限，表的所有者不能把该权限传给其他用户。（3）触发器只能创建在当前数据库中，并且一个触发器只能对应一个表。（4）只能在永久表上创建触发器，不能在视图或临时表上创建触发器。（5）TRUNCATE TABLE、WRITETEXT语句不能触发INSERT或U

4、PDATE型的触发器。（6）触发器不允许带有参数，也不能直接调用，不许由表中的一定的数据变化被系统激活。8.2创建触发器 8.2.1 用用 SQL Server management studio向导创建触发器向导创建触发器8.2.2 用用Transact-SQL语句创建触发器语句创建触发器8.2.1 用用SQL Server management studio向导创建触发器向导创建触发器8.2.2用Transact-SQL语句创建触发器 在SQL Server中，通过执行CREATE TRIGGER语句来创建触发器，其语法格式为:CREATE TRIGGER trigger_nameON t

5、able| view WITH ENCRYPTION FOR | AFTER | INSTEAD OF INSERT , DELETE , UPDATE WITH APPEND NOT FOR REPLICATION AS IF UPDATE ( column ) AND | OR UPDATE ( column ) .n | IF ( COLUMNS_UPDATED ( ) bitwise_operator updated_bitmask ) comparison_operator column_bitmask n sql_statement n 【例【例8-1】在Test数据库的表Cust

6、omers上创建一个触发器，禁止更新和向此表中插入数据。如果更新和向此表中插入数据则引发错误。（1）首先定义一个错误信息：EXEC SP_ADDMESSAGE 50009,16,Please do not , English（2）创建触发器：USE TestIF EXISTS ( SELECT name FROM sysobjects WHERE name = TR1 AND type = TR) DROP TRIGGER TR1GOCREATE TRIGGER TR1ON CustomersFOR INSERT,UPDATEASRAISERROR (50009,16,10)ROLLBACKG

7、O8.3修改和删除触发器 8.3.1修改触发器 8.3.2删除触发器 8.3.1修改触发器 修改触发器的语句格式为：ALTER TRIGGER trigger_nameON table| view WITH ENCRYPTION FOR | AFTER | INSTEAD OF INSERT , DELETE , UPDATE NOT FOR REPLICATION AS sql_statement n | ( FOR | AFTER | INSTEAD OF ) INSERT , UPDATE NOT FOR REPLICATION AS IF UPDATE ( column ) AND |

8、 OR UPDATE ( column ) .n | IF ( COLUMNS_UPDATED ( ) bitwise_operator updated_bitmask ) comparison_operator column_bitmask n sql_statement n 【例例8-2】更改Test数据库的表Customers上创建的触发器TR1，加密此触发器。ALTER TRIGGER TR1ON CustomersWITH ENCRYPTIONFOR UPDATEASIF UPDATE(Region)BEGINPRINT Please do not update this COLRO

9、LLBACKENDGO8.3.2删除触发器 删除触发器的语句格式为：DROP TRIGGER trigger_name ,n【例例8-3】删除Test数据库的表Customers上创建的触发器TR1。USE TestIF EXISTS ( SELECT name FROM sysobjects WHERE name = TR1 AND type = TR) DROP TRIGGER TR18.4触发器的工作原理 8.4.1INSERT触发器工作原理 8.4.2DELETE触发器工作原理 8.4.3UPDATE触发器工作原理8.4.4INSTEAD OF触发器工作原理 INSTEAD OF触发器

10、与ATTER触发器的区别 8.4.1INSERT触发器工作原理 INSERT触发器的工作过程如图所示。 【例例8-4】在Test数据库的Orders表中定义触发器，在向Orders表中插入数据行时，检查在Employees表中是否有和要插入数据行相同的Employeeid，如果没有就不允许插入。USE TestIF EXISTS ( SELECT name FROM sysobjects WHERE name = ord AND type = TR ) DROP TRIGGER ordGOCREATE TRIGGER ordON ordersFOR INSERTASIF EXISTS ( SE

11、LECT employeeID FROM inserted WHERE employeeID NOT IN ( SELECT employeeID FROM employees ) )BEGIN PRINT the employees is not exits ROLLBACK tranEND8.4.2DELETE触发器工作原理 DELETE触发器的工作过程如所示。 【例例8-5】在Test数据库的Employees表中定义一个触发器，当Employees表中某一行被删除时，检查在Orders表中是否有和要删除数据行相同的Employeeid，如果有就不允许删除。USE TestIF EXIS

12、TS ( SELECT name FROM sysobjects WHERE name = em AND type = TR ) DROP TRIGGER emGOCREATE TRIGGER emON employeesFOR DELETEASIF EXISTS ( SELECT employeeID FROM deleted WHERE employeeID IN ( SELECT employeeID FROM orders ) )BEGIN PRINT the employees is exits in orders ROLLBACK tranEND8.4.3UPDATE触发器工作原理

13、触发器工作原理 UPDATE触发器的工作过程如所示。【例例8-6】创建一个触发器，当更新Products表的Unitisinstock列时，如果Unitisinstock列值小于零时不允许更新。USE TestGOIF EXISTS ( SELECT name FROM sysobjects WHERE name = checks AND type = TR ) DROP TRIGGER checksGOCREATE TRIGGER checksON productsFOR UPDATEASIF ( SELECT unitsinstock FROM inserted ) 0 BEGIN PRI

14、NT can not oversell products PRINT transaction has been cancelled ROLLBACK END删除Products表上的一个约束后验证触发器，删除约束命令为：ALTER TABLE productsDROP CONSTRAINT ck_unitsinstock验证触发器命令为：USE TestUPDATE productsSET unitsinstock=unitsinstock-30WHERE productid=2GO8.4.4 INSTEAD OF触发器工作原理 当在表或视力中定义了针对某一操作INSERT、DELETE和UP

15、DATE的INSTEAD OF类型触发器，且执行了相应的操作时，尽管触发器被触发，但相应的操作并不被执行，而运行的仅是触发器SQL语句。 8.4.5 INSTEAD OF触发器与 ATTER触发器的区别 【例例8-7】分别创建两个表tb和tb1，表结构相同，再在这两个表上分别创建前触发器和后触发器，将两者运行结果相比较。1. 创建表创建表tb，及其上的前触发器，及其上的前触发器命令语句如下：/*创建表tb*/CREATE TABLE tb (id int identity(1,1), a varchar(10)/*创建表tb上的前触发器*/CREATE TRIGGER tri_tb ON tb

16、 INSTEAD OF INSERTASBEGININSERT tb (a)SELECT a FROM inserted END/*验证此触发器*/insert tb(a)select a/*查看结果*/select * from tb运行结果如图8-18所示。2. 创建表创建表tb1，及其上的后触发器，及其上的后触发器命令语句如下：/*创建表tb1 */CREATE TABLE tb1 (id int identity(1,1), a varchar(10)/*创建表tb1上的前触发器*/CREATE TRIGGER tri_tb1 ON tb1 FOR INSERTASBEGININSER

17、T tb1(a)SELECT a FROM inserted END/*验证此触发器*/insert tb1(a)select a/*查看结果*/select * from tb18.5数据的导入和导出 8.5.1导入数据 8.5.2导出数据 小结小结触发器是一种特殊字段的存储过程，当表中数据被修改时，SQL Server自动执行触发器。使用触发器可以实施更为复杂的数据完整性约束，如果需要对数据库进行各种更新操作，即添加数据、修改数据以及删除数据，则需要通过INSERT、UPDATE、DELETE等语句来实现。使用数据导入/导出工具可以在不同的SQL Server之间传输数据库，在数据库被加载到数据仓库之前必须被转换成一致格式，转换是运行程序的结果，在对象存储到特定的目的地之前，它用于表示数据源信息，DTS包是对转换程序过程中所有工作的详细描述。