sqlserver触发器实例,sql触发器的使用及语法

  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的观点,版权归原作者所有,仅为传播更多信息之目的,如有侵权请联系,我们将第一时间修改或删除,多谢。

留言与评论(共有 条评论)
   
验证码: