|
2005新增功能：查看与修改DDL触发器 DDL触发器有两种，一种是作用在当前SQL Server服务器上的，一种是作用在当前数据库中的。这两种DDL触发器在Management Studio中所在的位置是不同的。 l 作用在当前SQL Server服务器上的DDL触发器所在位置是：【对象资源管理器】，选择所在SQL Server服务器，定位到【服务器对象】à【触发器】，在【摘要】对话框里就可以看到所有的作用在当前SQL Server服务器上的DDL触发器。 l 作用在当前数据库中的DDL触发器所在位置是：【对象资源管理器】，选择所在SQL Server服务器，【数据库】，所在数据库，定位到【可编程性】à【数据库触发器】，在摘要对话框里就可以看到所有的当前数据库中的DDL触发器。 右击触发器，在弹出的快捷菜单中选择【编写数据库触发器脚本为】à【CREATE到】à【新查询编辑器对话框】，然后在新打开的【查询编辑器】对话框里可以看到该触发器的内容。 在Management Studio如果要修改DDL触发器内容，就只能先删除该触发器，再重新建立一个DDL触发器。 虽然在Management Studio中没有直接提供修改DDL触发器的对话框，但在【查询编辑器】对话框里依然可以用SQL语句来进行修改。下面给出几个对DDL触发器操作常用的SQL代码，由于对DDL触发器的操作和对DML触发器的操作类似，因此不再详细说明用法。 l 创建DDL触发器 CREATE TRIGGER (Transact-SQL) l 删除DDL触发器 DROP TRIGGER (Transact-SQL) l 修改DDL触发器 ALTER TRIGGER (Transact-SQL) l 重命名DDL触发器 sp_rename (Transact-SQL) l 禁用DDL触发器 DISABLE TRIGGER (Transact-SQL) l 启用DDL触发器 ENABLE TRIGGER (Transact-SQL) l 删除DDL触发器 DROP TRIGGER (Transact-SQL) 触发器的应用技巧 触发器的使用范围很广，使用的频率也很高，触发器的应用技巧也层出不穷，下面介绍一些在触发器里常用的技巧，希望可以做到抛砖引玉之功效。 如何知道触发器修改了多少条记录 需要注意的是，一种操作类型（Insert、Update或Delete）虽然可以激活多个触发器，但是每个操作类型在一次操作时，对一个触发器只激活一次。例如，运行一个Update语句，有可能一次更新了十条记录，但是对于After Update这个触发器，只激活一次，而不是十次。但是在Inserted表和Deleted表里会有十条记录，这个时候，只要利用@@Rowcount这个系统变量就可以得知更新了多少条记录。例如： CREATE TRIGGER 订单明细删除_test ON 订单明细 AFTER DELETE AS BEGIN print '您此次删除了' + Cast(@@rowcount as varchar) + '条记录' END GO Delete FROM 订单明细 where 折扣=0.25 GO Delete FROM 订单明细 where 订单ID='123456789' GO 这里先是建立了一个名为“订单明细删除_test”的触发器，作用就是显示删除了多少条记录。之后执行两个SQL语句，一个是删除折扣为0.25的记录，一个是删除订单ID号为123456789的记录，这条记录是不存在的。运行结果如图11.20所示： 图20 显示删除的记录数 在图20可以看出，用系统变量@@rowcount可以获得删除记录的条数。另外，在图中还可以看出，虽然第二个SQL语句删除的记录数为零，但是触发器还是被激活了。因此可以知道，触发器只与激活它的类型有关，与具体操作的记录数无关。 如何知道插入记录的自动编号是多少 在第11.7节，触发器的嵌套里，【类别】数据表设计了一个触发器，当在【类别】数据表里插入一件记录的时候，将会在【操作记录表】里也插入一条记录，用来记录具体的插入操作的，其实这个触发器还可以写得更好，不但可以记录插入操作所用的SQL语句，还可以记录下当时插入记录时候，数据库为这个记录自动生成编号是多少，为以后的操作提供更大的便利。修改该触发器的代码如下： ALTER TRIGGER 类别_Insert ON 类别 AFTER INSERT AS BEGIN Declare @类别名称 nvarchar(15), @说明 nvarchar(max) set @类别名称 = (Select 类别名称 from inserted) set @说明 = (Select 说明 from inserted) INSERT INTO 操作记录表 (操作表名,操作语句,操作内容) VALUES ('类别表','插入记录', '插入了ID号为'+cast(@@IDENTITY as varchar)+'的记录：类别名称：' +@类别名称+',说明：'+@说明) END GO 从上面的代码可以看出，用@@IDENTITY可以获得刚插入记录的标识值，在本例中是它的主键值。插入记录后，在【操作记录表】里可以详细查看到插入的记录的编号以及它的内容。 如何知道某个字段是否被修改 在Update触发器和Insert触发器里，可以用“Update(字段名)”来判断某个字段是不是被更改，返回的是一个布尔值。例如定单生成后，只能修改折扣的触发器： CREATE TRIGGER 只允许修改折扣 ON 订单明细 Instead Of UPDATE AS BEGIN SET NOCOUNT ON; if update(折扣) begin declare @订单ID int, @产品ID int, @折扣 real set @订单ID = (select 订单ID from inserted) set @产品ID = (select 产品ID from inserted) set @折扣 = (select 折扣 from inserted) update 订单明细 set 折扣=@折扣 where 订单ID=@订单ID and 产品ID=@产品ID end else begin print '只能更改折扣字段' end END GO update 订单明细 set 折扣=0.2 where 订单ID=10288 and 产品ID=54 Go update 订单明细 set 订单ID=10288 where 订单ID=10288 and 产品ID=54 Go 上面的代码，先建立了一个触发器，只有修改了折扣字段的Update语句才会被执行。然后写了两个Update的SQL语句，一个是修改了折扣字段的，一个是没有修改折扣字段的。运行后的结果如图11.21所示。第一个SQL语句被正确执行，第二个SQL语句没有被执行。 图21 用Update判断字段是否被修改 如何返回错误信息 虽然上面介绍触发器时，用过很多次Print来输出自定义的信息，但是实际上，只有在用【查询编辑器】中运行SQL语句才能看得到这些自定义的信息，而其他的前端应用程序都不会显示出这些自定义的信息，包括用Management Studio也一样。 读者可以自行测试一下，在Management Studio里打开【订单明细】数据表，因为上面建了一个【只允许修改折扣】的触发器，所以只要在不是折扣的字段里修改数据后，再将鼠标聚焦到其他记录上时，被修改的数据马上就会回滚到修改前的状态，在这个过程中，几乎是看不到什么提示的。如果想要在这个过程中看到提示的话，就要将触发器修改一下，加上“Raiserror”语句，具体修改代码如下： set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go ALTER TRIGGER 只允许修改折扣 ON 订单明细 Instead Of UPDATE AS BEGIN SET NOCOUNT ON; if update(折扣) begin declare @订单ID int, @产品ID int, @折扣 real set @订单ID = (select 订单ID from inserted) set @产品ID = (select 产品ID from inserted) set @折扣 = (select 折扣 from inserted) update 订单明细set 折扣=@折扣 where 订单ID=@订单ID and 产品ID=@产品ID end else begin print '只能更改折扣字段' Raiserror('除了折扣字段之外的其他字段信息不能修改',16,5) end END 修改完触发器之后，再去修改其他非“折扣”字段的内容时，就会弹出错误提示，如图11.22所示，Raiserror的用法可以查看SQL Server 2005的帮助。 图22 显示错误信息 小结 触发器是与数据库和数据表相结合的特殊的存储过程，当数据表有Insert、Update、Delete操作或数据库有Create、Alter、Drop操作的时候，可以激活触发器，并运行其中的T-SQL语句。 在SQL Server 2005中触发器分为DML触发器和DDL触发器两种。其中DML触发器又分为After触发器和Instead Of触发器两种。After触发器是先修改记录后激活的触发器；Instead Of触发器是“取代”触发器。DDL触发器根据作用范围可以分为作用在数据库的触发器和作用在服务器的触发器两种。After触发器只能用于数据表中，而Instead Of触发器即可以用在数据表中，也可以用在视图中。 使用CREATE TRIGGER语句可以创建触发器，使用ALTER TRIGGER语句可以修改触发器，使用Drop Trigger语句可以删除触发器。触发器允许嵌套和递归，嵌套最多可以是32层。 在下个章节里将会介绍索引的使用方法。 |
|
|
