数据库SQL触发器实例讲解

1、SQL触发器实例讲解1SQL触发器实例1定义：何为触发器？在SQLServer里面也就是对某一个表的一定的操作，触发某种条件，从而执行的一段程序。触发器是一个特殊的存储过程。常见的触发器有三种：分别应用于Insert,Update,Delete事件。我为什么要使用触发器？比如，这么两个表：CreateTableStudent(-学生表StudentIDintprimarykey,-学号)CreateTableBorrowRecord(-学生借书记录表BorrowRecordintidentity(1,1),-流水号StudentIDint,-学号BorrowDatedatetime,-借出时间

2、ReturnDAteDatetime,-归还时间）用到的功能有：1 .如果我更改了学生的学号，我希望他的借书记录仍然与这个学生相关（也就是同时更改借书记录表的学号）；2 .如果该学生已经毕业，我希望删除他的学号的同时，也删除它的借书记录。等等。这时候可以用到触发器。对于1,创建一个Update触发器:CreateTriggertruStudentOnStudent-在Student表中创建触发器forUpdate-为什么事件触发As-事件触发后所要做的事情ifUpdate(StudentID)beginUpdateBorrowRecordSetStudentID=i.StudentIDFrom

3、BorrowRecordbr,Deletedd,Insertedi-Deleted和Inserted临时表Wherebr.StudentID=d.StudentIDend理解触发器里面的两个临时的表：Deleted,Inserted。注意Deleted与Inserted分别表示触发事件的表汨的一条记录”和新的一条记录”。一个数据库系统中有两个虚拟表用于存储在表中记录改动的信息，分别是：虚拟表Inserted虚拟表Deleted在表记录新增时存放新增的记录不存储记录修改时存放用来更新的新记录删除时不存储记录存放更新前的记录存放被删除的记录一个Update的过程可以看作为：生成新的记录到Inser

4、ted表，复制旧的记录到Deleted表，然后删除Student记录并写入新纪录。对于2,创建一个Delete触发器CreatetriggertrdStudentOnStudentforDeleteAsDeleteBorrowRecordFromBorrowRecordbr,DelteddWherebr.StudentID=d.StudentID从这两个例子我们可以看到了触发器的关键：A.2个临时的表；B.触发机制。SQL触发器实例2/*建立虚拟测试环境，包含：表卷烟库存表,表卷烟销售表。请大家注意跟踪这两个表的数据，体会触发器到底执行了什么业务逻辑，对数据有什么影响。为了能更清晰的表述触发器

5、的作用，表结构存在数据冗余，且不符合第三范式，这里特此说明。*/USEMasterGOIFEXISTS(SELECTNAMEFROMSYSOBJECTSWHEREXTYPE='U'ANDNAME=卷烟库存表)DROPTABLE卷烟库存表GOIFEXISTS(SELECTNAMEFROMSYSOBJECTSWHEREXTYPE='U'ANDNAME=卷烟销售表)DROPTABLE卷烟销售表GO-业务规则：销售金额销售数量销售单价业务规则CREATETABLE卷烟销售表(卷烟品牌VARCHAR(40)PRIMARYKEYNOTNULL,购货商VARCHAR(40)N

6、ULL,销售数量INTNULL,销售单价MONEYNULL,销售金额MONEYNULL)GO- -业务规则：库存金额=库存数量*库存单价业务规则。CREATETABLE卷烟库存表(卷烟品牌VARCHAR(40)PRIMARYKEYNOTNULL,库存数量INTNULL,库存单价MONEYNULL,库存金额MONEYNULL)GO- -创建触发器，示例1/*创建触发器T_INSERT_卷烟库存表,这个触发器较简单。说明：每当卷烟库存表发生INSERT动作，则引发该触发器。触发器功能：强制执行业务规则，保证插入的数据中，库存金额=库存数量*库存单价。注意：INSERTED、DELETED为系统表，

7、不可创建、修改、删除，但可以调用。重要：这两个系统表的结构同插入数据的表的结构。*/IFEXISTS(SELECTNAMEFROMSYSOBJECTSWHEREXTYPE='TR'ANDNAME='T_INSERT_卷烟库存表)DROPTRIGGERT_INSERT_卷烟库存表GOCREATETRIGGERT_INSERT_卷烟库存表ON卷烟库存表FORINSERTAS- -提交事务处理BEGINTRANSACTION- -强制执行下列语句，保证业务规则UPDATE卷烟库存表SET库存金额=库存数量*库存单价WHERE卷烟品牌IN（SELECT卷烟品牌fromINSER

8、TED）COMMITTRANSACTIONGO/*针对卷烟库存表,插入测试数据：注意，第一条数据（红塔山新势力）中的数据符合业务规则，第二条数据（红塔山人为峰）中，库存金额空，不符合业务规则，第三条数据（云南映像）中，库存金额不等于库存数量乘以库存单价,不符合业务规则第四条数据库存数量为0。请注意在插入数据后，检查卷烟库存表中的数据是否库存金额=库存数量*库存单价。* /INSERTINTO卷烟库存表（卷烟品牌，库存数量，库存单价，库存金额）SELECT'红塔山新势力，100,12,1200UNIONALLSELECT'红塔山人为峰,100,22,NULLUNIONALLSEL

9、ECT'云南映像，100,60,500UNIONALLSELECT'玉溪',0,30,0GO* -查询数据SELECT*FROM卷烟库存表GO/*结果集RecordId卷烟品牌库存数量库存单价库存金额1红塔山新势力10012.00001200.00002红塔山人为峰10022.00002200.00003云南映像10060.00006000.00004玉溪030.0000.0000（所影响的行数为4行）* /* 触发器示例2/*创建触发器T_INSERT_卷烟销售表,该触发器较复杂。说明：每当卷烟库存表发生INSERT动作，则引发该触发器。触发器功能：实现业务规则。业务

10、规则：如果销售的卷烟品牌不存在库存或者库存为零，则返回错误。否则则自动减少卷烟库存表中对应品牌卷烟的库存数量和库存金额。ANDNAME*/IFEXISTS（SELECTNAMEFROMSYSOBJECTSWHEREXTYPE='TR'='T_INSERT_卷烟销售表）DROPTRIGGERT_INSERT_卷烟销售表GOCREATETRIGGERT_INSERT_卷烟销售表ON卷烟销售表FORINSERTASBEGINTRANSACTION- -检查数据的合法性：销售的卷烟是否有库存，或者库存是否大于零IFNOTEXISTS（SELECT库存数量FROM卷烟库存表WHE

11、RE卷烟品牌IN（SELECT卷烟品牌FROMINSERTED）BEGIN- -返回错误提示RAISERROR（'错误！该卷烟不存在库存，不能销售。'，16,1）- -回滚事务ROLLBACKRETURNENDIFEXISTS（SELECT库存数量FROM卷烟库存表WHERE卷烟品牌IN（SELECT卷烟品牌FROMINSERTED）AND库存数量=0）BEGIN- -返回错误提示RAISERROR（'错误！该卷烟库存小于等于0,不能销售。',16,1）- -回滚事务ROLLBACKRETURNEND-对合法的数据进行处理- -强制执行下列语句，保证业务规则UP

12、DATE卷烟销售表SET销售金额=销售数量*销售单价WHERE卷烟品牌IN（SELECT卷烟品牌FROMINSERTED）DECLARE卷烟品牌VARCHAR（40）SET卷烟品牌=（SELECT卷烟品牌FROMINSERTED）DECLARE销售数量MONEYSET销售数量=（SELECT销售数量FROMINSERTED）UPDATE卷烟库存表SET库存数量=库存数量-销售数量，库存金额=（库存数量-销售数量）*库存单价WHERE卷烟品牌=卷烟品牌COMMITTRANSACTIONGO- -请大家自行跟踪卷烟库存表和卷烟销售表的数据变化。- -针对卷烟销售表,插入第一条测试数据，该数据是正常

13、的。INSERTINTO卷烟销售表（卷烟品牌，购货商，销售数量，销售单价，销售金额）SELECT'红塔山新势力某购货商,10,12,1200GO- -针对卷烟销售表，插入第二条测试数据，该数据销售金额不等于销售单价*销售数量- -触发器将自动更正数据，使销售金额等于销售单价*销售数量。INSERTINTO卷烟销售表（卷烟品牌，购货商，销售数量，销售单价，销售金额）SELECT'红塔山人为峰某购货商,1022,2000GO- -针对卷烟销售表,插入第三条测试数据，该数据中的卷烟品牌在卷烟库存表中找不到对应。- -触发器将报错。INSERTINTO卷烟销售表（卷烟品牌，购货商，销售

14、数量，销售单价，销售金额）SELECT'红¥V8'某购货商,10,60,600GO/*结果集服务器：消息50000,级别16，状态1,过程T_INSERT_卷烟销售表，行15错误！该卷烟不存在库存，不能销售。*/- -针对卷烟销售表,插入第三条测试数据，该数据中的卷烟品牌在卷烟库存表中库存为0- -触发器将报错。INSERTINTO卷烟销售表(卷烟品牌，购货商，销售数量，销售单价，销售金额)SELECT'玉溪'某购货商',10,30,300GO/*结果集服务器：消息50000,级别16，状态1,过程T_INSERT_卷烟销售表，行29错误！该卷

15、烟库存小于等于0,不能销售。- /- -查询数据SELECT*FROM卷烟库存表SELECT*FROM卷烟销售表GO/*补充：1、本示例主要通过一个简单的业务规则实现来进行触发器使用的说明，具体的要根据需要灵活处理；2、关于触发器要理解并运用好INSERTED,DELETED两个系统表；3、本示例创建的触发器都是FORINSERT,具体的语法可参考：Trigger语法CREATETRIGGERtrigger_nameONtable|viewWITHENCRYPTION-用于加密触发器FOR|AFTER|INSTEADOFINSERT,UPDATEWITHAPPENDNOTFORREPLICAT

16、IONASIFUPDATE(column)AND|ORUPDATE(column)n|IF(COLUMNS_UPDATED()bitwise_operatorupdated_bitmask)comparison_operatorcolumn_bitmask.nsql_statement.n)4、关于触发器，还应该注意(1)、DELETE触发器不能捕获TRUNCATETABLE语句。、触发器中不允许以下Transact-SQL语句：ALTERDATABASECREATEDATABASEDISKINITDISKRESIZEDROPDATABASELOADDATABASELOADLOGRECONFI

17、GURERESTOREDATABASERESTORELOG(3)、触发器最多可以嵌套32层。- /- -修改触发器- -实质上，是将CREATETRIGGER.修改为ALTERTRIGGER.即可- -删除触发器DROPTRIGGERxxxGO- -删除测试环境DROPTABLE卷烟库存表GODROPTABLE卷烟销售表GODROPTRIGGERT_INSERT_卷烟库存表GODROPTRIGGERT_INSERT_卷烟销售表GO#触发器的基础知识和例子:createtriggertr_nameontable/viewfor|after|insteadofupdate,insert儿delet

18、ewithencryptionasbatch|ifupdate(col_name)and|orupdate(col_name)说明：1 tr_name:触发器名称2 ontable/view:触发器所作用的表。一个触发器只能作用于一个表3 for和after:同义4 after与insteadof:sql2000新增项目afrer与insteadof的区别After在触发事件发生以后才被激活，只可以建立在表上Insteadof代替了相应的触发事件而被执行，既可以建立在表上也可以建立在视图上5 insert、update、delete:激活触发器的三种操作，可以同时执行，也可选其一6 ifupd

19、ate(col_name):表明所作的操作对指定列是否有影响，有影响，则激活触发器。此外，因为delete操作只对行有影响，所以如果使用delete操作就不能用这条语句了(虽然使用也不出错，但是不能激活触发器，没意义)。7触发器执行时用到的两个特殊表：deleted.inserteddeleted和inserted可以说是一种特殊的临时表，是在进行激活触发器时由系统自动生成的，其结构与触发器作用的表结构是一样的，只是存放的数据有差异。续下面表格说明deleted与inserted数据的差异deleted与inserted数据的差异Inserted存放进行insert和update操作后的数据D

20、eleted存放进行delete和update操作前的数据注意：update操作相当于先进行delete再进行insert,所以在进行update操作时，修改前的数据拷贝一条到deleted表中，修改后的数据在存到触发器作用的表的同时，也同时生成一条拷贝到insered表中接(一)-触发器示例2/*创建触发器T_INSERT_卷烟销售表,该触发器较复杂。说明：每当卷烟库存表发生INSERT动作，则引发该触发器。触发器功能：实现业务规则。业务规则：如果销售的卷烟品牌不存在库存或者库存为零，则返回错误。否则则自动减少卷烟库存表中对应品牌卷烟的库存数量和库存金额。*/IFEXISTS(SELECTN

21、AMEFROMSYSOBJECTSWHEREXTYPE='TR'ANDNAME='T_INSERT_卷烟销售表)DROPTRIGGERT_INSERT_卷烟销售表GOCREATETRIGGERT_INSERT_卷烟销售表ON卷烟销售表FORINSERTASBEGINTRANSACTION- -检查数据的合法性：销售的卷烟是否有库存，或者库存是否大于零IFNOTEXISTS（SELECT库存数量FROM卷烟库存表WHERE卷烟品牌IN（SELECT卷烟品牌FROMINSERTED）BEGIN- -返回错误提示RAISERROR（'错误！该卷烟不存在库存，不能销售。

22、，16,1）- -回滚事务ROLLBACKRETURNENDIFEXISTS（SELECT库存数量FROM卷烟库存表WHERE卷烟品牌IN（SELECT卷烟品牌FROMINSERTED）AND库存数量=0）BEGIN- -返回错误提示RAISERROR（'错误！该卷烟库存小于等于0,不能销售。,16,1）- -回滚事务ROLLBACKRETURNEND- -对合法的数据进行处理- -强制执行下列语句，保证业务规则UPDATE卷烟销售表SET销售金额=销售数量*销售单价WHERE卷烟品牌IN（SELECT卷烟品牌FROMINSERTED）DECLARE卷烟品牌VARCHAR（40）SET

23、卷烟品牌=（SELECT卷烟品牌FROMINSERTED）DECLARE销售数量MONEYSET销售数量=（SELECT销售数量FROMINSERTED）UPDATE卷烟库存表SET库存数量=库存数量-销售数量，库存金额=（库存数量-销售数量）*库存单价WHERE卷烟品牌=卷烟品牌COMMITTRANSACTIONGO- -请大家自行跟踪卷烟库存表和卷烟销售表的数据变化。- -针对卷烟销售表,插入第一条测试数据，该数据是正常的。INSERTINTO卷烟销售表（卷烟品牌，购货商，销售数量，销售单价，销售金额）SELECT'红塔山新势力某购货商，10,12,1200GO- -针对卷烟销售表

24、，插入第二条测试数据，该数据销售金额不等于销售单价*销售数量。- -触发器将自动更正数据，使销售金额等于销售单价*销售数量。INSERTINTO卷烟销售表（卷烟品牌，购货商，销售数量，销售单价，销售金额）SELECT'红塔山人为峰某购货商,10,22,2000GOSQL拥有很多可用于计数和计算的内建函数。函数的语法内建SQL函数的语法是：SELECTfunction（歹U）FROM表函数的类型在SQL中，基本的函数类型和种类有若干种。函数的基本类型是:*Aggregate函数- Scalar函数合计函数(AggregatefunctionsAggregate函数的操作面向一系列的值，并

25、返回一个单一的值。注释：如果在SELECT语句的项目列表中的众多其它表达式中使用SELECT语句，则这个SELECT必须使用GROUPBY语句！"Persons"table（在大部分的例子中使用过）NameAgeAdams,John38Bush,George33Carter,Thomas28MSAccess中的合计函数函数描述AVG(column)返回某列的平均值COUNT(column)返回某列的行数（不包括NULL值）COUNT(*)返回被选行数FIRST(column)返回在指定的域中第一个记录的值LAST(column)返回在指定的域中最后一个记录的值MAX(col

26、umn)返回某列的最高值MIN(column)返回某列的最低值STDEV(column)STDEVP(column)SUM(column)返回某列的总和VAR(column)VARP(column)在SQLServer中的合计函数函数描述AVG(column)返回某列的行数BINARY_CHECKSUMCHECKSUMCHECKSUM_AGGCOUNT(column)返回某列的行数（不包括NULL值）COUNT(*)返回被选行数COUNT(DISTINCTcolumn)返回相异结果的数目FIRST(column)返回在指定的域中第一个记录的值（SQLServer2000不支持）LAST(col

27、umn)返回在指定的域中最后一个记录的值（SQLServer2000不支持）MAX(column)返回某列的最高值MIN(column)返回某列的最低值STDEV(column)STDEVP(column)SUM(column)返回某列的总和VAR(column)VARP(column)SQLServer游标简介与使用说明游标(Cursor)是处理数据的一种方法，为了查看或者处理结果集中的数据，游标提供了在结果集中一次以行或者多行前进或向后浏览数据的能力。我们可以把游标当作一个指针，它可以指定结果中的任何位置，然后允许用户对指定位置的数据进行处理。1 .游标的组成游标包含两个部分：一个是游标结

28、果集、一个是游标位置。游标结果集：定义该游标得SELECT语句返回的行的集合。游标位置：指向这个结果集某一行的当前指针。2 .游标的分类游标共有3类：API服务器游标、Transaction-SQL游标和API客户端游标。其中前两种游标都是运行在服务器上的，所以又叫做服务器游标。API服务器游标API服务器游标主要应用在服务上，当客户端的应用程序调用API游标函数时，服务器会对API函数进行处理。使用API函数和方法可以实现如下功能：(1)打开一个连接。(2)设置定义游标特征的特性或属性，API自动将游标影射到每个结果集。(3)执行一个或多个Transaction-SQL语句。(4)使用API

29、函数或方法提取结果集中的行。API服务器游标包含以下四种：静态游标、动态游标、只进游标、键集驱动游标(Primarykey)静态游标的完整结果集将打开游标时建立的结果集存储在临时表中，(静态游标始终是只读的)。静态游标具有以下特点：总是按照打开游标时的原样显示结果集；不反映数据库中作的任何修改，也不反映对结果集行的列值所作的更改；不显示打开游标后在数据库中新插入的行；组成结果集的行被其他用户更新，新的数据值不会显示在静态游标中；但是静态游标会显示打开游标以后从数据库中删除的行。动态游标与静态游标相反，当滚动游标时动态游标反映结果集中的所有更改。结果集中的行数据值、顺序和成员每次提取时都会改变。

30、只进游标不支持滚动，它只支持游标从头到尾顺序提取数据行。注意：只进游标也反映对结果集所做的所有更改。键集驱动游标同时具有静态游标和动态游标的特点。当打开游标时，该游标中的成员以及行的顺序是固定的，键集在游标打开时也会存储到临时工作表中，对非键集列的数据值的更改在用户游标滚动的时候可以看见，在游标打开以后对数据库中插入的行是不可见的，除非关闭重新打开游标。Transaction-SQL游标该游标是基于DeclareCursor语法，主要用于Transaction-SQL脚本、存储过程以及触发器中。Transaction-SQL游标在服务器处理由客户端发送到服务器的Transaction-SQL语

31、句。在存储过程或触发器中使用Transaction-SQL游标的过程为：(1)声明Transaction-SQL变量包含游标返回的数据。为每个结果集列声明一个变量。声明足够大的变量来保存列返回的值，并声明变量的类型为可从数据类型隐式转换得到的数据类型。(2)使用DeclareCursor语句将Transaction-SQL游标与Select语句相关联。还可以利用DeclareCursor定义游标的只读、只进等特性。(3)使用Open语句执行Select语句填充游标。(4)使用FetchInto语句提取单个行，并将每列中得数据移至指定的变量中。注意：其他Transaction-SQL语句可以引用

32、那些变量来访问提取的数据值。Transaction-SQL游标不支持提取行块。(5)使用Close语句结束游标的使用。注意：关闭游标以后，该游标还是存在，可以使用Open命令打开继续使用，只有调用Deallocate语句才会完全释放。客户端游标该游标将使用默认结果集把整个结果集高速缓存在客户端上，所有的游标操作都在客户端的高速缓存中进行。注意：客户端游标只支持只进和静态游标。不支持其他游标。3.游标的生命周期游标的生命周期包含有五个阶段：声明游标、打开游标、读取游标数据、关闭游标、释放游标。声明游标是为游标指定获取数据时所使用的Select语句，声明游标并不会检索任何数据,它只是为游标指明了相

33、应的Select语句。Declare游标名称Cursor参数声明游标的参数(1)Local与Global:Local表示游标的作用于仅仅限于其所在的存储过程、触发器以及批处理中、执行完毕以后游标自动释放。Global表示的是该游标作用域是整个会话层。由连接执行的任何存储过程、批处理等都可以引用该游标名称，仅在断开连接时隐性释放。(2)Forward_only与Scroll:前者表示为只进游标，后者表示为可以随意定位。默认为前者。(3)Static、Keyset与Dynamic:临时表内，对游标的所有请求都从临时表中应答，反映对基表所作的修改，并且该游标不允许修改。游标中行的身份与顺序是固定的，

34、并把其放到临时表中游标反映对结果集内所有数据的更改。第一个表示定义一个游标，其数据存放到一个因此，对该游标进行提取操作时返回的数据不Keyset表示的是，当游标打开时，键集驱动Dynamic表示的是滚动游标时，动态(4)Read_only、Scroll_Locks与Optimistic:第一个表示的是只读游标，第二个表示的是在使用的游标结果集数据上放置锁，当行读取到游标中然后对它们进行修改时，数据库将锁定这些行，以保证数据的一致性。Optimistic的含义是游标将数据读取以后，如果这些数据被更新了，则通过游标定位进行的更新与删除操作将不会成功。标准游标：DeclareMyCursorCurs

35、orForSelect*FromMaster_Goods只读游标DeclareMyCusrorCursorForSelect*FromMaster_GoodsForReadOnly可更新游标DeclareMyCusrorCursorForSelect*FromMaster_GoodsForUpDate打开游标使用Open语句用于打开Transaction-SQL服务器游标，执行Open语句的过程中就是按照Select语句进行填充数据，打开游标以后游标位置在第一行。打开游标全局游标：OpenGlobalMyCursor局部游标：OpenMyCursor读取游标数据：在打开游标以后，使用Fetch

36、语句从Transaction-SQL服务器游标中检索特定的一行。使用Fetch操作，可以使游标移动到下一个记录，并将游标返回的每个列得数据分别赋值给声明的本地变量。FetchNext|Prior|First|Last|Absoluten|RelativenFromMyCursorIntoGoodsID,GoodsName其中:Next表示返回结果集中当前行的下一行记录，如果第一次读取则返回第一行。默认的读取选项为NextPrior表示返回结果集中当前行的前一行记录，如果第一次读取则没有行返回，并且把游标置于第一行之前。First表示返回结果集中的第一行，并且将其作为当前行。Last表示返回结果集中的最后一行，并且将其作为当前行。Absoluten如果n为正数，则返回从游标头开始的第n行，并且返回行变成新的当前行。如果n为负，则返回从游标末尾开始的第n行，并且返回行为新的当前行，如果n为0,则返回当前行。Relativen如果n为正数，则返回从当前行开始的第n行，如果n为负，则返回从当前行之前的第n行，如果为0，则返回当前行。关闭游标调用的是Close语句，方式如下：CloseGlobalMyCursorCloseMyCursor释放游标调用的是Dealloca