sqlserver 进阶（五）触发器的使用
触发器是一种特殊类型的过程。
与普通过程不同的是，过程需要用户显式地调用才执行，
而触发器则是当某些事件发生时，由数据库自动执行，也就是隐式执行的。
1、触发器的组成
触发器中的临时表：
Inserted
存放进行 insert 和 update 操作后的数据
Deleted
存放进行 delete 和 update 操作前的数据
触发器触发时：
系统自动在内存中创建 deleted 表或 inserted 表；
只读，不允许修改，触发器执行完成后，自动删除。
inserted 表：
临时保存了插入或更新后的记录行；
可以从 inserted 表中检查插入的数据是否满足业务需求；
如果不满足，则向用户发送报告错误消息，并回滚插入操作。
deleted 表：
临时保存了删除或更新前的记录行；
可以从 deleted 表中检查被删除的数据是否满足业务需求；
如果不满足，则向用户报告错误消息，并回滚插入操作。
inserted 表和 deleted 表对照：
| 修改操作记录 | inserted 表 | deleted 表 |
|---|---|---|
| 增加 (insert) 记录 | 存放新增的记录 | ............ |
| 删除 (deleted) 记录 | .............. | 存放被删除的记录 |
| 修改 (update) 记录 | 存放更新后的记录 | 存放更新前的记录 |
你可以根据从下面方法判断触发器是在处理插入，删除还是更新时触发的：
代码如下:
--宣告两个变量
DECLARE @D BIT = 0
DECLARE @I BIT = 0
--如果在DELETED内部临时触发表找到记录，说明旧数据被删除
IF EXISTS(SELECT TOP 1 1 FROM DELETED)
SET @D = 1
--如果在INSERTED内部临时触发表找到记录，说明有新数据插入
IF EXISTS(SELECT TOP 1 1 FROM INSERTED)
SET @I = 1
--如果两个表都有记录，说明触发器是执行更新触发
IF @I = 1 AND @D = 1
PRINT(N'更新。')
--如果变量@I值被变更为1，而变量@D没有变更，说明触发器是执行插入触发
IF @I = 1 AND @D = 0
PRINT(N'插入')
--下面判断成立，说明说明触发器是执行删除触发
IF @I = 0 AND @D = 1
PRINT(N'删除')
另外有关两个内部临时触发表，触发器的 Inserted 表和 Deleted 表
触发器有两个虚拟表，Inserted 表和 Deleted 表, 这两个表在不同操作情况之下，表中的数据状态可不一样。
一、插入操作 (INSERT) 时：Inserted 表有数据，Deleted 表无数据。
二、更新操作 (UPDATE) 时：Inserted 表有数据(新数据)，Deleted 表有数据(旧数据)。
三、删除操作 (DELETE) 时：Inserted 表无数据，Deleted 表有数据。
2、触发器类型
行级触发器
语句级触发器
INSTEAD OF 触发器
系统事件触发器
3、创建触发器的语法
行级触发器语法：
CREATE [ OR ALTER ] TRIGGER [ schema_name . ]trigger_name
ON { table }
[ WITH <dml_trigger_option> [ ,...n ] ]
{ FOR | AFTER }
{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }
AS { sql_statement [ ; ] [ ,...n ] }
<dml_trigger_option> ::=
[ NATIVE_COMPILATION ]
[ SCHEMABINDING ]
[ EXECUTE AS Clause ]
语句级触发器语法：
CREATE [ OR ALTER ] TRIGGER trigger_name
ON { ALL SERVER | DATABASE }
[ WITH <ddl_trigger_option> [ ,...n ] ]
{ FOR | AFTER } { event_type | event_group } [ ,...n ]
AS { sql_statement [ ; ] [ ,...n ] | EXTERNAL NAME < method specifier > [ ; ] }
<ddl_trigger_option> ::=
[ ENCRYPTION ]
[ EXECUTE AS Clause ]
INSTEAD OF 触发器语法：
CREATE [ OR ALTER ] TRIGGER [ schema_name . ]trigger_name
ON { table | view }
[ WITH <dml_trigger_option> [ ,...n ] ]
{ FOR | AFTER | INSTEAD OF }
{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }
[ WITH APPEND ]
[ NOT FOR REPLICATION ]
AS { sql_statement [ ; ] [ ,...n ] | EXTERNAL NAME <method specifier [ ; ] > }
<dml_trigger_option> ::=
[ ENCRYPTION ]
[ EXECUTE AS Clause ]
<method_specifier> ::=
assembly_name.class_name.method_name
系统事件触发器语法：
CREATE [ OR ALTER ] TRIGGER trigger_name
ON ALL SERVER
[ WITH <logon_trigger_option> [ ,...n ] ]
{ FOR| AFTER } LOGON
AS { sql_statement [ ; ] [ ,...n ] | EXTERNAL NAME < method specifier > [ ; ] }
<logon_trigger_option> ::=
[ ENCRYPTION ]
[ EXECUTE AS Clause ]
参数：
CREATE OR ALTER：
创建或者有条件的修改触发器（即要修改的触发器必须已经存在）。
schema_name：
DML 触发器所属的模式的名称（即所有者，例如：dbo）。
trigger_name：
是触发器的名称。
table | view：
是执行 DML 触发器的表或视图，有时称为触发器表或触发器视图。指定表格或视图的完全限定名称是可选的。视图只能由 INSTEAD OF 触发器引用。
DATABASE：
将 DDL 触发器的范围应用于当前数据库。如果指定，触发器会在当前数据库中发生 event_type 或 event_group 时触发。
ALL SERVER：
将 DDL 或登录触发器的作用域应用于当前服务器。如果指定，触发器会在当前服务器的任何地方发生 event_type 或 event_group 时触发。
WITH ENCRYPTION：
加密 CREATE TRIGGER 语句的文本。使用 WITH ENCRYPTION 可以防止触发器作为 SQL Server 复制的一部分进行发布。无法为 CLR 触发器指定 WITH ENCRYPTION。
EXECUTE AS：
指定执行触发器的安全上下文。以便能够控制 SQL Server 实例用于验证触发器引用的任何数据库对象的权限的用户帐户。
NATIVE_COMPILATION：
表示触发器是本地编译的。
SCHEMABINDING：
指定触发器引用的表不能被删除或更改。
FOR | AFTER：
AFTER 指定仅在触发 SQL 语句中指定的所有操作成功执行时触发 DML 触发器。所有引用级联操作和约束检查在此触发器触发之前也必须成功。当 FOR 是指定的唯一关键字时，AFTER 是默认值。视图无法定义 AFTER 触发器。
INSTEAD OF：
指定执行 DML 触发器而不是触发 SQL 语句，因此覆盖触发语句的操作。无法为 DDL 或登录触发器指定 INSTEAD OF。
对于 INSTEAD OF 触发器，在具有指定级联动作 ON DELETE 的引用关系的表上不允许使用 DELETE 选项。类似地，在具有指定级联动作 ON UPDATE 的引用关系的表上，不允许 UPDATE 选项。
{[DELETE] [，] [INSERT] [，] [UPDATE]} ：
指定在针对此表或视图进行尝试时激活 DML 触发器的数据修改语句。必须至少指定一个选项。在触发器定义中允许以任何顺序对这些选项进行任意组合。
event_type：
是执行后导致 DDL 触发器触发的 Transact-SQL 语言事件的名称。
event_group：
是 Transact-SQL 语言事件的预定义分组的名称。属于任何 Transact-SQL 语言事件执行后的 DDL 触发器触发 event_group。
sql_statement：
是触发条件和动作。触发条件指定附加条件，以确定尝试的 DML，DDL 或登录事件是否导致执行触发器操作。
<method_specifier>：
对于 CLR 触发器，指定要与触发器绑定的程序集的方法。该方法不得不引用任何参数并返回 void。class_name 必须是有效的 SQL Server 标识符，并且必须作为具有程序集可见性的程序集中的类存在。
4、示例
例 1：
--创建触发器
Create trigger dept_onInsert
On dept
for insert
As
declare @msg nvarchar(50)
--@msg记录修改情况
select @msg = N'新增部门编号：“' + cast(Inserted.DEPTNO as varchar)+ N'”部门名称：“' + Inserted.DNAME + '”' from Inserted
--插入日志表
insert into [LOG](MSG)values(@msg)
--删除触发器
--drop trigger dept_onInsert
插入语句触发：
SET IDENTITY_INSERT DEPT ON
INSERT INTO [prc].[dbo].[DEPT] ([DEPTNO], [DNAME], [LOC]) VALUES ('16', '招商银行', '宁夏')
GO
SET IDENTITY_INSERT DEPT OFF
例 2：
--创建触发器
Create trigger deptUpdate
On DEPT
for Update
As
declare @msg nvarchar(50)
--@msg记录修改情况
select @msg = N'名字从“' + Deleted.DNAME + N'”修改为“' + Inserted.DNAME + '”' from Inserted,Deleted
--插入日志表
insert into [LOG](MSG)values(@msg)
更新语句触发：
update dept set dname='中信银行' where deptno='1'
例 3：
create trigger tig_dept_time
on DEPT
for insert, update , delete
as
declare @msg nvarchar(50)
begin
set language N'Simplified Chinese'
if( (select datename(weekday, getdate())) in ('星期六','星期日') or
getdate() not between (Select CONVERT(varchar(100), GETDATE(), 23)+' 08:30:00')
and (Select CONVERT(varchar(100), GETDATE(), 23)+' 18:0:00'))
begin
set @msg='不是上班时间，不允许修改dept表'
insert into [LOG](MSG)values(@msg)
end
else
begin
set @msg='上班时间，修改dept表'
insert into [LOG](MSG)values(@msg)
end
end
例 4：
--创建行级触发器
create trigger tig_emp_raiu
--触发对象
on emp
--触发时机
FOR
--触发类型，可以有多个，多个用or隔开如insert or update，只有当amount列被更新时，update才会被触发
insert , update
as
begin
--宣告两个变量
DECLARE @D BIT = 0
DECLARE @I BIT = 0
--如果在DELETED内部临时触发表找到记录，说明旧数据被删除
IF EXISTS(SELECT TOP 1 1 FROM DELETED)
SET @D = 1
--如果在INSERTED内部临时触发表找到记录，说明有新数据插入
IF EXISTS(SELECT TOP 1 1 FROM INSERTED)
declare @msg nvarchar(50)
SET @I = 1
if (@I = 1 AND @D = 0)
begin
set @msg='向emp表中插入数据'
end
else if ( @I = 1 AND @D = 1 )
begin
set @msg='emp表更新数据'
end
else
begin
set @msg='emp表删除数据'
end
insert into [LOG](MSG)values(@msg)
end
触发语句：
update emp set mgr='123' where deptno='1'
Instead of 类型触发器
--创建视图
CREATE VIEW emp_view AS
SELECT deptno, count(*) total_employeer, sum(sal) total_salary
FROM emp GROUP BY deptno
GO
--创建Instead of触发器
CREATE TRIGGER emp_view_delete
ON emp_view
INSTEAD OF DELETE
AS
BEGIN
declare @deptno int
select @deptno=deptno from Deleted
DELETE FROM emp WHERE deptno=@deptno
END
触发语句（删除 emp_view 中数据时，会同步删除 emp 中对应数据）：
delete from emp_view where deptno='4'
系统事件触发器
--创建登录触发器
CREATE OR ALTER TRIGGER tig_logon
ON ALL SERVER
AFTER LOGON
AS
BEGIN
/* Trigger action goes here. */
END
使用触发器限制数据库连接 IP
USE master GO
if exists (select * from sysobjects where name = 'tr_LoginCheck' and type='TR')
drop trigger dbo.tr_LoginCheck
GO
CREATE TRIGGER tr_LoginCheck ON ALL SERVER
FOR LOGON AS IF EVENTDATA().value('(/EVENT_INSTANCE/ClientHost)[1]', 'varchar(15)') <> '192.168.1.27'
ROLLBACK TRAN
GO
结果只有 192.168.1.27 能够登陆 SQL, 本地都无法登陆
删除该触发语句
使用 DAC 登陆执行删除触发器语句
使用 DAC 登陆执行删除触发器语句
1、进入 DOS 执行 sqlcmd -A -S localhost // 服务器名 当前示例是本机
2、执行删除触发器语句 DROP TRIGGER tr_LoginCheck ON ALL SERVER
3、输入 GO
4、 OK （有可能结果提示执行失败，但是实际已经删除触发器了 可以试试）
5、禁用和启用触发器
数据库 TRIGGER 的状态：
有效状态 (ENABLE)：当触发事件发生时，处于有效状态的数据库触发器 TRIGGER 将被触发。
无效状态 (DISABLE)：当触发事件发生时，处于无效状态的数据库触发器 TRIGGER 将不会被触发，此时就跟没有这个数据库触发器 (TRIGGER) 一样。
数据库 TRIGGER 的这两种状态可以互相转换。格式为：
ALTER TABLE [schema.]table_name {ENABLE|DISABLE} TRIGGER trigger_name ;
{ENABLE|DISABLE} trigger trigger_name on [schema.]table_name;
说明：
如果有多个触发器，则各个触发器名称之间用英文逗号隔开。
如果把“触发器名称”换成“ALL”，则表示禁用或启用该表的全部触发器。
例如：
ALTER TIGGER tig_logon DISABLE;
ALTER TIGGER tig_logon ENABLE;
ALTER TABLE emp DISABLE ALL TRIGGERS;
ALTER TABLE emp ENABLE ALL TRIGGERS;
6、修改触发器
--修改触发器语法
ALTER TRIGGER trigger_name
ON table_name
[ WITH ENCRYPTION ]
FOR {[DELETE][,][INSERT][,][UPDATE]}
AS
sql_statement;
7、删除触发器
语法格式:
DROP TRIGGER { trigger } [ ,...n ]
参数:
trigger: 要删除的触发器名称
n: 表示可以删除多个触发器的占位符
例 1：
DROP TRIGGER tig_logoff;
--禁用触发器
disable trigger dept_onInsert on DEPT;
--启用触发器
enable trigger dept_onInsert on DEPT;