触发器的特征：

　　1、触发器是在对表进行增、删、改时，自动执行的存储过程。触发器常用于强制业务规则，它是一种高级约束，通过事件进行触发而被执行。

　　2、触发器是一个特殊的事务单元，可以引用其他表中的列执行特殊的业务规则或数据逻辑关系。当出现错误时，可以执行rollback transaction操作将整个触发器以及触发它的T-SQL语句一并回滚（不需显示声明begin transaction）。

　　3、每个触发器将用到的两个临时表：

　　deleted 临时表：用于临时存放被删除的记录行副本(包括delete和update语句所影响的数据行)；

　　注意：被删除的记录行，首先从原始表中删除，并保存到触发器表。然后从触发器表中删除，再保存到deleted表。

　　inserted临时表：用于临时存放插入的记录行副本(包括insert和update语句所影响的数据行)；

　　deleted表和inserted表的特征：

　　> 这两个表的表结构与该触发器作用的表相同；

　　> 这两个表是逻辑表，并且由系统管理；

　　> 这两个表是动态驻留在内存中的(不是存储在数据库中)，当触发器工作完成后，它们也被删除；

　　> 这两个表是只读的，即只能运用select语句查看(用户不能直接更改)；

　　4、所创建的触发器(insert、delete、update)是在原表数据行已经修改完成后再触发。所以，触发器是在约束检查之后才执行。

　　什么时候使用触发器？

　　a、实现主外键关系所不能保证的复杂参照完整性和数据的一致性。

　　不过，通过“级联引用完整性约束”可以更有效地执行这些更改。

　　b、防止恶意或错误的 INSERT、UPDATE 以及 DELETE 操作，并强制执行比 CHECK 约束定义的限制更为复杂的其他限制。

　　> 与 CHECK 约束不同(check约束只能引用自身表中的列)，DML触发器可以引用其他表中的列；

　　> 触发器可以完成所有约束的功能，但不一定是最佳方案；

　　> 触发器能够使用自定义信息和较为复杂的错误处理；

　　c、DML 触发器可以评估数据修改前后表的状态，并根据该差异采取措施。

　　d、一个表中的同一个修改语句的DML触发器，允许被多个不同的操作（INSERT、UPDATE 或 DELETE）来响应；

　　触发器的类型：

　　insert 触发器；(略)

　　delete 触发器；(略)

　　update 触发器：在修改表中记录行或某列数据时触发执行；

　　注意：update(列)函数：实现检测某列是否被修改。

　　update 更新操作分为两步：

　　首先，“删除”更改前原有数据行：删除的原有数据行将复制到deleted临时表中；

　　然后，“插入”更改后的新数据行：插入新数据行到原始表，同时将新数据行保存到inserted临时表和触发器表中；

　　创建触发器的注意点：

　　1、create trigger必须是批处理(go)的第一条语句；

　　2、一个触发器语句只能用到一个表或一个视图中；

　　on 表名/ 视图名

　　3、一个触发器语句可以执行多个操作；

　　for delete,insert,update -- 无先后顺序的任意组合

　　4、建议DML触发器不返回任何结果。这是因为对这些返回结果的特殊处理必须写入每个允许对触发器表进行修改的应用程序中。

　　若要防止从 DML 触发器返回任何结果，请不要在触发器定义中包含select语句或变量赋值；

　　如果必须在触发器中进行变量赋值，则应该在触发器被触发之前使用set nocount on语句以避免返回任何结果集；

　　注意：未来版本的SQL Server 中，将会删除从触发器返回结果集的功能。

　　5、如果“触发器表”本身也存在约束，则在执行insert、delete、update触发器前，首先会检查“触发器表”上存在的约束。如果不满足约束，则不会执行其insert、delete、update触发器。

　　查看当前数据库中的所有触发器

　　select * from sys.triggers

　　创建临时表 #tableName

　　create table #tableName

　　如何使用 SQL Server 触发器

　　触发器2_初始化环境SQL

　　初始化环境

