SQL-------触发器
1.什么是触发器
q 触发器是在对表进行插入、更新或删除操作时自动执行的存储过程
q 触发器通常用于强制业务规则
q 触发器是一种高级约束,可以定义比用CHECK 约束更为复杂的约束
q 可执行复杂的SQL语句(if/while/case)
q 可引用其它表中的列
q 触发器定义在特定的表上,与表相关
q 自动触发执行
q 不能直接调用
q 是一个事务(可回滚)
2.触发器的类型
q DELETE 触发器
q INSERT 触发器
q UPDATE 触发器
3.inserted和deleted
q 触发器触发时:
q 系统自动在内存中创建deleted表或inserted表
q 只读,不允许修改;触发器执行完成后,自动删除
q inserted 表
q 临时保存了插入或更新后的记录行
q 可以从inserted表中检查插入的数据是否满足业务需求
q 如果不满足,则向用户报告错误消息,并回滚插入操作
q deleted 表
q 临时保存了删除或更新前的记录行
q 可以从deleted表中检查被删除的数据是否满足业务需求
q 如果不满足,则向用户报告错误消息,并回滚插入操作
4.创建触发器的语法
CREATE TRIGGER trigger_name
ON table_nam [WITH ENCRYPTION]
FOR [DELETE, INSERT, UPDATE]
AS
T-SQL语句
GO
5.一段简单的触发器代码(delete触发器)
CREATE TRIGGER trig_deletestuno ON [dbo].[student] -----创建触发器
FOR DELETE -----触发器类型
AS
declare @no varchar(20)-----定义变量
select @no=stuNo from deleted-------从deleted表中查找stuNo
delete from exam ------删除exam表中的数据
where stuNo=@no
6.一段简单的代码(update触发器)
跟踪用户的交易,交易金额超过20000元,则取消交易,并给出错误提示。
CREATE TRIGGER trig_update_bank------创建触发器
ON bank-----触发器所在表名
FOR UPDATE-----触发器的类型
AS
DECLARE @beforeMoney MONEY,@afterMoney MONEY---定义变量
SELECT @beforeMoney=currentMoney FROM deleted
-----从 deleted中取出currentMoney
SELECT @afterMoney=currentMoney FROM inserted
------从inserted中取出currentMoney
IF ABS(@afterMoney-@beforeMoney)>20000 -----判断
BEGIN
print '交易金额:'+convert(varchar(8),
ABS(@afterMoney-@beforeMoney))------ABS绝对值
RAISERROR ('每笔交易不能超过2万元,交易失败',16,1)
ROLLBACK TRANSACTION--------回滚
END
GO
7.insert触发器
解决上述的银行取款问题:当向交易信息表(transInfo)中插入一条交易信息时,我们应自动更新对应帐户的余额。
CREATE TRIGGER trig_transInfo ------创建触发器
ON transInfo -----触发器所在表名
FOR INSERT -----触发器的类型
AS
DECLARE @type char(4),@outMoney MONEY----定义变量
DECLARE @myCardID char(10),@balance MONEY-----定语变量
SELECT @type=transType,@outMoney=transMoney,
@myCardID=cardID FROM inserted------从inserted中取出数据
IF (@type='支取') -----判断
UPDATE bank SET currentMoney=currentMoney-@outMoney
WHERE cardID=@myCardID-----支取
ELSE
UPDATE bank SET currentMoney=currentMoney+@outMoney
WHERE cardID=@myCardID----存入
…..
GO