SQL触发器实例(上)

1 --1.) 创建测试用的表(testTable)
  2 if exists (select * from sysobjects where name='testTable')
  3 drop table testTable
  4 GO
  5 Create Table testTable
  6 (
  7 testField varchar(50)
  8 )
  9 
 10  select * from testTable
 11 
 12 
 13 
 14 --2.) 创建基于表(testTable)的触发器(testTrigger)
 15 IF EXISTS (Select name FROM sysobjects Where name = 'testTrigger' AND type = 'TR')
 16 Drop TRIGGER testTrigger
 17 GO
 18 Create Trigger testTrigger 
 19 ON testTable 
 20 for Insert,Delete,Update
 21 AS
 22 if exists(select * from inserted)
 23      if exists(select * from deleted)
 24          print '...更新'
 25      else
 26          print '...插入'
 27 else
 28      if exists(select * from deleted)
 29          print '...删除'
 30 Go
 31 
 32 
 33 --.) 操作testTable表,测试触发器testTrigger
 34 --分别执行Insert Into语句,Update语句,Delete语句,看看效果
 35 Insert Into testTable values ('testContent!')
 36 
 37 Update testTable Set testField = 'UpdateContent'
 38 
 39 Delete From testTable
 40 
 41 select * from testTable
 42 
 43 
 44 --用到的功能有: 
 45         --1.如果我更改了学生的学号,我希望他的借书记录仍然与这个学生相关(也就是同时更改借书记录表的学号); 
 46         --2.如果该学生已经毕业，我希望删除他的学号的同时，也删除它的借书记录，等等。
 47 
 48  --这时候可以用到触发器。对于需求1，创建一个Update触发器： 
 49 
 50      Create Trigger truStudent 
 51 
 52        On student      --在Student表中创建触发器 
 53        instead of Update      --为什么事件触发 
 54      As           --事件触发后所要做的事情 
 55        if Update(stuID)            
 56        begin 
 57 
 58          Update borrow 
 59            Set stuID=i.stuID 
 60            From borrow as br , Deleted as d ,Inserted as i      --Deleted和Inserted临时表 
 61            Where br.stuID=d.stuID 
 62 
 63        end   
 64 
 65 drop trigger truStudent
 66 UPDATE student set stuID='1006' WHERE stuID='1005'
 67 
 68 
 69     Create trigger trdStudent
 70        On Student 
 71        instead of Delete 
 72      As 
 73      Delete Borrow 
 74 　　　From Borrow as br , Deleted as d Where br.StuID=d.stuID 
 75 
 76 drop trigger trdStudent
 77 delete FROM student WHERE stuID='1004'
 78 
 79 disable trigger trdStudent on Student
 80 
 81 select * from book
 82 select * from borrow
 83 select * from student
 84 
 85 
 86 --创建触发器（对删除表的约束）
 87 create trigger droptabel
 88 on database
 89 for drop_table
 90 as
 91 print'删除表吗？'
 92 print'不能删除表'
 93 rollback transaction
 94 go
 95 
 96 drop table students
 97 disable trigger droptabel on database　　　　--关闭触发器
 98 enable trigger droptabel on database
 99 
100 
101 
102 
103 
104 CREATE TABLE students  --学生信息表
105 (
106   stuID CHAR(10) primary key,  --学生编号
107   stuName  CHAR(10) NOT NULL ,     --学生名称
108   major  CHAR(50) NOT NULL    --专业
109 )
110 GO
111 
112 
113 CREATE TABLE borrowS  --借书表
114 (
115  borrowID  CHAR(10) primary key,    --借书编号
116 stuID CHAR(10) NOT NULL, --学生编号
117 BID  CHAR(10) NOT NULL,--图书编号
118  T_time  datetime NOT NULL,   --借出日期
119  B_time  datetime    --归还日期
120 )
121 GO
122 
123 SElect * from students
124 select * from borrows
125 
126 Create Trigger truStudents 
127 
128        On students      --在Student表中创建触发器 
129        for Update      --为什么事件触发 
130      As           --事件触发后所要做的事情 
131        if Update(stuID)            
132        begin 
133 
134          Update borrows 
135            Set stuID=i.stuID 
136            From borrows as br , Deleted as d ,Inserted as i      --Deleted和Inserted临时表 
137            Where br.stuID=d.stuID 
138 
139        end   
140 
141 
142 
143 UPDATE studentS set stuID='1006' WHERE stuID='1005'
144 
145 
146     Create trigger trStudent
147        On Students 
148        for Delete 
149      As 
150        Delete borrows 
151          From borrows AS br , Deleted AS d 
152          Where br.stuID=d.stuID 
153 
154 
155 drop trigger trStudent
156 
157 
158 delete from students where stuID='1001'