---------------　初始化环境　---------------
create　database　TriggerDatabase
use　TriggerDatabase
go
if　exists(select　*　from　sysobjects　where　name='bank')
　　　drop　table　bank
create　table　bank　--　账户信息表
(
　　　userName　　　　　　varchar(10)　not　null,　　--顾客名
　　　cardID　　　　　　　　varchar(10)　not　null,　　--卡号
　　　currentMoney　　money　　　　　　　not　null　　　--当前余额
)
if　exists(select　*　from　sysobjects　where　name='transInfo')
　　　drop　table　transInfo
create　table　transInfo　--交易信息表
(
　　　cardID　　　　　varchar(10)　not　null,　　--卡号
　　　transType　　char(4)　　　　　not　null,　　--交易类型(存入/支取)
　　　transMoney　money　　　　　　　not　null,　　--交易金额
　　　transDate　　datetime　　　　not　null　　　--交易日期
)
go
---------------　添加约束　---------------
alter　table　bank
add　constraint　CK_currentMoney　check(currentMoney>=1);
alter　table　transInfo
add　constraint　DF_transDate　default(getdate())　for　transDate;
alter　table　transInfo
add　constraint　CK_transType　check(transType　in('支取','存入'));
---------------　添加测试数据　---------------
/*　张三　1000元　*/
insert　into　bank(userName,cardID,currentMoney)
　　　　　　　　values('张三','1001　0001',1000);
/*　李四　1元　*/
insert　into　bank(userName,cardID,currentMoney)
　　　　　　　　values('李四','1001　0002',1);
/*　张三　支取　200元　*/
insert　into　transInfo(cardID,transType,transMoney)
　　　　　　　　values('1001　0001','支取',200);
---------------　查看结果　---------------
select　*　from　bank;
select　*　from　transInfo;
go

　　触发器3_定义触发器的格式

　　定义触发器的格式

--　=============================================
--　Author:　　　　　　　　xugang
--　Create　date:　2010-2-14
--　Description:　　　　定义触发器的精简格式
--　　[　]：可选　　　　　{　}必选
--　=============================================
create　trigger　[　schema_name.　]　--　触发器所属架构
　　　　　　　　　　　　　　　trigger_name　　　　　--　触发器名称
on　{　table　|　view　}　　　　　　　--　触发器的表或视图
　　　[　with　encryption　]　　　　--　加密dml触发器定义(后面详解)
{　for　|　after　}　
　　　/*　after：只有在触发它的SQL语句执行成功后才能激发。
　　　　　　　　　　　　　(只能对“表”定义after)　*/
　　　　{　insert,update,delete　}　
as
　　　　/*　SQL语句...　*/
go

　　--查看当前数据库中的所有触发器

　　select * from sys.triggers

　　触发器4_insert 触发器SQL

　　insert 触发器

------------------　insert　触发器　------------------
use　TriggerDatabase
go
if　exists(select　*　from　sysobjects　
　　　　　　　　　　　where　name='trig_insert_transInfo')
drop　trigger　trig_insert_transInfo
go
--　create　trigger必须是批处理(go)的第一句
create　trigger　trig_insert_transInfo
on　transInfo　for　insert
as
　　　　declare　@_transType　　　char(4),　　--定义变量
　　　　　　　　　　　　@_transMoney　　money,
　　　　　　　　　　　　@_cardID　　　　　　char(10),
　　　　　　　　　　　　@balance　　　　　　money　　　　　--所剩余额
　　　　--　从inserted临时表中获取记录值
　　　　select　@_transType　=　transType,
　　　　　　　　　　　@_transMoney　=　transMoney,
　　　　　　　　　　　@_cardID　=　cardID　
　　　　　　　　　　　from　inserted
　　　　if(@_transType　=　'支取')
　　　　　　　update　bank　set　currentMoney=currentMoney-@_transMoney
　　　　　　　　　　　　　　where　cardID　=　@_cardID;
　　　　else　
　　　　　　　update　bank　set　currentMoney=currentMoney+@_transMoney
　　　　　　　　　　　　　　where　cardID　=　@_cardID;
　　　　--显示交易金额
　　　　print　'交易成功！　交易金额：'
　　　　　　　　　　+　convert(varchar(20),@_transMoney)
　　　　--显示所剩余额
　　　　select　@balance　=　currentMoney　from　bank　
　　　　　　　　　　　where　cardId　=　@_cardID
　　　　print　'卡号：'+@_cardID　
　　　　　　　　　　+　'　余额：'+convert(varchar(20),@balance);
go
　
------------------　测试触发器　------------------
--　delete　from　transInfo
set　nocount　on　--不显示T-SQL影响的记录行数
insert　into　transInfo(cardID,transType,transMoney)
　　　　　　　　　　　　　values('1001　0001','支取',200);
insert　into　transInfo(cardID,transType,transMoney)
　　　　　　　　　　　　　values('1001　0001','存入',10000);
--查看结果
select　*　from　bank
select　*　from　transInfo

　　触发器5_delete 触发器SQL

　　delete 触发器

