T-SQL 语句(六)—— 触发器操作

2/13/2017来源:SQL技巧人气:815

T-SQL 语句(六)—— 触发器操作

一、触发器简介

1、触发器的功能:   a、实现比约束更为复杂的数据约束   b、可以检查 SQL 所做的操作是否被允许   c、修改其他数据库里表的数据   d、可以一次调用多个存储过程   e、发送SQL Mail   f、返回自定义错误信息   g、修改原来要操作的 SQL 语句   h、防止数据表结构更改或表被删除  2、触发器的种类主要分为DML 触发器和DDL 触发器两种,其中DML触发器又分为After 和 Instead Of 触发器

二、创建 DML 触发器
-- 一个表针对每个操作可以定义多个After 触发器,但是Instead Of触发器对每个操作只能建立一个 CREATE TRIGGER trigger_insert ON news AFTER INSERT AS PRINT '插入了一条记录' GO CREATE TRIGGER trigger_update ON news AFTER UPDATE AS PRINT '修改了一条记录' GO CREATE TRIGGER trigger_delete ON news AFTER DELETE AS PRINT '删除了一条记录' GO CREATE TRIGGER trigger_change ON [order] AFTER INSERT,UPDATE,DELETE AS EXEC master..xp_sendmail 'TOM','订单有更改' GO CREATE TRIGGER trigger_order_insert ON [order] AFTER INSERT,UPDATE AS IF (SELECT discount from INSERTED) > 0.6 BEGIN PRINT '折扣不能大于 0.6' ROLLBACK TRANSACTION END GO
三、触发器排序

使用 sp_settriggerorder 存储过程进行触发器排序,其参数均为字符串,参数一是触发器名称,参数二是激活触发器的顺序参数,可以为 FIRST,LAST和NONE,参数三是激活触发器的动作,可以是 INSERT,UPDATE 和 DELETE

CREATE TRIGGER trigger_update_age_employee ON employee AFTER UPDATE AS PRINT '更新员工年龄' GO CREATE TRIGGER trigger_update_name_employee ON employee AFTER UPDATE AS PRINT '更新员工姓名' GO UPDATE employee SET age = 10 WHERE id=1 -- 进行观察 EXEC sp_settriggerorder 'trigger_update_age_employee','LAST',UPDATE GO UPDATE employee SET age = 10 WHERE id=11 -- 触发器执行的顺序改变了
四、Instead Of 触发器

  After 触发器只能在数据表中使用,而 Instead Of 触发器可以在数据表和视图中使用。以下情况建议使用 Instead Of 触发器:   1、数据库的数据禁止修改,可以使用 Instead Of 触发器来跳过 Update 修改记录的 SQL 语句   2、有可能需要回滚修改的 SQL 语句,Instead Of 触发器的回滚效率比 After 高。   3、在视图中使用触发器   4、用自己的方式修改数据,使用 Instead Of 触发器来控制数据的修改方式和流程。

CREATE TRIGGER trigger_insert_employee  ON employee  INSTEAD OF INSERT AS PRINT 'employee 表插入数据' GO   EXEC sp_help 'trigger_insert_employee' -- 查看触发器信息 EXEC sp_helptext 'trigger_insert_employee' -- 查看触发器SQL文本
五、修改 DML 触发器

  修改触发器的语法与修改存储过程语法类似,只是将 CREATE 改变成 ALTER。触发器重命名使用 sp_name 存储过程

六、删除 DML 触发器
DROP TRIGGER trigger_insert_employee
七、启用和禁用 DML 触发器
ALTER TABLE employee DISABLE TRIGGER ALL -- 禁止 employee 的所有触发器 ALTER TABLE employee ENABLE TRIGGER trigger_insert_employee -- 启用某个触发器
八、创建 DDL 触发器

  DDL 触发器是SQL Server 2005 以后新增的触发器类型,它在响应 DDL 语句时触发,一般用于在数据库中执行管理任务。

CREATE TRIGGER trigger_t1 ON ALL SERVER -- DDL 触发器作用到当前服务器上所有数据库,ON 可以指定某个数据库对象 FOR DROP_TABLE,ALTER_TABLE --使用FOR或AFTER是一个意思,但DDL触发器不支持INSTEAD OF触发器。DROP_TABLE是激活 DDL 触发器的事件 AS PRINT '删除表或修改表结构!' -- 创建数据库 DDL 语句操作记录表 CREATE TABLE table_ddl_log( id INT NOT NULL IDENTITY(1,1) PRIMARY KEY, [event] NVARCHAR(100),-- 事件 [server] NVARCHAR(100), -- 服务器名 [database] NVARCHAR(100), -- 数据库名 [schema] NVARCHAR(100),-- 架构名 [sql] NVARCHAR(MAX), -- SQL语句 [Operator] NVARCHAR(100), [date] datetime2 DEFAULT getdate() ) GO -- 创建 DDL 语句记录触发器 ALTER TRIGGER trigger_ddl ON DATABASE -- 指定当前数据库 FOR DDL_DATABASE_LEVEL_EVENTS AS DECLARE @log xml SET @log = EVENTDATA() INSERT ddl_log VALUES( @log.value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(100)'), @log.value('(/EVENT_INSTANCE/ServerName)[1]','nvarchar(100)'), @log.value('(/EVENT_INSTANCE/DatabaseName)[1]','nvarchar(100)'), @log.value('(/EVENT_INSTANCE/SchemaName)[1]','nvarchar(100)'), @log.value('(/EVENT_INSTANCE/TSQLCommand)[1]','nvarchar(4000)'), CONVERT(nvarchar(100),CURRENT_USER), GETDATE() ); GO
九、触发器的使用

1、在触发器中使用 @@ROWCOUNT 获取此次数据操作受影响行数; 2、在触发器中使用 @@IDENTITY 获取插入记录的编号; 3、在UPDATE和INSERT触发器里可以使用 Update(字段名) 来判断某个字段是否被更改。

CREATE TRIGGER trigger_update_name_employee ON employee INSTEAD OF UPDATE AS SET NOCOUNT ON -- 不记录行数提高性能,这是个运行时设置语句,不是编译时语句 IF UPDATE(name) BEGIN PRINT '名字不能修改' RAISERROR('名字一旦确定不能修改',16,5) END GO UPDATE employee SET name = '张三' WHERE id = 1