触发器
1
/*
2
建立虚拟测试环境，包含：表[卷烟库存表]，表[卷烟销售表]。
3请大家注意跟踪这两个表的数据，体会触发器到底执行了什么业务逻辑，对数据有什么影响。
4为了能更清晰的表述触发器的作用，表结构存在数据冗余，且不符合第三范式，这里特此说明。
5
*/
6
USE test
7GO
8
9IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE XTYPE = 'U' AND NAME = '卷烟库存表')
10DROP TABLE 卷烟库存表
11GO
12IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE XTYPE = 'U' AND NAME = '卷烟销售表')
13DROP TABLE 卷烟销售表
14GO
15
16--业务规则：销售金额 = 销售数量 * 销售单价 业务规则。
17
18CREATE TABLE 卷烟销售表
19(
20卷烟品牌 VARCHAR(40) PRIMARY KEY NOT NULL,
21购货商 VARCHAR(40) NULL,
22销售数量 INT NULL,
23销售单价 MONEY NULL,
24销售金额 MONEY NULL
25)
26GO
27
28--业务规则：库存金额 = 库存数量 * 库存单价 业务规则。
29
30CREATE TABLE 卷烟库存表
31(
32卷烟品牌 VARCHAR(40) PRIMARY KEY NOT NULL,
33库存数量 INT NULL,
34库存单价 MONEY NULL,
35库存金额 MONEY NULL
36)
37GO
38
39--创建触发器，示例1
40
41/*
42创建触发器[T_INSERT_卷烟库存表]，这个触发器较简单。
43说明： 每当[卷烟库存表]发生 INSERT 动作，则引发该触发器。
44触发器功能： 强制执行业务规则，保证插入的数据中，库存金额 = 库存数量 * 库存单价。
45注意： [INSERTED]、[DELETED]为系统表，不可创建、修改、删除，但可以调用。
46重要： 这两个系统表的结构同插入数据的表的结构。
47*/
48IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE XTYPE = 'TR' AND NAME = 'T_INSERT_卷烟库存表')
49DROP TRIGGER T_INSERT_卷烟库存表
50GO
51
52CREATE TRIGGER T_INSERT_卷烟库存表
53ON 卷烟库存表
54FOR INSERT
55AS
56--提交事务处理
57BEGIN TRANSACTION
58--强制执行下列语句，保证业务规则
59UPDATE 卷烟库存表
60SET 库存金额 = 库存数量 * 库存单价
61WHERE 卷烟品牌 IN (SELECT 卷烟品牌 from INSERTED)
62COMMIT TRANSACTION
63GO
64
65/*
66针对[卷烟库存表]，插入测试数据：
67注意，第一条数据（红塔山新势力）中的数据符合业务规则，
68第二条数据（红塔山人为峰）中，[库存金额]空，不符合业务规则，
69第三条数据（云南映像）中，[库存金额]不等于[库存数量]乘以[库存单价]，不符合业务规则。
70第四条数据库存数量为0。
71请注意在插入数据后，检查[卷烟库存表]中的数据是否 库存金额 = 库存数量 * 库存单价。
72*/
73
74INSERT INTO 卷烟库存表(卷烟品牌,库存数量,库存单价,库存金额)
75SELECT '红塔山新势力',100,12,1200 UNION ALL
76SELECT '红塔山人为峰',100,22,NULL UNION ALL
77SELECT '云南映像',100,60,500 UNION ALL
78SELECT '玉溪',0,30,0
79GO
80
81--查询数据
82
83SELECT * FROM 卷烟库存表
84GO
85/*
86
87结果集
88
89RecordId 卷烟品牌 库存数量 库存单价 库存金额
90-------- ------------ -------- ------- ---------
911 红塔山新势力 100 12.0000 1200.0000
922 红塔山人为峰 100 22.0000 2200.0000
933 云南映像 100 60.0000 6000.0000
944 玉溪 0 30.0000 .0000
95
96（所影响的行数为 4 行）
97
98*/
99
100--触发器示例2
101
102/*
103创建触发器[T_INSERT_卷烟销售表]，该触发器较复杂。
104说明: 每当[卷烟库存表]发生 INSERT 动作，则引发该触发器。
105触发器功能： 实现业务规则。
106业务规则: 如果销售的卷烟品牌不存在库存或者库存为零，则返回错误。
107否则则自动减少[卷烟库存表]中对应品牌卷烟的库存数量和库存金额。
108*/
109IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE XTYPE = 'TR' AND NAME = 'T_INSERT_卷烟销售表')
110DROP TRIGGER T_INSERT_卷烟销售表
111GO
112
113CREATE TRIGGER T_INSERT_卷烟销售表
114ON 卷烟销售表
115FOR INSERT
116AS
117BEGIN TRANSACTION
118--检查数据的合法性：销售的卷烟是否有库存，或者库存是否大于零
119IF NOT EXISTS (
120SELECT 库存数量
121FROM 卷烟库存表
122WHERE 卷烟品牌 IN (SELECT 卷烟品牌 FROM INSERTED)
123)
124BEGIN
125--返回错误提示
126RAISERROR('错误！该卷烟不存在库存，不能销售。',16,1)
127--回滚事务
128ROLLBACK
129RETURN
130END
131
132IF EXISTS (
133SELECT 库存数量
134FROM 卷烟库存表
135WHERE 卷烟品牌 IN (SELECT 卷烟品牌 FROM INSERTED) AND
136库存数量 <= 0
137)
138BEGIN
139--返回错误提示
140RAISERROR('错误！该卷烟库存小于等于0，不能销售。',16,1)
141--回滚事务
142ROLLBACK
143RETURN
144END
145
146--对合法的数据进行处理
147
148--强制执行下列语句，保证业务规则
149UPDATE 卷烟销售表
150SET 销售金额 = 销售数量 * 销售单价
151WHERE 卷烟品牌 IN (SELECT 卷烟品牌 FROM INSERTED)
152
153DECLARE @卷烟品牌 VARCHAR(40)
154SET @卷烟品牌 = (SELECT 卷烟品牌 FROM INSERTED)
155
156DECLARE @销售数量 MONEY
157SET @销售数量 = (SELECT 销售数量 FROM INSERTED)
158
159UPDATE 卷烟库存表
160SET 库存数量 = 库存数量 - @销售数量,
161库存金额 = (库存数量 - @销售数量)*库存单价
162WHERE 卷烟品牌 = @卷烟品牌
163COMMIT TRANSACTION
164GO
165
166--请大家自行跟踪[卷烟库存表]和[卷烟销售表]的数据变化。
167--针对[卷烟销售表]，插入第一条测试数据，该数据是正常的。
168
169INSERT INTO 卷烟销售表(卷烟品牌,购货商,销售数量,销售单价,销售金额)
170SELECT '红塔山新势力','某购货商',10,12,1200
171GO
172
173--针对[卷烟销售表]，插入第二条测试数据，该数据 销售金额 不等于 销售单价 * 销售数量。
174--触发器将自动更正数据，使 销售金额 等于 销售单价 * 销售数量。
175
176INSERT INTO 卷烟销售表(卷烟品牌,购货商,销售数量,销售单价,销售金额)
177SELECT '红塔山人为峰','某购货商',10,22,2000
178GO
179
180--针对[卷烟销售表]，插入第三条测试数据，该数据中的卷烟品牌在 卷烟库存表中找不到对应。
181--触发器将报错。
182
183INSERT INTO 卷烟销售表(卷烟品牌,购货商,销售数量,销售单价,销售金额)
184SELECT '红河V8','某购货商',10,60,600
185GO
186
187/*
188结果集
189服务器: 消息 50000，级别 16，状态 1，过程 T_INSERT_卷烟销售表，行 15
190错误！该卷烟不存在库存，不能销售。
191*/
192
193--针对[卷烟销售表]，插入第三条测试数据，该数据中的卷烟品牌在 卷烟库存表中库存为0。
194--触发器将报错。
195
196INSERT INTO 卷烟销售表(卷烟品牌,购货商,销售数量,销售单价,销售金额)
197SELECT '玉溪','某购货商',10,30,300
198GO
199
200/*
201结果集
202服务器: 消息 50000，级别 16，状态 1，过程 T_INSERT_卷烟销售表，行 29
203错误！该卷烟库存小于等于0，不能销售。
204*/
205--查询数据
206SELECT * FROM 卷烟库存表
207
208SELECT * FROM 卷烟销售表
209GO
210
211/*
212补充：
2131、本示例主要通过一个简单的业务规则实现来进行触发器使用的说明，具体的要根据需要灵活处理；
2142、关于触发器要理解并运用好 INSERTED ，DELETED 两个系统表；
2153、本示例创建的触发器都是 FOR INSERT ,具体的语法可参考：
216
217Trigger语法
218
219CREATE TRIGGER trigger_name
220ON { table | view }
221[ WITH ENCRYPTION ] --用于加密触发器
222{
223{ { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] }
224[ WITH APPEND ]
225[ NOT FOR REPLICATION ]
226AS
227[ { IF UPDATE ( column )
228[ { AND | OR } UPDATE ( column ) ]
229[ 
n ]
230| IF ( COLUMNS_UPDATED ( ) { bitwise_operator } updated_bitmask )
231{ comparison_operator } column_bitmask [ n ]
232} ]
233sql_statement [ n ]
234}
235}
236
2374、关于触发器，还应该注意
238(1)、DELETE 触发器不能捕获 TRUNCATE TABLE 语句。
239(2)、触发器中不允许以下 Transact-SQL 语句：
240ALTER DATABASE CREATE DATABASE DISK INIT
241DISK RESIZE DROP DATABASE LOAD DATABASE
242LOAD LOG RECONFIGURE RESTORE DATABASE
243RESTORE LOG
244(3)、触发器最多可以嵌套 32 层。
245
246*/
247
248--修改触发器
249--实质上，是将 CREATE TRIGGER 修改为 ALTER TRIGGER 即可。
250
251--删除触发器
252DROP TRIGGER xxx
253GO
254
255--删除测试环境
256DROP TABLE 卷烟库存表
257GO
258DROP TABLE 卷烟销售表
259GO
260DROP TRIGGER T_INSERT_卷烟库存表
261GO
262DROP TRIGGER T_INSERT_卷烟销售表
263GO
264/**----------------------------------------------------------------------
265##################################################################
266触发器的基础知识和例子
267：create trigger tr_name
268on table/view
269{for | after | instead of } [update][,][insert][,][delete]
270[with encryption]
271as {batch | if update (col_name) [{and|or} update (col_name)] }
272
273说明：
2741 tr_name ：触发器名称
2752 on table/view ：触发器所作用的表。一个触发器只能作用于一个表
2763 for 和after ：同义
2774 after 与instead of :sql 2000新增项目afrer 与 instead of 的区别
278After
279在触发事件发生以后才被激活,只可以建立在表上
280Instead of
281代替了相应的触发事件而被执行,既可以建立在表上也可以建立在视图上
2825 insert、update、delete：激活触发器的三种操作，可以同时执行，也可选其一
2836 if update (col_name)：表明所作的操作对指定列是否有影响，有影响，则激活触发器。此外，因为delete 操作只对行有影响，
284所以如果使用delete操作就不能用这条语句了(虽然使用也不出错，但是不能激活触发器，没意义)。
2857 触发器执行时用到的两个特殊表：deleted ,inserted
286deleted 和inserted 可以说是一种特殊的临时表，是在进行激活触发器时由系统自动生成的，其结构与触发器作用的表结构是一
287样的，只是存放 的数据有差异。
288
289续
290下面表格说明deleted 与inserted 数据的差异
291deleted 与inserted 数据的差异
292Inserted
293存放进行insert和update 操作后的数据
294Deleted
295存放进行delete 和update操作前的数据
296注意：update 操作相当于先进行delete 再进行insert ,所以在进行update操作时，修改前的数据拷贝一条到deleted 表中，修改后
297的数据在存到触发器作用的表的同时，也同时生成一条拷贝到insered表中
298---------------------------------------------------------------------------*/
/*
2
建立虚拟测试环境，包含：表[卷烟库存表]，表[卷烟销售表]。3
请大家注意跟踪这两个表的数据，体会触发器到底执行了什么业务逻辑，对数据有什么影响。4
为了能更清晰的表述触发器的作用，表结构存在数据冗余，且不符合第三范式，这里特此说明。5
*/ 6
USE test7
GO8
9
IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE XTYPE = 'U' AND NAME = '卷烟库存表')10
DROP TABLE 卷烟库存表11
GO12
IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE XTYPE = 'U' AND NAME = '卷烟销售表')13
DROP TABLE 卷烟销售表14
GO15
16
--业务规则：销售金额 = 销售数量 * 销售单价 业务规则。17
18
CREATE TABLE 卷烟销售表19
(20
卷烟品牌 VARCHAR(40) PRIMARY KEY NOT NULL,21
购货商 VARCHAR(40) NULL,22
销售数量 INT NULL,23
销售单价 MONEY NULL,24
销售金额 MONEY NULL25
)26
GO27
28
--业务规则：库存金额 = 库存数量 * 库存单价 业务规则。29
30
CREATE TABLE 卷烟库存表31
(32
卷烟品牌 VARCHAR(40) PRIMARY KEY NOT NULL,33
库存数量 INT NULL,34
库存单价 MONEY NULL,35
库存金额 MONEY NULL36
)37
GO38
39
--创建触发器，示例140
41
/*42
创建触发器[T_INSERT_卷烟库存表]，这个触发器较简单。43
说明： 每当[卷烟库存表]发生 INSERT 动作，则引发该触发器。44
触发器功能： 强制执行业务规则，保证插入的数据中，库存金额 = 库存数量 * 库存单价。45
注意： [INSERTED]、[DELETED]为系统表，不可创建、修改、删除，但可以调用。46
重要： 这两个系统表的结构同插入数据的表的结构。47
*/48
IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE XTYPE = 'TR' AND NAME = 'T_INSERT_卷烟库存表')49
DROP TRIGGER T_INSERT_卷烟库存表50
GO51
52
CREATE TRIGGER T_INSERT_卷烟库存表53
ON 卷烟库存表54
FOR INSERT55
AS56
--提交事务处理57
BEGIN TRANSACTION58
--强制执行下列语句，保证业务规则59
UPDATE 卷烟库存表60
SET 库存金额 = 库存数量 * 库存单价61
WHERE 卷烟品牌 IN (SELECT 卷烟品牌 from INSERTED)62
COMMIT TRANSACTION63
GO64
65
/* 66
针对[卷烟库存表]，插入测试数据：67
注意，第一条数据（红塔山新势力）中的数据符合业务规则，68
第二条数据（红塔山人为峰）中，[库存金额]空，不符合业务规则，69
第三条数据（云南映像）中，[库存金额]不等于[库存数量]乘以[库存单价]，不符合业务规则。70
第四条数据库存数量为0。71
请注意在插入数据后，检查[卷烟库存表]中的数据是否 库存金额 = 库存数量 * 库存单价。72
*/73
74
INSERT INTO 卷烟库存表(卷烟品牌,库存数量,库存单价,库存金额)75
SELECT '红塔山新势力',100,12,1200 UNION ALL76
SELECT '红塔山人为峰',100,22,NULL UNION ALL77
SELECT '云南映像',100,60,500 UNION ALL78
SELECT '玉溪',0,30,079
GO80
81
--查询数据82
83
SELECT * FROM 卷烟库存表84
GO85
/*86
87
结果集88
89
RecordId 卷烟品牌 库存数量 库存单价 库存金额90
-------- ------------ -------- ------- ---------91
1 红塔山新势力 100 12.0000 1200.000092
2 红塔山人为峰 100 22.0000 2200.000093
3 云南映像 100 60.0000 6000.000094
4 玉溪 0 30.0000 .000095
96
（所影响的行数为 4 行）97
98
*/99
100
--触发器示例2101
102
/*103
创建触发器[T_INSERT_卷烟销售表]，该触发器较复杂。104
说明: 每当[卷烟库存表]发生 INSERT 动作，则引发该触发器。105
触发器功能： 实现业务规则。106
业务规则: 如果销售的卷烟品牌不存在库存或者库存为零，则返回错误。107
否则则自动减少[卷烟库存表]中对应品牌卷烟的库存数量和库存金额。108
*/109
IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE XTYPE = 'TR' AND NAME = 'T_INSERT_卷烟销售表')110
DROP TRIGGER T_INSERT_卷烟销售表111
GO112
113
CREATE TRIGGER T_INSERT_卷烟销售表114
ON 卷烟销售表115
FOR INSERT116
AS117
BEGIN TRANSACTION118
--检查数据的合法性：销售的卷烟是否有库存，或者库存是否大于零119
IF NOT EXISTS (120
SELECT 库存数量121
FROM 卷烟库存表122
WHERE 卷烟品牌 IN (SELECT 卷烟品牌 FROM INSERTED)123
)124
BEGIN125
--返回错误提示126
RAISERROR('错误！该卷烟不存在库存，不能销售。',16,1)127
--回滚事务128
ROLLBACK129
RETURN130
END131
132
IF EXISTS (133
SELECT 库存数量134
FROM 卷烟库存表135
WHERE 卷烟品牌 IN (SELECT 卷烟品牌 FROM INSERTED) AND136
库存数量 <= 0 137
)138
BEGIN139
--返回错误提示140
RAISERROR('错误！该卷烟库存小于等于0，不能销售。',16,1)141
--回滚事务142
ROLLBACK143
RETURN144
END145
146
--对合法的数据进行处理147
148
--强制执行下列语句，保证业务规则149
UPDATE 卷烟销售表150
SET 销售金额 = 销售数量 * 销售单价151
WHERE 卷烟品牌 IN (SELECT 卷烟品牌 FROM INSERTED)152
153
DECLARE @卷烟品牌 VARCHAR(40)154
SET @卷烟品牌 = (SELECT 卷烟品牌 FROM INSERTED)155
156
DECLARE @销售数量 MONEY157
SET @销售数量 = (SELECT 销售数量 FROM INSERTED)158
159
UPDATE 卷烟库存表160
SET 库存数量 = 库存数量 - @销售数量,161
库存金额 = (库存数量 - @销售数量)*库存单价162
WHERE 卷烟品牌 = @卷烟品牌163
COMMIT TRANSACTION164
GO165
166
--请大家自行跟踪[卷烟库存表]和[卷烟销售表]的数据变化。167
--针对[卷烟销售表]，插入第一条测试数据，该数据是正常的。168
169
INSERT INTO 卷烟销售表(卷烟品牌,购货商,销售数量,销售单价,销售金额)170
SELECT '红塔山新势力','某购货商',10,12,1200171
GO172
173
--针对[卷烟销售表]，插入第二条测试数据，该数据 销售金额 不等于 销售单价 * 销售数量。174
--触发器将自动更正数据，使 销售金额 等于 销售单价 * 销售数量。175
176
INSERT INTO 卷烟销售表(卷烟品牌,购货商,销售数量,销售单价,销售金额)177
SELECT '红塔山人为峰','某购货商',10,22,2000178
GO179
180
--针对[卷烟销售表]，插入第三条测试数据，该数据中的卷烟品牌在 卷烟库存表中找不到对应。181
--触发器将报错。182
183
INSERT INTO 卷烟销售表(卷烟品牌,购货商,销售数量,销售单价,销售金额)184
SELECT '红河V8','某购货商',10,60,600185
GO186
187
/*188
结果集189
服务器: 消息 50000，级别 16，状态 1，过程 T_INSERT_卷烟销售表，行 15190
错误！该卷烟不存在库存，不能销售。191
*/192
193
--针对[卷烟销售表]，插入第三条测试数据，该数据中的卷烟品牌在 卷烟库存表中库存为0。194
--触发器将报错。195
196
INSERT INTO 卷烟销售表(卷烟品牌,购货商,销售数量,销售单价,销售金额)197
SELECT '玉溪','某购货商',10,30,300198
GO199
200
/*201
结果集202
服务器: 消息 50000，级别 16，状态 1，过程 T_INSERT_卷烟销售表，行 29203
错误！该卷烟库存小于等于0，不能销售。204
*/205
--查询数据206
SELECT * FROM 卷烟库存表207
208
SELECT * FROM 卷烟销售表209
GO210
211
/*212
补充：213
1、本示例主要通过一个简单的业务规则实现来进行触发器使用的说明，具体的要根据需要灵活处理；214
2、关于触发器要理解并运用好 INSERTED ，DELETED 两个系统表；215
3、本示例创建的触发器都是 FOR INSERT ,具体的语法可参考：216
217
Trigger语法218
219
CREATE TRIGGER trigger_name 220
ON { table | view } 221
[ WITH ENCRYPTION ] --用于加密触发器222
{223
{ { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] }224
[ WITH APPEND ]225
[ NOT FOR REPLICATION ]226
AS227
[ { IF UPDATE ( column )228
[ { AND | OR } UPDATE ( column ) ]229
[ n ]230
| IF ( COLUMNS_UPDATED ( ) { bitwise_operator } updated_bitmask )231
{ comparison_operator } column_bitmask [ n ]232
} ] 233
sql_statement [ n ] 234
} 235
} 236
237
4、关于触发器，还应该注意238
(1)、DELETE 触发器不能捕获 TRUNCATE TABLE 语句。239
(2)、触发器中不允许以下 Transact-SQL 语句：240
ALTER DATABASE CREATE DATABASE DISK INIT 241
DISK RESIZE DROP DATABASE LOAD DATABASE 242
LOAD LOG RECONFIGURE RESTORE DATABASE 243
RESTORE LOG 244
(3)、触发器最多可以嵌套 32 层。245
246
*/247
248
--修改触发器249
--实质上，是将 CREATE TRIGGER 修改为 ALTER TRIGGER 即可。250
251
--删除触发器252
DROP TRIGGER xxx253
GO254
255
--删除测试环境256
DROP TABLE 卷烟库存表257
GO258
DROP TABLE 卷烟销售表259
GO260
DROP TRIGGER T_INSERT_卷烟库存表261
GO262
DROP TRIGGER T_INSERT_卷烟销售表263
GO264
/**----------------------------------------------------------------------265
##################################################################266
触发器的基础知识和例子267
：create trigger tr_name268
on table/view269
{for | after | instead of } [update][,][insert][,][delete]270
[with encryption]271
as {batch | if update (col_name) [{and|or} update (col_name)] }272
273
说明：274
1 tr_name ：触发器名称275
2 on table/view ：触发器所作用的表。一个触发器只能作用于一个表276
3 for 和after ：同义277
4 after 与instead of :sql 2000新增项目afrer 与 instead of 的区别278
After 279
在触发事件发生以后才被激活,只可以建立在表上 280
Instead of281
代替了相应的触发事件而被执行,既可以建立在表上也可以建立在视图上282
5 insert、update、delete：激活触发器的三种操作，可以同时执行，也可选其一283
6 if update (col_name)：表明所作的操作对指定列是否有影响，有影响，则激活触发器。此外，因为delete 操作只对行有影响，284
所以如果使用delete操作就不能用这条语句了(虽然使用也不出错，但是不能激活触发器，没意义)。285
7 触发器执行时用到的两个特殊表：deleted ,inserted286
deleted 和inserted 可以说是一种特殊的临时表，是在进行激活触发器时由系统自动生成的，其结构与触发器作用的表结构是一287
样的，只是存放 的数据有差异。288
289
续290
下面表格说明deleted 与inserted 数据的差异291
deleted 与inserted 数据的差异292
Inserted 293
存放进行insert和update 操作后的数据 294
Deleted295
存放进行delete 和update操作前的数据296
注意：update 操作相当于先进行delete 再进行insert ,所以在进行update操作时，修改前的数据拷贝一条到deleted 表中，修改后297
的数据在存到触发器作用的表的同时，也同时生成一条拷贝到insered表中 298
---------------------------------------------------------------------------*/