SQL触发器实例(下)

  1 基本语法:
  2 Create Trigger [TriggerName] 
  3 ON [TableName] 
  4 FOR [Insert][,Delete][,Update]
  5 AS
  6 --触发器要执行的操作语句.
  7 Go
  8 
  9 注意:
 10 触发器中不允许以下 Transact-SQL 语句:
 11 Alter DATABASE ,Create DATABASE,DISK INIT, 
 12 DISK RESIZE, Drop DATABASE, LOAD DATABASE, 
 13 LOAD LOG, RECONFIGURE, RESTORE DATABASE, 
 14 RESTORE LOG
 15 
 16 触发器语句中使用了两种特殊的表:deleted 表和 inserted 表。
 17 Deleted 表用于存储 DELETE 和 UPDATE 语句所影响的行的复本。在执行 DELETE 或 UPDATE 语句时,行从触发器表中删除,并传输到 deleted 表中。Deleted 表和触发器表通常没有相同的行。
 18 
 19 Inserted 表用于存储 INSERT 和 UPDATE 语句所影响的行的副本。在一个插入或更新事务处理中,新建行被同时添加到 inserted 表和触发器表中。Inserted 表中的行是触发器表中新行的副本。
 20 
 21 1.插入操作(Insert) 
 22 Inserted表有数据,Deleted表无数据 
 23 
 24 2.删除操作(Delete) 
 25 Inserted表无数据,Deleted表有数据 
 26 
 27 3.更新操作(Update) 
 28 Inserted表有数据(新数据),Deleted表有数据(旧数据)
 29 
 30 1.) 创建测试用的表(testTable)
 31 if exists (select * from sysobjects where id = object_id(N'testTable') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
 32 drop table testTable
 33 GO
 34 Create Table testTable(testField varchar(50))
 35 
 36 2.) 创建基于表(testTable)的触发器(testTrigger)
 37 IF EXISTS (Select name FROM sysobjects Where name = 'testTrigger' AND type = 'TR')
 38 Drop TRIGGER testTrigger
 39 GO
 40 Create Trigger testTrigger 
 41 ON testTable 
 42 FOR Insert,Delete,Update
 43 AS
 44 if exists(select * from inserted)
 45      if exists(select * from deleted)
 46          print '...更新'
 47      else
 48          print '...插入'
 49 else
 50      if exists(select * from deleted)
 51          print '...删除'
 52 Go
 53 
 54 3.) 操作testTable表,测试触发器testTrigger
 55 分别执行Insert Into语句,Update语句,Delete语句,看看效果
 56 Insert Into testTable values ('testContent!')
 57 
 58 Update testTable Set testField = 'UpdateContent'
 59 
 60 Delete From testTable
 61 
 62 00
 63 
 64 SQL触发器实例1
 65 
 66       我为什么要使用触发器?比如,这么两个表: 
 67 
 68       Create Table Student(              --学生表 
 69         StudentID int primary key,       --学号 
 70         .... 
 71        ) 
 72 
 73       Create Table BorrowRecord(               --学生借书记录表 
 74         BorrowRecord   int identity(1,1),       --流水号   
 75         StudentID      int ,                    --学号 
 76         BorrowDate     datetime,                --借出时间 
 77         ReturnDAte     Datetime,                --归还时间 
 78         ... 
 79       ) 
 80 
 81      用到的功能有: 
 82         1.如果我更改了学生的学号,我希望他的借书记录仍然与这个学生相关(也就是同时更改借书记录表的学号); 
 83         2.如果该学生已经毕业,我希望删除他的学号的同时,也删除它的借书记录。 
 84      等等。 
 85 
 86      这时候可以用到触发器。对于1,创建一个Update触发器: 
 87 
 88      Create Trigger truStudent 
 89        On Student                         --在Student表中创建触发器 
 90        for Update                          --为什么事件触发 
 91      As                                        --事件触发后所要做的事情 
 92        if Update(StudentID)            
 93        begin 
 94 
 95          Update BorrowRecord 
 96            Set StudentID=i.StudentID 
 97            From BorrowRecord br , Deleted   d ,Inserted i      --Deleted和Inserted临时表 
 98            Where br.StudentID=d.StudentID 
 99 
