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