/*　实现:　当清除'交易信息表'的数据时，
　　　　　　　　　自动备份被清除的数据到backupTable表中
*/
------------------　delete　触发器　------------------
use　TriggerDatabase
go
if　exists　(select　*　from　sysobjects　
　　　　　　　　　　　where　name='trig_delete_transInfo')
drop　trigger　trig_delete_transInfo
go
create　trigger　trig_delete_transInfo
on　transInfo　after　delete　　--　　for　|　after
as
　　　print　'开始备份数据，请稍后......'
　　　--　如果数据库中，不存在　backupTable　表
　　　if　not　exists(select　*　from　sysobjects　
　　　　　　　　　　　　　　　　　　where　name='backupTable')
　　　　　　select　*　into　backupTable　from　deleted　--deleted临时表
　　　else
　　　　　　insert　into　backupTable　select　*　from　deleted
　　
　　　print　'备份成功，备份表　backupTable　中的数据为：'
　　　　　　　　select　*　from　backupTable;
go

------------------　测试触发器　------------------
set　nocount　on
delete　from　transInfo;　--测试
--查看结果
select　*　from　transInfo
select　*　from　backupTable

　　触发器6_update 触发器SQL

　　update 触发器

------------------　update　触发器　------------------
use　TriggerDatabase
go
if　exists　(select　*　from　sysobjects　
　　　　　　　　　　　where　name='trig_update_bank')
drop　trigger　trig_update_bank
go
create　trigger　trig_update_bank
on　bank　for　update　　--在bank表上创建update触发器
as
　　　declare　@beforeMoney　money,
　　　　　　　　　　　@afterMoney　　money,
　　　　　　　　　　　@currentTrans　money　--当前交易金额
　　　--从deleted临时表，获取交易前的余额
　　　select　@beforeMoney　=　currentMoney　from　deleted;
　　　--从inserted临时表，获取交易后的余额
　　　select　@afterMoney　=　currentMoney　from　inserted;
　　　
　　　if　abs(@afterMoney-@beforeMoney)　>　2000
　　　　　　begin
　　　　　　　　print　'当前交易金额为：'　+
　　　　　　　　　　　　　　convert(varchar(20),abs(@afterMoney-@beforeMoney))
　　　　　　　　--　自定义错误消息
　　　　　　　　raiserror('每次交易金额不能超过2000元，交易失败！',16,1)
　　　　　　　
　　　　　　　　rollback　transaction　--回滚事务，撤销交易！
　　　　　　　　/*　注意：
　　　　　　　　　　　触发器是一个特殊的事务单元
　　　　　　　　　　　不需显示声明begin　transaction
　　　　　　　　*/
　　　　　　end
go

------------------　测试触发器　------------------
set　nocount　on
--测试1:　在　bank表触发　update触发器
update　bank　set　currentMoney　=　currentMoney　+　25000
　　　　　　　where　cardID　=　'1001　0001'
--测试2:　通过　transInfo表的　trig_insert_transInfo触发器
--　　　　　　　　　　　　　间接触发　bank表的　trig_update_bank触发器
insert　into　transInfo(cardID,transType,transMoney)
　　　　　　　　　　　　　values('1001　0001','存入',10000);
--查看结果
select　*　from　bank
select　*　from　transInfo

　　触发器7_MSDN参考

　　加密 dml触发器定义

　　若要确保其他用户不能查看触发器定义，可以使用with encryption子句加密 dml 触发器。

　　使用with encryption子句后，触发器定义即以无法读取的格式进行存储。

　　触发器定义加密后，无法进行解密。且任何人都无法进行查看，包括触发器的所有者和系统管理员。

　　update() 函数：

　　可用于确定 insert或 update语句是否影响表中的特定列。

　　无论何时为列赋值，该函数都将返回 true。

　　使用if update() 子句示例：

　　if update()子句示例

　　create　table　testTable(a　int　null,　b　int　null)
　　go
　　create　trigger　my_trig
　　on　testTable　for　insert
　　as
　　　　　if　update(b)
　　　　　print　'列b已被修改！'
　　go
　
　　insert　into　testTable(b)　values(123);
　　--　drop　table　testTable

　　注意：　

　　由于 delete 语句无法只对某列进行删除，

　　因此不能将if update()子句应用于delete 语句。

　　columns_updated() 函数：

　　也可用于检查 insert或 update语句更新了表中的哪些列。

　　此函数使用整数位掩码指定要测试的列。

　　使用columns_updated() 函数示例：

　　columns_updated()函数示例

　　create　table　testTable2(a　int　null,　b　int　null)
　　go
　　create　trigger　my_trig2
　　on　testTable2　for　insert
　　as
　　　　　if　(　columns_updated()　&　2　=　2　)
　　　　　print　'列b已被修改！'
　　go
　　insert　into　testTable2(b)　values(123);
　


　　--　drop　table　testTable2

关键字：SQL Server、触发器、数据库


【 顶部 】 【 关闭 】