100        end        
101                  
102      理解触发器里面的两个临时的表:Deleted , Inserted 。注意Deleted 与Inserted分别表示触发事件的表“旧的一条记录”和“新的一条记录”。 
103      一个数据库系统中有两个虚拟表用于存储在表中记录改动的信息,分别是: 
104                              虚拟表Inserted                     虚拟表Deleted 
105 
106 在表记录新增时     存放新增的记录                         不存储记录 
107          修改时           存放用来更新的新记录                   存放更新前的记录 
108          删除时           不存储记录                             存放被删除的记录 
109 
110 
111      一个Update 的过程可以看作为:生成新的记录到Inserted表,复制旧的记录到Deleted表,然后删除Student记录并写入新纪录。 
112 
113      对于2,创建一个Delete触发器 
114      Create trigger trdStudent 
115        On Student 
116        for Delete 
117      As 
118        Delete BorrowRecord 
119          From BorrowRecord br , Delted d 
120          Where br.StudentID=d.StudentID 
121 
122      从这两个例子我们可以看到了触发器的关键:A.2个临时的表;B.触发机制。 
123 
124 SQL触发器实例2
125 /* 
126 建立虚拟测试环境,包含:表[卷烟库存表],表[卷烟销售表]。 
127 请大家注意跟踪这两个表的数据,体会触发器到底执行了什么业务逻辑,对数据有什么影响。 
128 为了能更清晰的表述触发器的作用,表结构存在数据冗余,且不符合第三范式,这里特此说明。 
129 */ 
130 USE Master 
131 GO 
132 
133 IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE XTYPE = ’U’ AND NAME = ’卷烟库存表’) 
134 DROP TABLE 卷烟库存表 
135 GO 
136 IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE XTYPE = ’U’ AND NAME = ’卷烟销售表’) 
137 DROP TABLE 卷烟销售表 
138 GO 
139 
140 --业务规则:销售金额 = 销售数量 * 销售单价 业务规则。 
141 
142 CREATE TABLE 卷烟销售表 
143 ( 
144 卷烟品牌 VARCHAR(40) PRIMARY KEY NOT NULL, 
145 购货商 VARCHAR(40) NULL, 
146 销售数量 INT NULL, 
147 销售单价 MONEY NULL, 
148 销售金额 MONEY NULL 
149 ) 
150 GO 
151 
152 --业务规则:库存金额 = 库存数量 * 库存单价 业务规则。 
153 
154 CREATE TABLE 卷烟库存表 
155 ( 
156 卷烟品牌 VARCHAR(40) PRIMARY KEY NOT NULL, 
157 库存数量 INT NULL, 
158 库存单价 MONEY NULL, 
159 库存金额 MONEY NULL 
160 ) 
161 GO 
162 
163 --创建触发器,示例1 
164 
165 /* 
166 创建触发器[T_INSERT_卷烟库存表],这个触发器较简单。 
167 说明: 每当[卷烟库存表]发生 INSERT 动作,则引发该触发器。 
168 触发器功能: 强制执行业务规则,保证插入的数据中,库存金额 = 库存数量 * 库存单价。 
169 注意: [INSERTED]、[DELETED]为系统表,不可创建、修改、删除,但可以调用。 
170 重要: 这两个系统表的结构同插入数据的表的结构。 
171 */ 
172 IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE XTYPE = ’TR’ AND NAME = ’T_INSERT_卷烟库存表’) 
173 DROP TRIGGER T_INSERT_卷烟库存表 
174 GO 
175 
176 CREATE TRIGGER T_INSERT_卷烟库存表 
177 ON 卷烟库存表 
178 FOR INSERT 
179 AS 
180 --提交事务处理 
181 BEGIN TRANSACTION 
182 --强制执行下列语句,保证业务规则 
183 UPDATE 卷烟库存表 
184 SET 库存金额 = 库存数量 * 库存单价 
185 WHERE 卷烟品牌 IN (SELECT 卷烟品牌 from INSERTED) 
186 COMMIT TRANSACTION 
187 GO 
188 
189 /* 
190 针对[卷烟库存表],插入测试数据: 
191 注意,第一条数据(红塔山新势力)中的数据符合业务规则, 
192 第二条数据(红塔山人为峰)中,[库存金额]空,不符合业务规则, 
193 第三条数据(云南映像)中,[库存金额]不等于[库存数量]乘以[库存单价],不符合业务规则。 
194 第四条数据库存数量为0。 
195 请注意在插入数据后,检查[卷烟库存表]中的数据是否 库存金额 = 库存数量 * 库存单价。 
196 */ 
197 
198 INSERT INTO 卷烟库存表(卷烟品牌,库存数量,库存单价,库存金额) 
199 SELECT ’红塔山新势力’,100,12,1200 UNION ALL 
200 SELECT ’红塔山人为峰’,100,22,NULL UNION ALL 
201 SELECT ’云南映像’,100,60,500 UNION ALL 
202 SELECT ’玉溪’,0,30,0 
203 GO 
204 
205 --查询数据 
206 
207 SELECT * FROM 卷烟库存表 
208 GO 
209 /* 
210 
211 结果集 
212 
213 RecordId 卷烟品牌 库存数量 库存单价 库存金额 
214 -------- ------------ -------- ------- --------- 
215 1 红塔山新势力 100 12.0000 1200.0000 
216 2 红塔山人为峰 100 22.0000 2200.0000 
217 3 云南映像 100 60.0000 6000.0000 
218 4 玉溪 0 30.0000 .0000 
219 
220 (所影响的行数为 4 行) 
221 
222 */ 
223 
224 --触发器示例2 
225 
226 /* 
227 创建触发器[T_INSERT_卷烟销售表],该触发器较复杂。 
228 说明: 每当[卷烟库存表]发生 INSERT 动作,则引发该触发器。 
229 触发器功能: 实现业务规则。 
230 业务规则: 如果销售的卷烟品牌不存在库存或者库存为零,则返回错误。 
231 否则则自动减少[卷烟库存表]中对应品牌卷烟的库存数量和库存金额。 
232 */ 
233 IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE XTYPE = ’TR’ AND NAME = ’T_INSERT_卷烟销售表’) 
234 DROP TRIGGER T_INSERT_卷烟销售表 
235 GO 
236 
237 CREATE TRIGGER T_INSERT_卷烟销售表 
238 ON 卷烟销售表 
239 FOR INSERT 
240 AS 
241 BEGIN TRANSACTION 
242 --检查数据的合法性:销售的卷烟是否有库存,或者库存是否大于零 
243 IF NOT EXISTS ( 
244 SELECT 库存数量 
245 FROM 卷烟库存表 
246 WHERE 卷烟品牌 IN (SELECT 卷烟品牌 FROM INSERTED) 
247 ) 
248 BEGIN 
249 --返回错误提示 
250 RAISERROR(’错误!该卷烟不存在库存,不能销售。’,16,1) 
251 --回滚事务 
252 ROLLBACK 
253 RETURN 
254 END 
255 
256 IF EXISTS ( 
257 SELECT 库存数量 
258 FROM 卷烟库存表 
259 WHERE 卷烟品牌 IN (SELECT 卷烟品牌 FROM INSERTED) AND 
260 库存数量 <= 0 
261 ) 
262 BEGIN 
263 --返回错误提示 
264 RAISERROR(’错误!该卷烟库存小于等于0,不能销售。’,16,1) 
265 --回滚事务 
266 ROLLBACK 
267 RETURN 
268 END 
269 
270 --对合法的数据进行处理 
271 
272 --强制执行下列语句,保证业务规则 
273 UPDATE 卷烟销售表 
274 SET 销售金额 = 销售数量 * 销售单价 
275 WHERE 卷烟品牌 IN (SELECT 卷烟品牌 FROM INSERTED) 
276 
277 DECLARE @卷烟品牌 VARCHAR(40) 
278 SET @卷烟品牌 = (SELECT 卷烟品牌 FROM INSERTED) 
279 
280 DECLARE @销售数量 MONEY 
281 SET @销售数量 = (SELECT 销售数量 FROM INSERTED) 
282 
283 UPDATE 卷烟库存表 
284 SET 库存数量 = 库存数量 - @销售数量, 
285 库存金额 = (库存数量 - @销售数量)*库存单价 
286 WHERE 卷烟品牌 = @卷烟品牌 
287 COMMIT TRANSACTION 
288 GO 
289 
290 --请大家自行跟踪[卷烟库存表]和[卷烟销售表]的数据变化。 
291 --针对[卷烟销售表],插入第一条测试数据,该数据是正常的。 
292 
293 INSERT INTO 卷烟销售表(卷烟品牌,购货商,销售数量,销售单价,销售金额) 
294 SELECT ’红塔山新势力’,’某购货商’,10,12,1200 
295 GO 
296 
297 --针对[卷烟销售表],插入第二条测试数据,该数据 销售金额 不等于 销售单价 * 销售数量。 
298 --触发器将自动更正数据,使 销售金额 等于 销售单价 * 销售数量。 
299 
300 INSERT INTO 卷烟销售表(卷烟品牌,购货商,销售数量,销售单价,销售金额) 
301 SELECT ’红塔山人为峰’,’某购货商’,10,22,2000 
302 GO 
303 
304 --针对[卷烟销售表],插入第三条测试数据,该数据中的卷烟品牌在 卷烟库存表中找不到对应。 
305 --触发器将报错。 
306 
307 INSERT INTO 卷烟销售表(卷烟品牌,购货商,销售数量,销售单价,销售金额) 
308 SELECT ’红河V8’,’某购货商’,10,60,600 
309 GO 
310 
311 /* 
312 结果集 
313 服务器: 消息 50000,级别 16,状态 1,过程 T_INSERT_卷烟销售表,行 15 
314 错误!该卷烟不存在库存,不能销售。 
315 */ 
316 
317 --针对[卷烟销售表],插入第三条测试数据,该数据中的卷烟品牌在 卷烟库存表中库存为0。 
318 --触发器将报错。 
319 
320 INSERT INTO 卷烟销售表(卷烟品牌,购货商,销售数量,销售单价,销售金额) 
321 SELECT ’玉溪’,’某购货商’,10,30,300 
322 GO 
323 
324 /* 
325 结果集 
326 服务器: 消息 50000,级别 16,状态 1,过程 T_INSERT_卷烟销售表,行 29 
327 错误!该卷烟库存小于等于0,不能销售。 
328 */ 
329 --查询数据 
330 SELECT * FROM 卷烟库存表 
331 
332 SELECT * FROM 卷烟销售表 
333 GO 
334 
335 /* 
336 补充: 
337 1、本示例主要通过一个简单的业务规则实现来进行触发器使用的说明,具体的要根据需要灵活处理; 
338 2、关于触发器要理解并运用好 INSERTED ,DELETED 两个系统表; 
339 3、本示例创建的触发器都是 FOR INSERT ,具体的语法可参考: 
340 
341 Trigger语法 
342 
343 CREATE TRIGGER trigger_name 
344 ON { table | view } 
345 [ WITH ENCRYPTION ] --用于加密触发器 
346 { 
347 { { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] } 
348 [ WITH APPEND ] 
349 [ NOT FOR REPLICATION ] 
350 AS 
351 [ { IF UPDATE ( column ) 
352 [ { AND | OR } UPDATE ( column ) ] 
353 [ ...n ] 
354 | IF ( COLUMNS_UPDATED ( ) { bitwise_operator } updated_bitmask ) 
355 { comparison_operator } column_bitmask [ ...n ] 
356 } ] 
357 sql_statement [ ...n ] 
358 } 
359 } 
360 
361 4、关于触发器,还应该注意 
362 (1)、DELETE 触发器不能捕获 TRUNCATE TABLE 语句。 
363 (2)、触发器中不允许以下 Transact-SQL 语句: 
364 ALTER DATABASE CREATE DATABASE DISK INIT 
365 DISK RESIZE DROP DATABASE LOAD DATABASE 
366 LOAD LOG RECONFIGURE RESTORE DATABASE 
367 RESTORE LOG 
368 (3)、触发器最多可以嵌套 32 层。 
369 
370 */ 
371 
372 --修改触发器 
373 --实质上,是将 CREATE TRIGGER ... 修改为 ALTER TRIGGER ...即可。 
374 
375 --删除触发器 
376 DROP TRIGGER xxx 
377 GO 
378 
379 --删除测试环境 
380 DROP TABLE 卷烟库存表 
381 GO 
382 DROP TABLE 卷烟销售表 
383 GO 
384 DROP TRIGGER T_INSERT_卷烟库存表 
385 GO 
386 DROP TRIGGER T_INSERT_卷烟销售表 
387 GO 
388 ################################################################## 
389 触发器的基础知识和例子 
390 :create trigger tr_name 
391 on table/view 
392 {for | after | instead of } [update][,][insert][,][delete] 
393 [with encryption] 
394 as {batch | if update (col_name) [{and|or} update (col_name)] } 
395 
396 说明: 
397 1 tr_name :触发器名称 
398 2 on table/view :触发器所作用的表。一个触发器只能作用于一个表 
399 3 for 和after :同义 
400 4 after 与instead of :sql 2000新增项目afrer 与 instead of 的区别 
401 After 
402 在触发事件发生以后才被激活,只可以建立在表上 
403 Instead of 
404 代替了相应的触发事件而被执行,既可以建立在表上也可以建立在视图上 
405 5 insert、update、delete:激活触发器的三种操作,可以同时执行,也可选其一 
406 6 if update (col_name):表明所作的操作对指定列是否有影响,有影响,则激活触发器。此外,因为delete 操作只对行有影响, 
407 所以如果使用delete操作就不能用这条语句了(虽然使用也不出错,但是不能激活触发器,没意义)。 
408 7 触发器执行时用到的两个特殊表:deleted ,inserted 
409 deleted 和inserted 可以说是一种特殊的临时表,是在进行激活触发器时由系统自动生成的,其结构与触发器作用的表结构是一 
410 样的,只是存放 的数据有差异。 
411 
412 续 
413 下面表格说明deleted 与inserted 数据的差异 
414 deleted 与inserted 数据的差异 
415 Inserted 
416 存放进行insert和update 操作后的数据 
417 Deleted 
418 存放进行delete 和update操作前的数据 
419 注意:update 操作相当于先进行delete 再进行insert ,所以在进行update操作时,修改前的数据拷贝一条到deleted 表中,修改后 
420 的数据在存到触发器作用的表的同时,也同时生成一条拷贝到insered表中

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏Python数据科学

