SQL触发器实例.docx
《SQL触发器实例.docx》由会员分享,可在线阅读,更多相关《SQL触发器实例.docx(9页珍藏版)》请在冰豆网上搜索。
SQL触发器实例
SQL触发器实例
/*
建立虚拟测试环境,包含:
表[卷烟库存表],表[卷烟销售表]。
请大家注意跟踪这两个表的数据,体会触发器到底执行了什么业务逻辑,对数据有什么影响。
为了能更清晰的表述触发器的作用,表结构存在数据冗余,且不符合第三范式,这里特此说明。
*/
USEMaster
GO
IFEXISTS(SELECTNAMEFROMSYSOBJECTSWHEREXTYPE='U'ANDNAME='卷烟库存表')
DROPTABLE卷烟库存表
GO
IFEXISTS(SELECTNAMEFROMSYSOBJECTSWHEREXTYPE='U'ANDNAME='卷烟销售表')
DROPTABLE卷烟销售表
GO
--业务规则:
销售金额=销售数量*销售单价业务规则。
CREATETABLE卷烟销售表
(
卷烟品牌VARCHAR(40)PRIMARYKEYNOTNULL,
购货商VARCHAR(40)NULL,
销售数量INTNULL,
销售单价MONEYNULL,
销售金额MONEYNULL
)
GO
--业务规则:
库存金额=库存数量*库存单价业务规则。
CREATETABLE卷烟库存表
(
卷烟品牌VARCHAR(40)PRIMARYKEYNOTNULL,
库存数量INTNULL,
库存单价MONEYNULL,
库存金额MONEYNULL
)
GO
--创建触发器,示例1
/*
创建触发器[T_INSERT_卷烟库存表],这个触发器较简单。
说明:
每当[卷烟库存表]发生INSERT动作,则引发该触发器。
触发器功能:
强制执行业务规则,保证插入的数据中,库存金额=库存数量*库存单价。
注意:
[INSERTED]、[DELETED]为系统表,不可创建、修改、删除,但可以调用。
重要:
这两个系统表的结构同插入数据的表的结构。
*/
IFEXISTS(SELECTNAMEFROMSYSOBJECTSWHEREXTYPE='TR'ANDNAME='T_INSERT_卷烟库存表')
DROPTRIGGERT_INSERT_卷烟库存表
GO
CREATETRIGGERT_INSERT_卷烟库存表
ON卷烟库存表
FORINSERT
AS
--提交事务处理
BEGINTRANSACTION
--强制执行下列语句,保证业务规则
UPDATE卷烟库存表
SET库存金额=库存数量*库存单价
WHERE卷烟品牌IN(SELECT卷烟品牌fromINSERTED)
COMMITTRANSACTION
GO
/*
针对[卷烟库存表],插入测试数据:
注意,第一条数据(红塔山新势力)中的数据符合业务规则,
第二条数据(红塔山人为峰)中,[库存金额]空,不符合业务规则,
第三条数据(云南映像)中,[库存金额]不等于[库存数量]乘以[库存单价],不符合业务规则。
第四条数据库存数量为0。
请注意在插入数据后,检查[卷烟库存表]中的数据是否库存金额=库存数量*库存单价。
*/
INSERTINTO卷烟库存表(卷烟品牌,库存数量,库存单价,库存金额)
SELECT'红塔山新势力',100,12,1200UNIONALL
SELECT'红塔山人为峰',100,22,NULLUNIONALL
SELECT'云南映像',100,60,500UNIONALL
SELECT'玉溪',0,30,0
GO
--查询数据
SELECT*FROM卷烟库存表
GO
/*
结果集
RecordId卷烟品牌库存数量库存单价库存金额
--------------------------------------------
1红塔山新势力10012.00001200.0000
2红塔山人为峰10022.00002200.0000
3云南映像10060.00006000.0000
4玉溪030.0000.0000
(所影响的行数为4行)
*/
--触发器示例2
/*
创建触发器[T_INSERT_卷烟销售表],该触发器较复杂。
说明:
每当[卷烟库存表]发生INSERT动作,则引发该触发器。
触发器功能:
实现业务规则。
业务规则:
如果销售的卷烟品牌不存在库存或者库存为零,则返回错误。
否则则自动减少[卷烟库存表]中对应品牌卷烟的库存数量和库存金额。
*/
IFEXISTS(SELECTNAMEFROMSYSOBJECTSWHEREXTYPE='TR'ANDNAME='T_INSERT_卷烟销售表')
DROPTRIGGERT_INSERT_卷烟销售表
GO
CREATETRIGGERT_INSERT_卷烟销售表
ON卷烟销售表
FORINSERT
AS
BEGINTRANSACTION
--检查数据的合法性:
销售的卷烟是否有库存,或者库存是否大于零
IFNOTEXISTS(
SELECT库存数量
FROM卷烟库存表
WHERE卷烟品牌IN(SELECT卷烟品牌FROMINSERTED)
)
BEGIN
--返回错误提示
RAISERROR('错误!
该卷烟不存在库存,不能销售。
',16,1)
--回滚事务
ROLLBACK
RETURN
END
IFEXISTS(
SELECT库存数量
FROM卷烟库存表
WHERE卷烟品牌IN(SELECT卷烟品牌FROMINSERTED)AND
库存数量<=0
)
BEGIN
--返回错误提示
RAISERROR('错误!
该卷烟库存小于等于0,不能销售。
',16,1)
--回滚事务
ROLLBACK
RETURN
END
--对合法的数据进行处理
--强制执行下列语句,保证业务规则
UPDATE卷烟销售表
SET销售金额=销售数量*销售单价
WHERE卷烟品牌IN(SELECT卷烟品牌FROMINSERTED)
DECLARE@卷烟品牌VARCHAR(40)
SET@卷烟品牌=(SELECT卷烟品牌FROMINSERTED)
DECLARE@销售数量MONEY
SET@销售数量=(SELECT销售数量FROMINSERTED)
UPDATE卷烟库存表
SET库存数量=库存数量-@销售数量,
库存金额=(库存数量-@销售数量)*库存单价
WHERE卷烟品牌=@卷烟品牌
COMMITTRANSACTION
GO
--请大家自行跟踪[卷烟库存表]和[卷烟销售表]的数据变化。
--针对[卷烟销售表],插入第一条测试数据,该数据是正常的。
INSERTINTO卷烟销售表(卷烟品牌,购货商,销售数量,销售单价,销售金额)
SELECT'红塔山新势力','某购货商',10,12,1200
GO
--针对[卷烟销售表],插入第二条测试数据,该数据销售金额不等于销售单价*销售数量。
--触发器将自动更正数据,使销售金额等于销售单价*销售数量。
INSERTINTO卷烟销售表(卷烟品牌,购货商,销售数量,销售单价,销售金额)
SELECT'红塔山人为峰','某购货商',10,22,2000
GO
--针对[卷烟销售表],插入第三条测试数据,该数据中的卷烟品牌在卷烟库存表中找不到对应。
--触发器将报错。
INSERTINTO卷烟销售表(卷烟品牌,购货商,销售数量,销售单价,销售金额)
SELECT'红河V8','某购货商',10,60,600
GO
/*
结果集
服务器:
消息50000,级别16,状态1,过程T_INSERT_卷烟销售表,行15
错误!
该卷烟不存在库存,不能销售。
*/
--针对[卷烟销售表],插入第三条测试数据,该数据中的卷烟品牌在卷烟库存表中库存为0。
--触发器将报错。
INSERTINTO卷烟销售表(卷烟品牌,购货商,销售数量,销售单价,销售金额)
SELECT'玉溪','某购货商',10,30,300
GO
/*
结果集
服务器:
消息50000,级别16,状态1,过程T_INSERT_卷烟销售表,行29
错误!
该卷烟库存小于等于0,不能销售。
*/
--查询数据
SELECT*FROM卷烟库存表
SELECT*FROM卷烟销售表
GO
/*
补充:
1、本示例主要通过一个简单的业务规则实现来进行触发器使用的说明,具体的要根据需要灵活处理;
2、关于触发器要理解并运用好INSERTED,DELETED两个系统表;
3、本示例创建的触发器都是FORINSERT,具体的语法可参考:
Trigger语法
CREATETRIGGERtrigger_name
ON{table|view}
[WITHENCRYPTION]--用于加密触发器
{
{{FOR|AFTER|INSTEADOF}{[INSERT][,][UPDATE]}
[WITHAPPEND]
[NOTFORREPLICATION]
AS
[{IFUPDATE(column)
[{AND|OR}UPDATE(column)]
[...n]
|IF(COLUMNS_UPDATED(){bitwise_operator}updated_bitmask)
{comparison_operator}column_bitmask[...n]
}]
sql_statement[...n]
}
}
4、关于触发器,还应该注意
(1)、DELETE触发器不能捕获TRUNCATETABLE语句。
(2)、触发器中不允许以下Transact-SQL语句:
ALTERDATABASECREATEDATABASEDISKINIT
DISKRESIZEDROPDATABASELOADDATABASE
LOADLOGRECONFIGURERESTOREDATABASE
RESTORELOG
(3)、触发器最多可以嵌套32层。
*/
--修改触发器
--实质上,是将CREATETRIGGER...修改为ALTERTRIGGER...即可。
--删除触发器
DROPTRIGGERxxx
GO
--删除测试环境
DROPTABLE卷烟库存表
GO
DROPTABLE卷烟销售表
GO
DROPTRIGGERT_INSERT_卷烟库存表
GO
DROPTRIGGERT_INSERT_卷烟销售表
GO
##################################################################
触发器的基础知识和例子
:
createtriggertr_name
ontable/view
{for|after|insteadof}[update][,][insert][,][delete]
[withencryption]
as{batch|ifupdate(col_name)[{and|or}update(col_name)]}
说明:
1tr_name:
触发器名称
2ontable/view:
触发器所作用的表。
一个触发器只能作用于一个表
3for和after:
同义
4after与insteadof:
sql2000新增项目afrer与insteadof的区别
After
在触发事件发生以后才被激活,只可以建立在表上
Insteadof
代替了相应的触发事件而被执行,既可以建立在表上也可以建立在视图上
5insert、update、delete:
激活触发器的三种操作,可以同时执行,也可选其一
6ifupdate(col_name):
表明所作的操作对指定列是否有影响,有影响,则激活触发器。
此外,因为delete操作只对行有影响,
所以如果使用delete操作就不能用这条语句了(虽然使用也不出错,但是不能激活触发器,没意义)。
7触发器执行时用到的两个特殊表:
deleted,inserted
deleted和inserted可以说是一种特殊的临时表,是在进行激活触发器时由系统自动生成的,其结构与触发器作用的表结构是一
样的,只是存放的数据有差异。
续
下面表格说明deleted与inserted数据的差异
deleted与inserted数据的差异
Inserted
存放进行insert和update操作后的数据
Deleted
存放进行delete和update操作前的数据
注意:
update操作相当于先进行delete再进行insert,所以在进行update操作时,修改前的数据拷贝一条到deleted表中,修改后
的数据在存到触发器作用的表的同时,也同时生成一条拷贝到insered表中
(2)创建触发器
根据需求分析,为BBS_Type表、BBS_LanMu表、BBS_Topic表分别建立AFTER触发器,该触发器由DELETE事件触发。
建立在这3个表之上的触发器之间是嵌套触发的关系,即BBS_Type表上的触发器触发BBS_LanMu表上的触发器,BBS_LanMu表上的触发器再触发BBS_Topic表上的触发器。
本文中使用的数据库为MicrosoftSQLServer2005。
要让触发器能嵌套触发必须在数据库“属性”中,将“递归触发器已启用”设置为TRUE。
1.为BBS_Type表(存储大版块信息)建立触发器DelType。
该触发器功能是删除BBS_LanMu表中属于刚删除的大版块的所有分论坛信息。
CREATEtrigger[DelType]on[dbo].[BBS_Type]
afterdelete
as
begin
declare@typeidint
select@typeid=TypeIDfromdeleted--获得要被删除的版块ID
deletefromdbo.BBS_LanMuwhereTypeid=@typeid
end
2.为BBS_LanMu表(存储分论坛信息)建立触发器DelLanmu。
该触发器功能是删除BBS_Topic表中属于刚删除分论坛的所有帖子信息。
CREATEtrigger[DelLanmu]on[dbo].[BBS_LanMu]
AFTERDELETE
as
BEGIN
declare@lmidint
select@lmid=LMIDfromdeleted--获得要被删除的分论坛ID
deletefromdbo.BBS_TopicwhereLMID=@lmid
END
3.为BBS_Topic表(存储帖子信息)建立触发器DelTopic。
该触发器功能是删除BBS_Reply表中属于刚删除帖子的所有回复信息。
CREATEtrigger[DelTopic]on[dbo].[BBS_Topic]
afterdelete
as
BEGIN
declare@tidint
select@tid=TIDfromdeleted--获得要被删除的帖子ID
deletefromdbo.BBS_ReplywhereTID=@tid
END
执行过程
触发器执行过程如图Pic-2。
当数据操作层对数据表BBS_Type发出DELETE一条记录的时候,触发器DelType被触发,此触发器将删除BBS_LanMu表中属于刚删除的大版块的所有分论坛信息。
当DelType触发器对数据表BBS_LanMu删除一条记录时,又触发BBS_LanMu表上的触发器DelLanmu,此触发器将删除BBS_Topic表中属于刚删除分论坛的所有帖子信息。
当DelLanmu触发器对数据表BBS_Topic删除一条记录时,又触发触发器DelTopic,此触发器将删除BBS_Reply表中属于刚删除帖子的所有回复信息。
至此数据库中与BBS_Type中删除记录相关联的所有记录全部删除,保证了数据库各表数据的完整性与一致性。
这个过程是在数据库中自动进行的,因此速度非常快,用户只需要对BBS_Type表发出删除一条记录的命令,其他表中相关的记录会自动删除。
3.1.总结:
触发器能保持数据的完整性与一致性,它可以方便地基于一个表的修改,自动更新其他相关表的记录,以保证数据的完整性。
在数据库的应用中,触发器扮演着很重要的角色。
无论是作为提供高级参照完整性功能的途径,还是执行自动维护非正规化数据的任务,触发器都能帮助用户实现满足实际需要的规则,简化业务逻辑,并使系统更方便更有效