触发器相关sql sever 编程

1、触发器相关sql sever 编程一个典型的应用有两个表:user表(userid,username,userpwd,sex,departid) /userid主键department表(departid,departname,membercount) /departid主键两表建立了外键约束membercount是部门人数，在插入一个Userde的时候相应部门的membercount需要加1，删除的时候需要减1，修改departid的时候需要在改前部门减1，改后部门加1。想用触发器实现在更新user表时自动更新department表的membercount。-触发器代码create trig

2、ger tr_useron userfor insert,update,deleteasset nocount onupdate aset membercount=a.membercount+b.membercount           -b.membercount是这次操作的增量，可能为负数from department a,(select departid,sum(membercount) as membercount      

3、   -增量由统计得到from (select departid,1 as membercount                        -插入或者改后的部门+1from inserted        union allselect departid,-1 as mem

4、bercount                       -删除或者改前的部门-1from deleted) as tgroup by departidhaving sum(membercount)<>0             &#

5、160;                -只取<>0的数据减少更改的记录数) as bwhere a.departid=b.departid                         

6、;    -连接条件go这里只打算讲解四部分了，也就最简单、最常用的四部分。1、触发器。定义：何为触发器？在SQL Server里面也就是对某一个表的一定的操作，触发某种条件，从而执行的一段程序。触发器是一个特殊的存储过程。常见的触发器有三种：分别应用于Insert , Update , Delete 事件。(SQL Server 2000定义了新的触发器，这里不提）我为什么要使用触发器？比如，这么两个表：Create Table Student( -学生表StudentID int primary key, -学号.)Create Table BorrowRec

7、ord( -学生借书记录表BorrowRecord int identity(1,1), -流水号 StudentID int , -学号BorrowDate datetime, -借出时间ReturnDAte Datetime, -归还时间.)用到的功能有:1.如果我更改了学生的学号,我希望他的借书记录仍然与这个学生相关(也就是同时更改借书记录表的学号);2.如果该学生已经毕业，我希望删除他的学号的同时，也删除它的借书记录。等等。这时候可以用到触发器。对于1，创建一个Update触发器：Create Trigger truStudentOn Studentfor UpdateAsif Upd

8、ate(StudentID)beginUpdate BorrowRecord Set StudentID=i.StudentIDFrom BorrowRecord br , Deleted d ,Inserted i Where br.StudentID=d.StudentIDend 理解触发器里面的两个临时的表：Deleted , Inserted 。注意Deleted 与Inserted分别表示触发事件的表“旧的一条记录”和“新的一条记录”。一个Update 的过程可以看作为：生成新的记录到Inserted表，复制旧的记录到Deleted表，然后删除Student记录并写入新纪录。对于2，

9、创建一个Delete触发器Create trigger trdStudentOn Studentfor DeleteAsDelete BorrowRecord From BorrowRecord br , Delted dWhere br.StudentID=d.StudentID从这两个例子我们可以看到了触发器的关键：A.2个临时的表；B.触发机制。这里我们只讲解最简单的触发器。复杂的容后说明。事实上，我不鼓励使用触发器。触发器的初始设计思想，已经被“级联”所替代.触发器一个重要的作用是在修改一个表的数据的同时可以修改另一个表的数据，这里所说的修改是指包括插入、更新和删除操作。但是，在编写触

10、发器代码的时候，需要考虑各种可能的情况，使得触发器代码变得健壮，可以适应未来的各种变化。        一般来说，需要考虑以下情况：1、一次修改多条记录的情况，无论是插入、删除还是修改，都有可能一次操作多条记录，所以触发器中应该避免使用变量来取得inserted、deleted临时表的字段值,因为这样取值很可能只取得一条记录的字段值，造成其他记录未处理，而且这种错误在语法上没有任何报错，在大部分应用的时候（大部分应用一次只处理一条记录）不容易发现错误，而一旦发生错误的时候，又很难找出错误所在，因为很多时候我们不会首先怀疑触发

11、器的问题。所以个人更愿意把这类问题作为第一个问题提出。2、低效的语句，在触发器中，更应该注意语句的效率，因为每次对这个表的操作，都会调用这个触发器。效率问题首先是需要缩小数据修改的范围，充分利用inserted、deleted临时表连接相关表来减少数据操作的记录数。其次是触发器操作的的表最好都设置主键和聚集索引，既保证数据的一致性也可以提高代码的效率。3、递归和嵌套触发器，sql server安装的时候，默认是允许嵌套触发器和不允许直接递归触发器，有些应用可以关闭嵌套触发器选项来保证不会发生递归,但是大部分情况是需要打开嵌套触发器选项来保证数据的一致性的,关闭嵌套触发器选项是需要认真认证后才能

12、作出的决定。在打开嵌套触发器选项的情况下，需要很好的利用代码防止无限递归触发器的发生。4、必须注重触发器的测试，要在各种情况下（空表、有一条数据、有多条数据），各种可能的操作（插入、删除、修改、一条记录、多条记录等）都要测试。          下面以一个例子来说明编写触发器需要注意的地方，这是一个保持两个结构相同的表的数据一致的处罚其解决方案，要求任何一个表的改动在另一个表度需要作相应改动。首先，这个事例很容易引起触发器的间接递归，间接递归是应该允许的，但是必须防止无限间接递归的情况发生。所以在触发器中操作数据的

13、语句都增加了判断，只有在必须修改的时候才进行修改。其次，必须注意都多行修改的情况，所以必须都是连接inserted、deleted临时表进行数据操作。下面是代码和注释-建表create table T1(id int not null PRIMARY KEY, -这里必须有主键value int)gocreate table T2(    -这里必须有主键id int not null PRIMARY KEY,value int)go-触发器代码-T1 Insert 触发器Create Trigger tr_T1_Inserton T1for Insertasse

14、t nocount on   if exists (    -这个判断是防止无限递归的   select 1 from inserted i   where not exists (    select 1 from T2 where id=i.id    )   )insert T2   select * from inserted i   where not exists

15、( -必须有这个条件,防止重复插入    select 1 from T2 where id=i.id   )print '1I'    -报告触发器执行,只用于测试go-T2 Insert 触发器Create Trigger tr_T2_Inserton T2for Insertasset nocount onif exists (    -这个判断是防止无限递归的   select 1 from inserted i   wh

16、ere not exists (    select 1 from T1 where id=i.id    )   )insert T1   select * from inserted i   where not exists ( -必须有这个条件,防止重复插入    select 1 from T1 where id=i.id   )print '2I'    -报告触发器执行

17、,只用于测试go-T1 Update 触发器Create Trigger tr_T1_Updateon T1for updateasset nocount onif update (value)   -只有修改value才触发begin      if exists (    -这个判断是防止无限递归的     select 1       from T2,Inserted i,deleted d 

18、0;     where i.id=d.id       and i.id=T2.id       and i.value<>d.value   )       update T2          set value=i.Value       from

19、T2,Inserted i,deleted d       where i.id=d.id       and i.id=T2.id       and i.value<>d.value -这个条件保证只有真正修改了值才触发endprint '1U'    -报告触发器执行,只用于测试go-T2 Update 触发器Create Trigger tr_T2_Updateon T2for u

20、pdateasset nocount onif update (value)   -只有修改value才触发begin      if exists (    -这个判断是防止无限递归的       select 1       from T1,Inserted i,deleted d       where i.id=d.id   &#

21、160;   and i.id=T1.id       and i.value<>d.value      )       update T1           set value=i.Value       from T1,Inserted i,deleted d 

22、0;     where i.id=d.id       and i.id=T1.id       and i.value<>d.value -这个条件保证只有真正修改了值才触发endprint '2U'    -报告触发器执行,只用于测试go-T1 Delete 触发器Create Trigger tr_T1_deleteon T1for deleteasset nocount onif exists (&

23、#160;   -这个判断是防止无限递归的   select 1 from T2,deleted d   where T2.id=d.Id)delete T2from deleted dwhere T2.id=d.Idprint '1D'    -报告触发器执行,只用于测试go-T2 Delete 触发器Create Trigger tr_T2_deleteon T2for deleteasset nocount onif exists (    -这个判断是

24、防止无限递归的   select 1 from T1,deleted d   where T1.id=d.Id)delete T1from deleted dwhere T1.id=d.Idprint '2D'    -报告触发器执行,只用于测试go-测试-测试1insert T1 values(1,1)-结果12I1I-这说明调用了两个触发器-以下查看数据select * from T1id          valu

25、e       - - 1           1select * from T2id          value       - - 1           1-测试2insert T2

26、 select 2,2 union all select 3,3        -一次插入两条记录-结果21I2I-这说明调用了两个触发器-以下查看数据select * from T1id          value       - - 1           12 

27、          23           3select * from T2id          value       - - 1          

28、12           23           3-测试3update T1 set value=5where id=1-结果31U2U1U-这说明调用了两个触发器，其中1表的update触发器调用了两次-以下查看数据select * from T1id          value  

29、;     - - 1           52           23           3select * from T2id          value

30、60;      - - 1           52           23           3-测试4update T1 set value=value+1where id>1-结果41U2U1U-这说明调用了两个触发器，其中1表的u

31、pdate触发器调用了两次-以下查看数据select * from T1id          value       - - 1           52           33           4select * from T2id          value       - - 1           52