【SQL刷题系列】:leetcode183 Customers Who Never Order

假设一个网站包含两个表: Customers和Orders。写出一个SQL查询语句找出所有没有任何订单的顾客。

1152
来自专栏杨建荣的学习笔记

通过图表简化sql语句的表关联(r4笔记第70天)

在之前的博文中分享过一个执行了两天的一条sql语句,走了两个大表的扫描,导致执行时间很长,通过简化sql做了不小的改进,今天我们来看看还可以做些什么。 上次简化...

3634
来自专栏数据和云

性能为王:SQL标量子查询的优化案例分析

黄廷忠(网名:认真就输) 云和恩墨技术专家 个人博客:http://www.htz.pw/ 本篇整理内容是黄廷忠在“云和恩墨大讲堂”微信分享中的讲解案例,S...

3765
来自专栏数据和云

SQL为王:oracle标量子查询和表连接改写

小鱼(邓秋爽) 云和恩墨专家,有超过5年超大型数据库专业服务经验,擅长oracle 数据库优化、SQL优化和troubleshooting 编辑手记:如何提高数...

4576
来自专栏小怪聊职场

MySQL(八)|MySQL中In与Exists的区别(2)

1773
来自专栏java达人

SQL索引优化

序言 数据库的优化方法有很多种,在应用层来说,主要是基于索引的优化。本次秘笈根据实际的工作经验,在研发原来已有的方法的基础上,进行了一些扩充,总结了基于索引的S...

2028
来自专栏数据之美

关于mysql 索引自动优化机制: 索引选择性(Cardinality:索引基数)

1、两个同样结构的语句一个没有用到索引的问题: 查1到20号的就不用索引,查1到5号的就用索引,为什么呢?不稳定? mysql> expla...

3388
来自专栏杨建荣的学习笔记

一条SQL语句的执行计划变化探究(r10笔记第3天)

最近有个同事碰到一个问题,想让我给点思路。我大体了解了一下,是一个系统目前在做压力测试,但是经业务反馈发现某个环节的处理时间有些长,排查了一圈,最后这件事情就落...

3296
来自专栏程序猿DD

一个不可思议的MySQL慢查分析与解决

前言 开发需要定期的删除表里一定时间以前的数据,SQL如下 mysql > delete from testtable WHERE biz_date <= '2...

3465
来自专栏Grace development

老项目重构手记之用户系统

重构首先要注意几个点 – 重构后功能的可扩展性 – 业务互相依赖的复杂度 – 脱离本身的业务进行重构 – 重构后的代码可读性与可维护性 – 性能的提升...

1222

扫码关注云+社区

领取腾讯云代金券