48. SQL- 触发器之DML 触发器(1)

触发器

 

触发器分类

在SQLServer 中，触发器可以分为两大类：DML 触发器和DDL 触发器

DML 触发器：

DML触发器是当数据库服务器中发生数据操作语言（Data Manipulation

Language）事件时执行的存储过程。

DML 触发器又分为两类：After 触发器和Instead Of 触发器

○1 、After 触发器：这类触发器是在记录已经改变完之后（after），才会被激活执行，它主要是用于记录变更后的处理或检查，一旦发现错误，也可以用RollbackTransaction 语句来回滚本次的操作。即AFTER 触发器在执行INSERT、UPDATE或DELETE 语句的操作之后执行

○2 、InsteadOf 触发器：这类触发器一般是用来取代原本的操作，在记录变更之前发生的，它并不去执行原来SQL 语句里的操作（Insert、Update、Delete），

而去执行触发器本身所定义的操作。INSTEADOF 触发器代替INSERT,UPDATE 或DELETE语句执行

 

 

触发器工作原理

 

DML 触发器工作原理：

在SQLServer 里，为每个DML 触发器都定义了两个特殊的表，一个是插入表

（Inserted），一个是删除表（Deleted）。这两个表是建在数据库服务器的内存中的，是由系统管理的逻辑表，而不是真正存储在数据库中的物理表。对于这两个表，用户只有读取的权限，没有修改的权限。这两个表的结构与触发器所在数据表的结构是完全一致的，当触发器的工作完成之后，这两个表也将会从内存中删除。

 

插入表里存放的是更新前的记录：对于插入记录操作来说，插入表里存放的是要插

入的数据；对于更新记录操作来说，插入表里存放的是要更新的记录。

删除表里存放的是更新后的记录：对于更新记录操作来说，删除表里存放的是更新

前的记录（更新完后即被删除）；对于删除记录操作来说，删除表里存入的是被删除的旧记录。

○.After 触发器的工作原理

After触发器是在记录变更完之后才被激活执行的。以删除记录为例：当SQL

Server接收到一个要执行删除操作的SQL 语句时，SQL Server 先将要删除的记录存放在删除表里，然后把数据表里的记录删除，再激活After 触发器，执行After触发器里的SQL 语句。执行完毕之后，删除内存中的删除表，退出整个操作。

 

DEMO:在产品库存表里，如果要删除一条产品记录，在删除记录时，触发器可以检查该产品库存数量是否为零，如果不为零则取消删除操作。看一下数据库是怎么操作的：

（1）接收SQL 语句，将要从产品库存表里删除的产品记录取出来，放在删除表里。

（2）从产品库存表里删除该产品记录。

（3）从删除表里读出该产品的库存数量字段，判断是不是为零，如果为零的话，

完成操作，从内存里清除删除表；如果不为零的话，用RollbackTransaction 语句来回滚操作。

○2 、InsteadOf 触发器的工作原理

InsteadOf 触发器与After 触发器不同。After 触发器是在Insert、Update和Delete

操作完成后才激活的，而Instead Of 触发器，是在这些操作进行之前就激活了，并且不再去执行原来的SQL 操作，而去运行触发器本身的SQL 语句。

 

 

设计DML 触发器的注意事项及技巧

设计触发器的限制

在触发器中，有一些SQL 语句是不能使用的，这些语句包括：不能使用的语句语句功能

 

Alter Database 修改数据库

Create Database 新建数据库

Drop Database 删除数据库

Load Database 导入数据库

Load Log 导入日志

Reconfigure 更新配置选项

Restore Database 还原数据库

Restore Log 还原数据库日志

另外，在对作为触发操作的目标的表或视图使用了下面的SQL 语句时，不允许在DML触发器里再使用这些语句：

INSERT, UPDATE,或者DELETE 语句

触发器在另一张表上执行INSERT,UPDATE 或者DELETE

不能使用的语句语句功能

Create Index 建立索引

Alter Index 修改索引

Drop Index 删除索引

DBCC Dbreindex 重新生成索引

Alter Partition Function 通过拆分或合并边界值更改分区

Drop Table 删除数据表

Alter Table 修改数据表结构

 

如何在触发器取得字段修改前和修改后的数据

之前介绍过，SQL Server 在为每个触发器都定义了两个虚拟表，一个是插入表

（inserted），一个是删除表（deleted），现在把这两个表存放的数据列表说明一下：

激活触发器的动作Inserted 表 Deleted表

Insert 存放要插入的记录

Update 存放要更新的记录 存放更新前的旧记录

Delete 存放要删除的旧记录

