SqlServer中触发器的使用 .

1. --案例表 ??
2. USE?stuDB???
3. GO??
4. CREATE??TABLE??stuInfo??
5. (??
6. stuName??varchar(20)??not?null?,??
7. stuNo???char(6)??PRIMARY?KEY,??
8. stuAge??int?not?null?check(stuAge>0?and?stuAge<100),???
9. stuID??varchar(18)?not?null,???
10. stuSeat???int?identity(1,1),??
11. stuAddress???varchar(50)?default?'住址不详'??
12. )??
13. GO??
14. insert?into?stuInfo(stuName,stuNo,stuAge,stuId)??
15. select?'1','010001',21,'421990198909112311'?union??
16. select?'2','010002',22,'421990198909111342'?union??
17. select?'3','010003',23,'421990198909111242'?union??
18. select?'4','010004','421990198909111278'?union??
19. select?'5','010005','421990198909114556'?union??
20. select?'6','010006','421990198909117845'?union??
21. select?'7','010007',24,'421990198909112345'?union??
22. select?'8','010008',20,'421990198909117457'?union??
23. select?'9','010009','421990198909111557'?union??
24. select?'蒋雯丽','010010','421990198909111905'???
25. go??
26. CREATE?TABLE?stuMarks??
27. (??
28. ExamNo??CHAR(7)??primary?key,??
29. stuNo??CHAR(6)??NOT?NULL?references?stuInfo(stuNo),??
30. writtenExam??INT??NOT?NULL,??
31. LabExam??INT??NOT?NULL??
32. )??
33. GO??
34. insert?into?stuMarks??
35. ????select?'09001',58,68?union??
36. ????select?'09002',66,77?union??
37. ????select?'09003',86,45?union??
38. ????select?'09004',62,62?union??
39. ????select?'09005',67,54?union??
40. ????select?'09006',78,69?union??
41. ????select?'09007',60,83?union??
42. ????select?'09008',48,74?union??
43. ????select?'09009',54,69?union??
44. ????select?'09010',61,55???
45. ??
46. --创建登录触发器 ??
47. --限制sa用户只能登陆3次 ??
48. CREATE?TRIGGER?connection_limit_trigger??
49. ON?ALL?SERVER??
50. FOR?LOGON??
51. AS??
52. BEGIN??
53. IF?ORIGINAL_LOGIN()=?'sa'?AND??
54. ????(SELECT?COUNT(*)?FROM?sys.dm_exec_sessions??
55. ????????????WHERE?is_user_process?=?1?AND??
56. ????????????????original_login_name?=?'sa')?>?3??
57. ????ROLLBACK;??
58. END;??
59. ??
60. use?BOOK??
61. go??
62. create?trigger?create_trigger??
63. on?database??
64. for?create_table??
65. as??
66. ????print?'正在创建表'??
67. go??
68. ??
69. --禁止用户删除和修改表 ??
70. use?BOOK??
71. go??
72. create?trigger?deny_drop_table??
73. on?database??
74. for?drop_table,alter_table??
75. as??
76. begin??
77. ????print?'不允许删除和修改表'??
78. ????rollback?tran??
79. end??
80. --测试触发器 ??
81. alter?table?test?add?tname?varchar(20)?not?null??
82. --禁用DDL触发器 ??
83. disable?trigger?触发器名?on?all?server??
84. disable?trigger?触发器名?on?database??
85. --启用DDL触发器 ??
86. enable?trigger?触发器名?on?all?server??
87. enable?trigger?触发器名?on?database??
88. --删除DDL触发器 ??
89. drop?trigger?触发器名?on?all?server??
90. drop?trigger?触发器名?on?database??
91. ??
92. --after?insert触发器 ??
93. select?*?from?stuinfo??
94. select?*?from?stumarks??
95. --限制用户插入年龄》30?或《18的信息 ??
96. --删除触发器 ??
97. drop?trigger?check_insert_stuinfo??
98. ??
99. create?trigger?check_insert_stuinfo??
100. on?stuinfo??
101. for?insert??
102. as??
103. begin??
104. ????declare?@age?int??
105. ????--获取当前用户插入的数据 ??
106. ????select?@age=stuage?from?inserted??
107. ????--判断年龄信息 ??
108. ????if?@age>30?or?@age<18??
109. ????begin??
110. ????????raiserror('年龄数据必须在18-30之间',16,1)??
111. ????????rollback?tran??
112. ????end??
113. end??
114. ??
115. --测试触发器 ??
116. insert?into?stuinfo(stuname,stuno,stuage,stuid,stuaddress)??
117. ????values('CCC','010014','555666',default)??
118. SELECT?*?FROM?STUINFO??
119. ??
120. --After?delete触发器 ??
121. --禁止用户删除信息 ??
122. create?trigger?deny_delete_stuinfo??
123. on?stuinfo??
124. for?delete??
125. as??
126. begin??
127. ????declare?@name?varchar(20)??
128. ????select?@name=stuname?from?deleted??
129. ????if?@name='李斯文'?or?@name='梅超风'??
130. ????begin??
131. ????????raiserror('不允许删除指定的学员信息',1)??
132. ????????rollback?tran??
133. ????end??
134. end??
135. ??
136. --备份删除的信息 ??
137. --创建备份表 ??
138. select?*?into?StuBak?from?stuinfo?where?1=2??
139. ????--删除列stuseat ??
140. ????alter?table?stubak?drop?column?stuseat??
141. ????--添加列stuseat ??
142. ????alter?table?stubak?add?stuseat?int???
143. ??
144. --创建触发器 ??
145. create?trigger?delete_bak_stuinfo??
146. on?stuinfo??
147. for?delete??
148. as??
149. ????insert?into?stubak(stuname,stuseat,stuaddress)?select?*?from?deleted??
150. ??
151. --测试???? ??
152. delete?from?stuinfo?where?stuage>30??
153. select?*?from?stubak??
154. ??
155. ??
156. --After?update触发器 ??
157. create?trigger?update_stumarks??
158. on?stumarks??
159. for?update??
160. as??
161. begin??
162. ????--如何判断有没有更新writtenEXAM和labExam ??
163. ????if?update(writtenExam)?or?update(labExam)??
164. ????begin??
165. ????????raiserror('成绩字段不能为更新',1)??
166. ????????rollback?tran??
167. ????end??
168. end??
169. --测试触发器 ??
170. update?stumarks?set?labexam=labexam+10??
171. ??
172. --日志审计 ??
173. create?table?tb_log??
174. (??
175. ????log_id?int?identity(1,1)?primary?key,??
176. ????username?varchar(20)?not?null,??
177. ????log_date?datetime,??
178. ????log_desc?varchar(100)??
179. )??
180. create?trigger?log_trigger??
181. on?stuinfo??
182. for?insert,delete,update??
183. as??
184. ????--获取当前登录用户 ??
185. ????declare?@name?varchar(20)??
186. ????set?@name=ORIGINAL_LOGIN()??
187. ????--获取当前操作时间 ??
188. ????declare?@date?datetime??
189. ????set?@date=getdate()??
190. ????declare?@desc?varchar(100)??
191. ????if?exists(select?*?from?inserted)?and?not?exists(select?*?from?deleted)??
192. ????set?@desc='插入数据'??
193. ????else?if(exists(select?*?from?deleted)?and?not?exists(select?*?from?inserted))??
194. ????set?@desc='删除数据'??
195. ????else??
196. ????set?@desc='修改数据'??
197. ????insert?into?tb_log?values(@name,@date,@desc)??
198. go??
199. ??
200. insert?into?StuInfo(stuname,stuid)???
201. ????values('AAA','001','123456')??
202. select?*?from?tb_log??
203. ??
204. --instead?of触发器 ??
205. create?table?stu??
206. (??
207. ????sid?int,??
208. ????sname?varchar(20)??
209. )??
210. create?table?computer??
211. (??
212. ????sid?int,??
213. ????marks?float??
214. )??
215. insert?into?stu?values(1,'AAA')??
216. insert?into?stu?values(2,'BBB')??
217. insert?into?stu?values(3,'CCC')??
218. ??
219. insert?into?computer?values(1,'60')??
220. insert?into?computer?values(2,'70')??
221. insert?into?computer?values(3,'80')??
222. select?*?from?stu??
223. select?*?from?computer??
224. --创建视图 ??
225. create?view?view_stu_computer??
226. as??
227. ????select?stu.sid,sname,marks?from?stu,computer??
228. ????where?stu.sid=computer.sid??
229. go??
230. --查询视图 ??
231. --视图基于一张表创建，可以对视图实施增、删、改操作 ??
232. --视图基于多张表创建，不允许对视图实施。。。（在视图上创建instead?of触发器） ??
233. select?*?from?view_stu_computer??
234. insert?into?view_stu_computer?values(4,'DDD',90)??
235. delete?from?view_stu_computer?where?sid=4??
236. ??
237. create?trigger?insert_view_stu_computer??
238. on?view_stu_computer??
239. instead?of?insert??
240. as??
241. ????--从inserted表中获取插入的数据 ??
242. ????declare?@id?int,@name?varchar(20),@marks?float??
243. ????select?@id=sid,@name=sname,@marks=marks?from?inserted??
244. ????--向基表中插入数据 ??
245. ????insert?into?stu?values(@id,@name)??
246. ????insert?into?computer?values(@id,@marks)??
247. go??
248. ??
249. create?trigger?delete_view_stu_computer??
250. on?view_stu_computer??
251. instead?of?delete??
252. as??
253. ????--从deleted表中获取正在删除的编号 ??
254. ????declare?@id?int??
255. ????select?@id=sid?from?deleted??
256. ????--从基表删除数据 ??
257. ????delete?from?computer?where?sid=@id??
258. ????delete?from?stu?where?sid=@id??
259. go??

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!