| 订阅 | 在线投稿
分享
 
 
 

讲解Microsoft SQL Server的行式触发器

2008-07-11 05:57:45 编辑來源:互联网 国际版 评论
 
 
本文为【讲解Microsoft SQL Server的行式触发器】的汉字拼音对照版显示拼音
  zengjingyiweiSQL SERVERdechufaqizhinengchufadanxingyejiushishuoruguoyigedeletechufaqiruguotongshishanchuduoxingshizhihuiduidiyitiaojiluchufahoulaifaxianlebushirenjiaSQL SERVERbuzhichiershiounaozibenmeifaxian

  qishiinsertedhedeletedliangzhangneibubiaocunfanglesuoyouyaocharuhuoyaoshanchudejilukeyiyongcursorzhuci访fangwenlimiandemeitiaojiluxiamianshiyigeshiligaichufaqijiangyaoshanchudejiluzhuanyidaolingyizhangbiaozhong

  diyibuchuangjianzheliangzhangbiao

  create table table1([id] int primary key, [value] varchar(100))create table table2([id] int primary key, [value] varchar(100))

  dierbucharuceshishuju

  declare @i intset @i = 1while @i <= 100begin insert into table1([id], [value]) values(@i, cast(@i as varchar)) set @i = @i + 1end

  chuangjiantable1dedeletechufaqi

  create trigger tr_d_table1 on table1 for deleteasbegin declare @id int, @value varchar(100) declare cur_del cursor local forward_only for select [id], [value] from deleted open cur_del fetch next from cur_del into @id, @value while @@fetch_status = 0 begin insert into table2([id], [value]) values(@id, @value) fetch next from cur_del into @id, @value end close cur_del deallocate cur_del end

  xianzaiduitable1zhixingdeleteyujufaxiansuoyoubeishanchudejiludoujiluzaizaitable2zhongle

  delete from table1原文
 
 
曾经以为SQL SERVER的触发器只能触发单行,也就是说如果一个delete触发器,如果同时删除多行时,只会对第一条记录触发,后来发现了不是人家SQL SERVER不支持,而是偶脑子笨没发现。 其实inserted和deleted两张内部表存放了所有要插入或要删除的记录,可以用cursor逐次访问里面的每条记录,下面是一个示例,该触发器将要删除的记录转移到另一张表中: 第一步,创建这两张表 create table table1([id] int primary key, [value] varchar(100))create table table2([id] int primary key, [value] varchar(100)) 第二步,插入测试数据 declare @i intset @i = 1while @i <= 100begin insert into table1([id], [value]) values(@i, cast(@i as varchar)) set @i = @i + 1end 创建table1的delete触发器 create trigger tr_d_table1 on table1 for deleteasbegin declare @id int, @value varchar(100) declare cur_del cursor local forward_only for select [id], [value] from deleted open cur_del fetch next from cur_del into @id, @value while @@fetch_status = 0 begin insert into table2([id], [value]) values(@id, @value) fetch next from cur_del into @id, @value end close cur_del deallocate cur_del end 现在对table1执行delete语句,发现所有被删除的记录都记录在在table2中了 delete from table1
󰈣󰈤
 
 
>>返回首页<<
 为你推荐
 
 
 
 转载本文
 UBB代码 HTML代码
复制到剪贴板...
 
 
 热帖排行
 
 
王朝网络微信公众号
微信扫码关注本站公众号wangchaonetcn
 
  免责声明:本文仅代表作者个人观点,与王朝网络无关。王朝网络登载此文出于传递更多信息之目的,并不意味着赞同其观点或证实其描述,其原创性以及文中陈述文字和内容未经本站证实,对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
 
 
©2005- 王朝网络 版权所有