浅谈SQL Server 数据库的触发器
触发器的特征：
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*fromsys.triggers
创建临时表 #tableName
createtable#tableName
如何使用 SQL Server 触发器
触发器2_初始化环境SQL
初始化环境 ---------------初始化环境---------------
createdatabaseTriggerDatabase
useTriggerDatabase
go ifexists(select*fromsysobjectswherename='bank')
droptablebank createtablebank--账户信息表
(
userNamevarchar(10)notnull,--顾客名
cardIDvarchar(10)notnull,--卡号
currentMoneymoneynotnull--当前余额
) ifexists(select*fromsysobjectswherename='transInfo')
droptabletransInfo createtabletransInfo--交易信息表
(
cardIDvarchar(10)notnull,--卡号
transTypechar(4)notnull,--交易类型(存入/支取)
transMoneymoneynotnull,--交易金额
transDatedatetimenotnull--交易日期
)
go ---------------添加约束---------------
altertablebank
addconstraintCK_currentMoneycheck(currentMoney>=1); altertabletransInfo
addconstraintDF_transDatedefault(getdate())fortransDate; altertabletransInfo
addconstraintCK_transTypecheck(transTypein('支取','存入')); ---------------添加测试数据---------------
/*张三1000元*/
insertintobank(userName,cardID,currentMoney)
values('张三','10010001',1000);
/*李四1元*/
insertintobank(userName,cardID,currentMoney)
values('李四','10010002',1);
/*张三支取200元*/
insertintotransInfo(cardID,transType,transMoney)
values('10010001','支取',200); ---------------查看结果---------------
select*frombank;
select*fromtransInfo;
go
触发器3_定义触发器的格式
定义触发器的格式 --=============================================
--Author:xugang
--Createdate:-2-14
--Description:定义触发器的精简格式
--[]：可选{}必选
--=============================================
createtrigger[schema_name.]--触发器所属架构
trigger_name--触发器名称
on{table|view}--触发器的表或视图
[withencryption]--加密dml触发器定义(后面详解)
{for|after}
/*after：只有在触发它的SQL语句执行成功后才能激发。
(只能对“表”定义after)*/
{insert,update,delete}
as
/*SQL语句...*/
go --查看当前数据库中的所有触发器
select*fromsys.triggers
触发器4_insert 触发器SQL
insert 触发器 ------------------insert触发器------------------
useTriggerDatabase
go
ifexists(select*fromsysobjects
wherename='trig_insert_transInfo')
droptriggertrig_insert_transInfo
go --createtrigger必须是批处理(go)的第一句
createtriggertrig_insert_transInfo
ontransInfoforinsert
as
declare@_transTypechar(4),--定义变量
@_transMoneymoney,
@_cardIDchar(10),
@balancemoney--所剩余额
--从inserted临时表中获取记录值
select@_transType=transType,
@_transMoney=transMoney,
@_cardID=cardID
frominserted if(@_transType='支取')
updatebanksetcurrentMoney=currentMoney-@_transMoney
wherecardID=@_cardID;
else
updatebanksetcurrentMoney=currentMoney+@_transMoney
wherecardID=@_cardID; --显示交易金额
print'交易成功！交易金额：'
+convert(varchar(20),@_transMoney) --显示所剩余额
select@balance=currentMoneyfrombank
wherecardId=@_cardID print'卡号：'+@_cardID
+'余额：'+convert(varchar(20),@balance);
go ------------------测试触发器------------------
--deletefromtransInfo
setnocounton--不显示T-SQL影响的记录行数
insertintotransInfo(cardID,transType,transMoney)
values('10010001','支取',200);
insertintotransInfo(cardID,transType,transMoney)
values('10010001','存入',10000);
--查看结果
select*frombank
select*fromtransInfo
触发器5_delete 触发器SQL
delete 触发器
/*实现:当清除'交易信息表'的数据时，
自动备份被清除的数据到backupTable表中
*/ ------------------delete触发器------------------
useTriggerDatabase
go ifexists(select*fromsysobjects
wherename='trig_delete_transInfo')
droptriggertrig_delete_transInfo
go createtriggertrig_delete_transInfo
ontransInfoafterdelete--for|after
as
print'开始备份数据，请稍后......'
--如果数据库中，不存在backupTable表
ifnotexists(select*fromsysobjects
wherename='backupTable')
select*intobackupTablefromdeleted--deleted临时表
else
insertintobackupTableselect*fromdeleted
print'备份成功，备份表backupTable中的数据为：'
select*frombackupTable;
go ------------------测试触发器------------------
setnocounton deletefromtransInfo;--测试
--查看结果
select*fromtransInfo
select*frombackupTable
触发器6_update 触发器SQL
update 触发器 ------------------update触发器------------------
useTriggerDatabase
go ifexists(select*fromsysobjects
wherename='trig_update_bank')
droptriggertrig_update_bank
go createtriggertrig_update_bank
onbankforupdate--在bank表上创建update触发器
as
declare@beforeMoneymoney,
@afterMoneymoney,
@currentTransmoney--当前交易金额
--从deleted临时表，获取交易前的余额
select@beforeMoney=currentMoneyfromdeleted;
--从inserted临时表，获取交易后的余额
select@afterMoney=currentMoneyfrominserted;
ifabs(@afterMoney-@beforeMoney)>2000
begin
print'当前交易金额为：'+
convert(varchar(20),abs(@afterMoney-@beforeMoney))
--自定义错误消息
raiserror('每次交易金额不能超过2000元，交易失败！',16,1)
rollbacktransaction--回滚事务，撤销交易！
/*注意：
触发器是一个特殊的事务单元
不需显示声明begintransaction
*/
end
go ------------------测试触发器------------------
setnocounton --测试1:在bank表触发update触发器
updatebanksetcurrentMoney=currentMoney+25000
wherecardID='10010001' --测试2:通过transInfo表的trig_insert_transInfo触发器
--间接触发bank表的trig_update_bank触发器
insertintotransInfo(cardID,transType,transMoney)
values('10010001','存入',10000); --查看结果
select*frombank
select*fromtransInfo
触发器7_MSDN参考：
加密 dml触发器定义
若要确保其他用户不能查看触发器定义，可以使用with encryption子句加密 dml触发器。
使用with encryption子句后，触发器定义即以无法读取的格式进行存储。
触发器定义加密后，无法进行解密。且任何人都无法进行查看，包括触发器的所有者和系统管理员。
update() 函数：
可用于确定 insert或 update语句是否影响表中的特定列。
无论何时为列赋值，该函数都将返回 true。
使用if update()子句示例：
if update()子句示例 createtabletestTable(aintnull,bintnull)
go createtriggermy_trig
ontestTableforinsert
as
ifupdate(b)
print'列b已被修改！'
go
insertintotestTable(b)values(123); --droptabletestTable
注意：
由于 delete 语句无法只对某列进行删除，
因此不能将if update()子句应用于delete 语句。
columns_updated()函数：
也可用于检查 insert或 update语句更新了表中的哪些列。
此函数使用整数位掩码指定要测试的列。
使用columns_updated()函数示例：
columns_updated()函数示例 createtabletestTable2(aintnull,bintnull)
go createtriggermy_trig2
ontestTable2forinsert
as
if(columns_updated()&2=2)
print'列b已被修改！'
go insertintotestTable2(b)values(123);
--droptabletestTable2
/xugang/archive//02/20/1669619.html
![[转]SQL SERVER数据库开发之触发器的应用](https://300zi.50zi.cn/uploadfile/img/16/3/52f73a20a0e0d977fb39165193d73714.jpg)
