sqlserver触发器实例,sql触发器的使用及语法
SQL触发器实例1
定义:什么是触发器?在SQL Server中,就是对某个表的某个操作触发了某个条件,从而执行一个程序。触发器是一个特殊的存储过程。
有三种常见的触发器:它们分别应用于插入、更新、删除事件。
为什么我应该使用触发器?例如,这两个表:
创建学生(-学生表
StudentID int主键,-学生编号
.
)
创建表BorrowRecord( -学生借阅记录表
借入记录int identity (1,1),-序列号
StudentID int,-学生编号
借出日期日期时间,-借出时间
返回日期日期时间,-返回时间
.
)
使用的功能有:
1.如果我更改了学生的学号,我希望他的借阅记录仍然与这个学生相关(即同时更改借阅记录表的学号);
2.如果学生已经毕业,我希望删除他的学号,以及他的借阅记录。
等一下。
此时可以使用触发器。对于1,创建更新触发器:
创建触发器信任学生
对学生-在学生表中创建触发器。
更新-事件为什么会触发?
As -事件触发后要做什么
如果更新(学生ID)
开始
更新借款记录
Set StudentID=i.StudentID
从borrowrecordbr,删除的、插入的I-删除的和插入的临时表
br在哪里?学生ID=d .学生ID
目标
理解触发器中的两个临时表:deleted和inserted。请注意,Deleted和Inserted分别表示触发事件的表的“旧记录”和“新记录”。
在数据库系统中,有两个虚拟表用于存储记录在表中的变化信息,即:
虚拟表已插入虚拟表已删除
添加表记录时,存储新记录,但不存储。
修改时,存储新记录以供更新,存储更新前的记录。
删除时不存储记录,存储已删除的记录。
一个更新过程可以看作是:向插入的表生成新记录,将旧记录复制到删除的表,然后删除学生记录并写入新记录。
对于2,创建一个删除触发器
创建触发器学生
论学生
用于删除
如同
删除借款记录
来自BorrowRecord br,Delted d
br在哪里?学生ID=d .学生ID
从这两个例子可以看出trigger的关键:A.2临时表;b .触发机制。
SQL触发器实例2/*
建立虚拟测试环境,包括:表【卷烟库存表】和表【卷烟销售表】。
请注意跟踪这两个表的数据,认识到触发器执行的是什么业务逻辑,对数据有什么影响。
为了更清晰的表达trigger的功能,表结构中存在数据冗余,不符合第三范式,在此说明。
*/
使用母版
去
如果存在(从sysobjects中选择名称,其中xtype= u 和name=香烟库存表)
下降表香烟库存表
去
如果存在(从sysobjects中选择名称,其中xtype= u 和name=香烟销售表)
下降表香烟销售表
去
-业务规则:销售金额=销售数量*销售单价业务规则。
创建表香烟销售表
(
卷烟品牌VARCHAR(40)主键不为空NULL,
购买者varchar (40)为空,
销售数量INT NULL,
销售单价金额为空,
销售金额为空
)
去
-业务规则:存货金额=存货数量*存货单价业务规则。
创建表香烟库存表
(
卷烟品牌VARCHAR(40)主键不为空NULL,
库存数量INT NULL,
库存单价金额为空,
库存金额为空
)
去
-创建触发器,示例1
/*
创建一个触发器【T_INSERT_香烟库存表】,很简单。
注意:每当[香烟库存表]插入时,该触发器将被触发。
触发功能:强制执行业务规则,确保插入数据中的存货金额=存货数量*存货单价。
注意:【插入】、【删除】为系统表,不能新建、修改、删除,但可以调用。
要点:这两个系统表的结构与插入数据的表的结构相同。
*/
如果存在(从sysobjects中选择名称,其中xtype= tr and name= t _ insert _ circuit inventory table )
DROP TRIGGER T_INSERT_卷烟库存表
去
创建触发器T_INSERT_香烟库存表
在卷烟库存表上
用于插入
如同
-提交交易。
开始交易
-执行以下声明以确保业务规则
更新卷烟库存表
设置库存金额=库存数量*库存单价
香烟品牌在哪里(从插入的内容中选择香烟品牌)
提交事务
去
/*
对于[卷烟库存表],插入测试数据:
注意,第一个数据(红塔山新势力)中的数据符合业务规则,
数据中第二条(红塔山人造峰),【库存金额】为空,不符合业务规则。
第三条数据中(云南映像),【存货金额】不等于【存货数量】乘以【存货单价】,不符合业务规则。
第四条数据库存数量为0。
请注意,插入数据后,检查【卷烟库存表】中的数据是否为库存金额=库存数量*库存单价。
*/
插入卷烟库存表(卷烟品牌、库存数量、库存单价、库存金额)
选择‘红塔新势力’,100,12,1200工会全部
选择‘红塔山人造峰’,100,22,空联全部
选择“云南图像”,100,60,500联盟所有
选择羽西,0,30,0
去
-查询数据
从卷烟库存表中选择*
去
/*
结果集
RecordId卷烟品牌库存数量库存单价库存金额
- - - - -
1红塔山新势力100 12.0000 1200.0000
2红塔山人造山峰100 22.0000 2200.000
3云南图像100 60.0000 6000.0000
4玉溪0 30.0000 .0000
(受影响的行数为4)
*/
-触发器示例2
/*
创建触发器[T_INSERT_卷烟销售表],比较复杂。
注意:每当[香烟库存表]插入时,该触发器将被触发。
触发功能:实现业务规则。
业务规则:如果销售的卷烟品牌没有库存或者库存为零,则返回错误。
否则,卷烟库存表中相应品牌卷烟的库存数量和库存金额会自动减少。
*/
如果存在(从sysobjects中选择名称,其中xtype= tr 和name= t _ insert _香烟销售表)
DROP TRIGGER T_INSERT_卷烟销售表
去
创建触发器T_INSERT_卷烟销售表
在香烟销售台上
用于插入
如同
开始交易
-检查数据的合法性:所售香烟是否有库存,或者库存是否大于零。
如果不存在(
选择库存数量
来自卷烟库存表
香烟品牌在哪里(从插入的内容中选择香烟品牌)
)
开始
-返回错误提示。
RAISERROR(错误!这种香烟没有库存,不能出售。16,1)
-回滚事务
反转
返回
结束
如果存在(
选择库存数量
来自卷烟库存表
香烟品牌在哪里(从插入的内容中选择香烟品牌)以及
库存=0
)
开始
-返回错误提示。
RAISERROR(错误!卷烟库存小于等于0,不能销售。16,1)
-回滚事务
反转
返回
结束
-处理法律数据
-执行以下声明以确保业务规则
更新卷烟销售表
设定销售金额=销售数量*销售单价
香烟品牌在哪里(从插入的内容中选择香烟品牌)
声明@卷烟品牌VARCHAR(40)
SET @卷烟品牌=(从插入的内容中选择卷烟品牌)
申报@销售数量金额
SET @ sales quantity=(从插入内容中选择销售数量)
更新卷烟库存表
设置库存数量=库存数量-@销售数量,
库存金额=(库存数量-@销售数量)*库存单价
凡香烟品牌=@香烟品牌
提交事务
去
-请自行跟踪【卷烟库存单】和【卷烟销售单】的数据变化。
-对于【卷烟销售表】,插入第一个测试数据,正常。
插入卷烟销售表(卷烟品牌、购买者、销售数量、销售单价、销售金额)
选择‘红塔山新势力’,‘一个买家’,10,12,1200
去
-对于[卷烟销售表],插入第二个测试数据,该数据销售金额不等于销售单价*销售数量。
-Trigger会自动更正数据,使销售额等于销售单价*销售数量。
插入卷烟销售表(卷烟品牌、购买者、销售数量、销售单价、销售金额)
选择“红塔山是顶峰”,“一个买家”,10,22,2000
去
-对于【卷烟销售表】,插入第三个测试数据,该数据中的卷烟品牌在卷烟库存表中找不到。
-触发器会报告错误。
插入卷烟销售表(卷烟品牌、购买者、销售数量、销售单价、销售金额)
选择红河V8 ,某买家,10,60,600
去
/*
结果集
服务器:消息50000,第16层,状态1,处理T_INSERT_香烟销售表,第15行
不对!这种香烟没有存货,不能出售。
*/
-对于【卷烟销售表】,插入第三个测试数据,其中数据中的卷烟品牌在卷烟库存表中的库存为0。
-触发器会报告错误。
插入卷烟销售表(卷烟品牌、购买者、销售数量、销售单价、销售金额)
选择玉溪,某买家,10,30,300
去
/*
结果集
服务器:消息50000,级别16,状态1,处理T_INSERT_香烟销售表,第29行
不对!卷烟库存小于等于0,不能销售。
*/
-查询数据
从卷烟库存表中选择*
从卷烟销售表中选择*
去
/*
补充:
1.本例主要通过一个简单的业务规则实现来说明触发器的使用,要根据需要灵活处理;
2.理解并利用好关于触发器的两个系统表,插入和删除;
3.本例中创建的触发器都是用于插入的。请参考:
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
触发语法
//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
创建触发器trigger_name
在{表格视图}上
[带加密] -用于加密触发器。
{
{ { FOR AFTER INSTEAD OF } { [插入] [,] [更新] }
[带追加]
[不用于复制]
如同
[ {如果更新(列)
[ {和或}更新(列)]
[.n ]
IF(COLUMNS _ UPDATED(){ bitwise _ operator } UPDATED _ bit mask)
{比较_运算符}列_位掩码[.n ]
} ]
sql_statement [.n ]
}
}
4.关于触发因素,还应该指出
(1) DELETE触发器不能捕获TRUNCATE TABLE语句。
(2)触发器中不允许使用以下Transact-SQL语句:
更改数据库创建数据库磁盘初始化
磁盘大小调整删除数据库加载数据库
加载日志重新配置还原数据库
恢复日志
(3)触发器最多可以嵌套32层。
*/
-修改触发器
-本质上,只是修改创建触发器.改变触发器.
-删除触发器
丢弃触发器xxx
去
-删除测试环境
下降表香烟库存表
去
下降表香烟销售表
去
DROP TRIGGER T_INSERT_卷烟库存表
去
DROP TRIGGER T_INSERT_卷烟销售表
去
##################################################################
触发器的基本知识和示例
:创建触发器tr_name
在表格/视图上
{ for after instead of }[更新][,][插入][,][删除]
[带加密]
as { batch if update(col _ name)[{ and or } update(col _ name)]]
描述:
1 tr_name:触发器名称
Ontable/view:触发器作用的表。一个触发器只能作用于一个表。
3及以后:同义
4 afrer和instead:新项“African”和“instead:instead:SQL 2000”的区别
在.之后
它仅在触发事件发生后被激活,并且只能在表上构建。
代替
它执行的不是相应的触发事件,而是可以在表或视图上构建的事件。
5.插入、更新、删除:激活触发器的三个操作可以同时执行,也可以选择其中一个。
6 if update (col_name):指示操作是否对指定的列有影响,如果有,则激活触发器。此外,因为删除操作只影响行,
所以如果使用删除操作,就不能使用这个语句(虽然不出错,但是不能激活触发器,没有意义)。
7触发器执行中使用的两个特殊表:删除的、插入的
删除和插入是特殊的临时表,当触发器被激活时,由系统自动生成。它们的结构与触发器使用的表的结构相同。
样本,但存储的数据存在差异。
继续
下表说明了删除的数据和插入的数据之间的差异。
删除与插入的数据的差异
插入的
存放进行插入和更新操作后的数据
删除
存放进行删除和更新操作前的数据
注意:更新操作相当于先进行删除再进行插入,所以在进行更新操作时,修改前的数据拷贝一条到删除表中,修改后
的数据在存到触发器作用的表的同时,也同时生成一条拷贝到插入表中
/////////
在dbo.chl_lydj上创建触发器[触发混合物_接收_日志]
用于更新
如同
开始
declare @ djsfxg char(10)declare @ wtbh char(20)
从插入的中选择@wtbh=wtbh
update ly_tzk set djsfxg=已修改wherewtbh=@wtbh
目标
if(从t _ log设置中选择data _ sfjl 是
开始
declare @ oldc jmc char(100)declare @ oldlyrq datetime
declare @ oldbzbh char(60)declare @ oldzl char(20)
declare @olddj char (10)
declare @ new cjmc char(100)declare @ newlyrq datetime
declare @ newzbh char(60)declare @ newzl char(20)
declare @newdj char (10)
declare @xgr char (20)
select @oldcjmc=cjmc,@oldlyrq=lyrq,@oldbzbh=bzbh,@oldzl=zl,@olddj=dj from deleted
select @newcjmc=cjmc,@newlyrq=lyrq,@ newbzbh=bzbh,@newzl=zl,@newdj=dj from inserted
select @ xgr=xgr from t _ modifyuser where @ wtbh=wtbh
if @oldcjmc @newcjmc
开始
插入t_modifylog (wtbh,mod_time,mod_table,mod_field,ori_value,now_value,mod_people)值
(@wtbh,getdate(), chl_lydj , cjmc ,@oldcjmc,@newcjmc,@xgr)
目标
目标
//////////修改时,直接把创建改为"改变"即可
/////////////////////////
在dbo.ly_tzk上创建触发器[触发ly_tzk_syf]
用于插入
如同
开始
declare @ clmc char(100)declare @ dwbh char(100)declare @ syf char(100)declare @ dwgcbh char(100)declare @ wtbh char(50)
申报@dj_1金钱申报@费勇_z金钱申报@费勇_xf金钱申报@费勇_sy金钱
declare @dj char(20)
select @wtbh=wtbh,@clmc=clmc,@dwbh=dwbh,@syf=syf from inserted
select @dj=dj from郝菲_bz其中clmc=@clmc
选择@费勇_z=费勇_ z,@费勇_xf=费勇_ xf,@费勇_ sy=费勇_sy来自恭城新禧其中dwgcbh=@dwbh
set @dj_1=convert(money,@dj)
if @dj_1 0
开始
设置@费勇_ xf=@费勇_xf @dj_1
设置@费勇_ sy=@费勇_sy-@dj_1
更新ly _ tzk设置YF=@ djwherewtbh=@ wtbh
更新恭城新禧setfeiyong _xf=@费勇_ xf,费勇_ sy=@费勇_sywheredwgcbh=@dwbh
目标
否则更新ly_tzk set syf=convert(char,0.0) wherewtbh=@wtbh
目标
//////////////////////
在dbo.ly_tzk上创建触发器[触发ly_tzk_syf_shanchu]
用于删除
如同
开始
declare @ clmc char(100)declare @ dwbh char(100)declare @ dwgcbh char(100)declare @ wtbh char(50)
申报@费勇_z钱申报@费勇_xf钱申报@费勇_sy钱
声明@syf char(100)声明@syf_1钱
-declare @ DJ char(20)declare @ DJ _ 1 money
select @wtbh=wtbh,@clmc=clmc,@dwbh=dwbh,@syf=syf from inserted
- select @dj=dj from郝菲_bz其中clmc=@clmc
选择@费勇_z=费勇_ z,@费勇_xf=费勇_ xf,@费勇_ sy=费勇_sy来自恭城新禧其中dwgcbh=@dwbh
set @syf_1=convert(money,@syf)
if @syf_1 0
开始
设置@费勇_ xf=@费勇_xf-@syf_1
设置@费勇_ sy=@费勇sy @syf_1
更新恭城新禧setfeiyong _xf=@费勇_ xf,费勇_ sy=@费勇_sywheredwgcbh=@dwbh
目标
目标
//////////////////////
来自:http://www。cn博客。com/Blog-/archive/2011/03/06。超文本标记语言
郑重声明:本文由网友发布,不代表盛行IT的观点,版权归原作者所有,仅为传播更多信息之目的,如有侵权请联系,我们将第一时间修改或删除,多谢。