《Sql Server2005 Transact-SQL 新兵器学习总结之-DDL触发器》由会员分享，可在线阅读，更多相关《Sql Server2005 Transact-SQL 新兵器学习总结之-DDL触发器（9页珍藏版）》请在金锄头文库上搜索。
1、1.简介：Sql Server2005新增加了DDL触发器。与DML触发器不同的是，它们不会为响应针对表或视图的UPDATE、INSERT或DELETE语句而激发。相反，它们将为了响应各种数据定义语言(DDL)事件而激发。这些事件主要与以关键字CREATE、ALTER和DROP开头的Transact-SQL语句对应。执行DDL式操作的系统存储过程也可以激发DDL触发器。2.作用：如果要执行以下操作，可以使用DDL触发器： (1)要防止对数据库架构进行某些更改。(2)希望数据库中发生某种情况以响应数据库架构中的更改。 (3)要记录数据库架构中的更改或事件。3.DDL 触发器作用域：(1)数据库范
2、围:数据库范围内的DDL触发器都作为对象存储在创建它们的数据库中。(2)服务器范围:服务器范围内的DDL触发器作为对象存储在master数据库中。例如:当数据库中发生CREATE TABLE事件时，都会触发为响应CREATE TABLE事件创建的数据库范围DDL触发器。每当服务器上发生CREATE Index事件时，都会触发为响应CREATE Index事件创建的服务器范围DDL触发器。4.与DML触发器比较不同处：(1)DML触发器在INSERT、UPDATE和DELETE语句上操作。(2)DDL触发器在CREATE、ALTER、DROP和其他DDL语句上操作。(3)只有在完成Transac
3、t-SQL语句后才运行DDL触发器。DDL 触发器无法作为 INSTEAD OF 触发器使用。http:/ (4)DDL触发器不会创建插入(inserted)的和删除(deleted)的表.但是可以使用EVENTDATA函数捕获有关信息。5.例题：-例题1-服务器范围的DDL触发器IFEXISTS(SELECT*FROMsys.server_triggersWHEREname=TR_CREATEDATABASE)DROPTRIGGERTR_CREATEDATABASEONALLSERVER;GOCREATETRIGGERTR_CREATEDATABASEONALLSERVERFORCREATE
4、_DATABASEASPRINTDatabaseCreatedPRINTCONVERT(nvarchar(1000),EventData()GOhttp:/ -创建数据库db1CREATEDATABASEdb1;-收到下列消息Database CreatedCREATE_DATABASE2008-09-01T20:17:35.17052YANFA0YANFA0Administratordb1CREATE DATABASE db1;-例题2-服务器范围的DDL触发器IFEXISTS(SELECT*FROMsys.triggersWHEREname=NTR_DROPINDEXANDparent_c
5、lass=0)DROPTRIGGERTR_DROPINDEXONDATABASEGOCREATETRIGGERTR_DROPINDEXONDATABASEFORDROP_INDEXASPRINTDROPINDEXROLLBACKGO-建立表CREATETABLETableTest(idsINT,iNVARCHAR(100)GO-建立唯一索引CREATEUNIQUEINDEXindex_TableTestONTableTest(ids)GO-删除索引DROPINDEXindex_TableTestONTableTestGO-收到下列消息-我们从消息可以看到删除索引不成功！DROP INDEX消息
6、 3609，级别 16，状态 2，第 2 行事务在触发器中结束。批处理已中止。-例题3-当不再需要某个DDL触发器时，可以禁用或删除该触发器。-禁用DDL触发器不会将其删除。该触发器仍然作为对象存在于当前数据库中。-但是，当运行编写触发器程序所用的任何DDL语句时，不会激发触发器。-可以重新启用禁用的DDL触发器。DISABLETRIGGERTR_DROPINDEXONDATABASEGOENABLETRIGGERTR_DROPINDEXONDATABASEGOSql Server2005 Transact-SQL 新兵器学习总结之-PIVOT和UNPIVOT运算符1.简介 PIVOT通过将表
7、达式某一列中的唯一值转换为输出中的多个列来旋转表值表达式，并在必要时对最终输出中所需的任何其余列值执行聚合。UNPIVOT与PIVOT执行相反的操作，将表值表达式的列转换为列值。通俗简单的说：PIVOT就是行转列，UNPIVOT就是列传行2.例题-建立销售表CREATETABLESell(YearINT,QuarterNVARCHAR(10),QuantityINT)-插入测试数据INSERTINTOSellVALUES(2006,Q1,20)INSERTINTOSellVALUES(2006,Q2,15)INSERTINTOSellVALUES(2006,Q2,4)INSERTINTOSel
8、lVALUES(2006,Q3,12)INSERTINTOSellVALUES(2006,Q4,18)INSERTINTOSellVALUES(2007,Q1,10)INSERTINTOSellVALUES(2007,Q2,10)INSERTINTOSellVALUES(2008,Q1,8)INSERTINTOSellVALUES(2008,Q2,7)INSERTINTOSellVALUES(2008,Q3,5)INSERTINTOSellVALUES(2008,Q3,10)INSERTINTOSellVALUES(2008,Q4,9)GO-得到每年每季度的销售总数SELECT*FROMSel
9、lPIVOT(SUM(Quantity)FORQuarterIN(Q1,Q2,Q3,Q4)ASPGO-查询得如下结果-注意：-如果子组为空，SQL Server生成空值。如果聚合函数是COUNT，且子组为空，则返回零。 Year Q1 Q2 Q3 Q42006 20 19 12 182007 10 10 NULL NULL20088 7 15 9其实PIVOT在sql2000中可以用SELECT.CASE语句来实现，下面是sql2000的代码：-sql2000静态版本SELECTyear,SUM(CASEWHENQuarter=Q1THENQuantityEND)ASQ1,SUM(CASEWH
10、ENQuarter=Q2THENQuantityEND)ASQ2,SUM(CASEWHENQuarter=Q3THENQuantityEND)ASQ3,SUM(CASEWHENQuarter=Q4THENQuantityEND)ASQ4FROMsellGROUPBYyear-sql2000动态版本DECLAREsqlNVARCHAR(2000)SELECTsql=selectyearSELECTsql=sql+,sum(casewhenQuarter=+Quarter+thenQuantityend)as+QuarterFROMsellGROUPBYQuarterORDERBYQuarterA
11、SCselectsql=sql+fromsellgroupbyyearexecute(sql)UNPIVOT将与PIVOT执行几乎完全相反的操作，将列转换为行。-创建测试表CREATETABLETestUNPIVOT(IDINT,A1NVARCHAR(10),A2NVARCHAR(10),A3NVARCHAR(10)-插入测试数据INSERTINTOTestUNPIVOTVALUES(1,q1,q2,q3)INSERTINTOTestUNPIVOTVALUES(2,q1,p1,m1)INSERTINTOTestUNPIVOTVALUES(3,t1,p1,m1)GO-UNPIVOTSELECTID,A,ValueFROM(SELECTID,A1