以上面删除库存产品记录为例，在删除时触发器要判断库存数量是否为零，那么判断就应该这么写：

If(Select 库存数量From Deleted)>0

Begin

Print库存数量大于零时不能删除此记录

RollbackTransaction

End

其它注意事项

After 触发器只能用于数据表中，Instead Of 触发器可以用于数据表和视图上，但两种触发器都不可以建立在临时表上。

一个数据表可以有多个触发器，但是一个触发器只能对应一个表。

在同一个数据表中，对每个操作（如Insert、Update、Delete）而言可以建立许多个After 触发器，但Instead Of 触发器针对每个操作只有建立一个。

如果针对某个操作即设置了After触发器又设置了Instead Of 触发器，那么Instead of触发器一定会激活，而After 触发器就不一定会激活了。

Truncate Table 语句虽然类似于Delete 语句可以删除记录，但是它不能激活Delete类型的触发器。因为Truncate Table 语句是不记入日志的。

WRITETEXT 语句不能触发Insert 和Update 型的触发器。

不同的SQL 语句，可以触发同一个触发器，如Insert 和Update语句都可以激活同一个触发器。

 

创建ALTER触发器

语法：

CREATETRIGGER <Schema_Name, sysname, Schema_Name>.<Trigger_Name, sysname,

Trigger_Name>

ON <Schema_Name, sysname, Schema_Name>.<Table_Name, sysname, Table_Name>

AFTER<Data_Modification_Statements, , INSERT,DELETE,UPDATE>

AS

BEGIN

--SET NOCOUNT ON added to prevent extra result sets from

--interfering with SELECT statements.

SETNOCOUNT ON;

--Insert statements for trigger here

END

GO

转换成中文：

CREATETRIGGER 触发器名

ON 数据表名或视图名

AFTERINSERT或DELETE或UPDATE

AS

BEGIN

--这里是要运行的SQL语句

END

GO

参数说明：

CREATE TRIGGER 触发器名：这一句声明SQL 语句是用来建立一个触发器。其中触发器名在所在的数据库里必须是唯一的。由于触发器是建立中数据表或视图中的，所以有很多人都 以为只要是在不同的数据表中，触发器的名称就可以相同，其实触发器的全名（Server.Database.Owner.TriggerName）是必须 唯一的，这与触发器在哪个数据表或视图无关。

ON 数据表名或视图名：这是指定触发器所在的数据表或视图，但是请注意，只有Instead Of 触发器才能建立在视图上。并且，有设置为With Check Option 的视图也不允许建立InsteadOf 触发器。

AFTER INSERT 或 DELETE或UPDATE：这是指定触发器的类型，是After Insert触发器，还是AfterDelete 触发器，或者是After Update 触发器。其中After 可以用For 来代取，它们的意思都是一样的，代表只有在数据表的操作都已正确完成后才会激活的触发器。INSERT、 DELETE和UPDATE 至少要指定一个，当然也可以指定多个，若指定多个时，必须用逗号来分开。其顺序可以任意摆放。

With Encryption：WithEncryption 是用来加密触发器的，放在“On 数据表名或视图名”的后面，“For”的前面。如果使用了这句话，该触发器将会被加密，任何人都看不到触发器的内容了。

DEMO：

○1、创建一个insert 触发器

--查看原表数据：

use demo_db

go

select * from orders

--1、创建一个结构与orders一样的空白测试表ordermail,

select *

into ordermail

from orders

where 1=2

--2、创建一个触发器，当插入一条数据时，从inserted（临时表）中插入一条数据到ordersmail表中

create trigger tr_sendordmail

on orders

forinsert

as

insert into ordermail

select * from inserted

go

--3、插入一条数据：

insert into orders

select 'a01','2011-09-04','cisco','CH','5000'

--4、查看触发结果：

select * from ordermail

分析：

执行insert 插入语句，向表中插入数据行；

触发insert 触发器，向系统临时表inserted 表中插入新行的备份（副本）

触发器检查inserted 表中插入的新行数据，产生一个触发操作，向表ordermail 中写入一行数据

○2、创建一个DELETE 触发器

--1、创建一个触发器，当删除一条数据时，插入一条数据到ordersmial

create trigger tr_sendordmaila

on orders

for delete

as

insert into ordermail

select * from deleted

go

--2、删除数据：

delete Orders

where cust='cisco'

--3、看触发结果：

select * from ordermail

select * from Orders

分析：

执行delete 删除语句，删除表中的数据行；

触发delete 删除触发器，向系统临时表的deleted 表中插入被删除的副本

触发器检查deleted 表中被删除的数据，产生一个触发操作，向表ordermail 中写入一行数据

○3、创建一个UPDATE 触发器

--删除表ordermial中的数据

delete ordermail

where docno='a01'

select * from ordermail

对比在使用UPDATE 触发器时，从inserted及deleted 表插入数据对比

在使用UPDATE触发器时，从inserted插入数据

--1、创建触发器，当表orders执行一个update操作时，触发操作从inserted表中插入数据到ordermail

中

create trigger tr_sendordmaild

on orders

for update

as

insert into ordermail

select * from inserted

go

--2、更新一条数据

update Orders set cust ='linkcom'

go

--3、查看触发结果

select * from ordermail

select * from Orders

返回结果：

从上可以看到，表ordermail 中插入的数据是upadate 后的数据

在使用UPDATE 触发器时，从deleted 插入数据

--1、创建触发器，当表orders执行一个update操作时，触发操作从deleted表中插入数据到ordermail中

create trigger tr_sendordmaile

on orders

for update

as

insert into ordermail

select * from deleted

go

--2、更新一条数据

update Orders set cust ='cisco'

go

--3、查看触发结果

select * from ordermail

select *  from Orders

返回：

从上可以看出，表ordermail 中插入的数据是update 前及update后的数据

○4、创建一个列级UPDATE 触发器，当列新某一列时，触发某一动作

--删除之前创建的触发器

drop trigger tr_sendordmail

go

drop trigger tr_sendordmaila

go

drop trigger tr_sendordmaild

go

drop trigger tr_sendordmaile

go

--清空表ordermial 中的数据

delete ordermail

where cust in ('cisco','linkcom')

--1、创建一个触发器，当列新表orders 的cust 列时，触发一个操作，从deleted 表中插入数据到order mail表中

create trigger tr_sendordmail

on orders

for update

as

if update (cust)

begin

insert into ordermail

select * from inserted

end

go

--2、同时更新2 条数据，对列docno 及列cust 进行更新

update Orders

set docno = 'a01'

where docno = 'p02'

update Orders

set cust ='MOF'

where docno = 'a01'

go

--3、查看触发结果：

select * from ordermail

select * from Orders

可以看出，以上同时对docno及cust 进行更新，但只有更新列cust 时，才触发触发器执行一条插入操作.

○5、当更新某一列某个字段某个值时，触发一个条件，插入ordersmail 一条数据

--1、创建触发器，当列新表orders中列cust中字段值为aaa时，触发触发器，执行一条插入操作

alter trigger tr_sendordmail

on orders

for update

as

if update (cust)

begin

if exists (select top 1 1 from inserted where cust ='aaa')

begin

insert into ordermail

select * from inserted

end

end

go

--2、同时更新2 条数据，对列cust 进行更新

update Orders

set cust ='mod'

where docno = 'p03'

go

update Orders

set cust ='aaa'

where docno = 'p03'

go

--3、查看触发结果：

select * from ordermail

select * from Orders

可以看出，以上同时对cust进行2 条数据更新，但只有更新列cust 值为aaa 时，才触发触发器执行一条插入操作.

简单实例： DML触发器之Insert

 

--判断如果存在表，删除

Use tempdb

If object_id('dbo.T1_Audit','U') IS NOT NULL  DROP TABLE dbo.T1_Audit;

If object_id('dbo.T1','U') IS NOT NULL DROP TABLE dbo.T1;

 

 

--建立两个测试表

CREATE TABLE dbo.T1

(

      keycol INT NOT NULL PRIMARY KEY,

      datacolvarchar(10) NOT NULL

);

 

CREATE TABLE dbo.T1_Audit

(

      Audit_lsn  INT NOT NULL IDENTITY PRIMARY KEY,

      Dt        datetime NOT NULL DEFAULT (CURRENT_TIMESTAMP),

    Login_name sysname NOT NULLDEFAULT(SUSER_SNAME()),

    Keycol int not null,

    Datacol VARCHAR(10)  NOT NULL

);

 

--创建INSERT触发器

CREATE TRIGGER trg_T1_INSERT_Audit ondbo.T1 AFTER INSERT

AS

SET NOCOUNT ON;

INSERT INTO dbo.T1_Audit(keycol,datacol)

Select keycol, datacol frominserted;

Go

 

--测试触发器是否触发

Insert into dbo.T1(keycol,datacol) values(200,'a');

Insert into dbo.T1(keycol,datacol) values(400,'x');

Insert into dbo.T1(keycol,datacol) values(500,'g');

 

--查询测试结果

Select audit_lsn, dt, login_name, keycol, datacol

From dbo.T1_Audit