临回家了，把这两天搞的oracle的点东西，放到博客上，主要是触发器的使用，没有涉及到具体复杂的业务，但是都是比较实际的业务需求。实际中能够用的上，主要是用于审计，用户实际业务需求（友好性的考虑），与view等配合使用等。直接上code吧，在pl/sql中执行相应的模块就能得到相应的结果，注释和清晰，主要是看书一步步搞的。有的自己备用方便。如果有用，大家也可以看看。
1
--
好的创建表的方法
2
DECLARE
3
TB
NUMBER
;
4
BEGIN
5
SELECT
COUNT
(
*
)
INTO
TB
FROM
TABS
WHERE
TABLE_NAME
=
'
FRAME
'
;
6
IF
TB
<>
0
THEN
7
EXECUTE
IMMEDIATE
'
DROP TABLE frame
'
;
8
EXECUTE
IMMEDIATE
'
9
CREATE TABLE frame
10
(
11
bowler_id number,
12
game_id number,
13
frame_id number,
14
strike varchar2(1) default
''
N
''
,
15
spare varchar2(1) default
''
N
''
,
16
score number,
17
constraint frame_pk
18
primary key (bowler_id,game_id,frame_id)
19
)
'
;
20
DBMS_OUTPUT.PUT_LINE(
'
存在该表已删除，并创建新的表
'
);
21
ELSE
22
EXECUTE
IMMEDIATE
'
CREATE TABLE frame
23
(
24
bowler_id number,
25
game_id number,
26
frame_id number,
27
strike varchar2(1) default
''
N
''
,
28
spare varchar2(1) default
''
N
''
,
29
score number,
30
constraint frame_pk
31
primary key (bowler_id,game_id,frame_id)
32
)
'
;
33
DBMS_OUTPUT.PUT_LINE(
'
不存在该表，已创建新表
'
);
34
END
IF
;
35
END
;
36
37
--
创建审计表
38
DECLARE
39
TB
NUMBER
;
40
BEGIN
41
SELECT
COUNT
(
*
)
INTO
TB
FROM
TABS
WHERE
TABLE_NAME
=
'
FRAME_AUDIT
'
;
42
IF
TB
<>
0
THEN
43
EXECUTE
IMMEDIATE
'
DROP TABLE frame_audit
'
;
44
EXECUTE
IMMEDIATE
'
45
CREATE TABLE frame_audit
46
(
47
bowler_id NUMBER,
48
game_id NUMBER,
49
frame_id NUMBER,
50
old_strike VARCHAR2(1),
51
new_strike VARCHAR2(1),
52
53
old_spare VARCHAR2(1),
54
new_spare VARCHAR2(1),
55
56
old_score number,
57
new_score number,
58
59
change_date DATE,
60
operation VARCHAR2(6)
61
)
'
;
62
DBMS_OUTPUT.PUT_LINE(
'
存在该表已删除，并创建新的表
'
);
63
ELSE
64
EXECUTE
IMMEDIATE
'
CREATE TABLE frame_audit
65
(
66
bowler_id NUMBER,
67
game_id NUMBER,
68
frame_id NUMBER,
69
old_strike VARCHAR2(1),
70
new_strike VARCHAR2(1),
71
72
old_spare VARCHAR2(1),
73
new_spare VARCHAR2(1),
74
75
old_score number,
76
new_score number,
77
78
change_date DATE,
79
operation VARCHAR2(6)
80
)
'
;
81
DBMS_OUTPUT.PUT_LINE(
'
不存在该表，已创建新表
'
);
82
END
IF
;
83
END
;
84
85
86
--
创建触发器
87
CREATE
OR
REPLACE
TRIGGER
audit_frames
88
AFTER
INSERT
OR
UPDATE
OR
DELETE
ON
frame
89
FOR
EACH ROW
90
BEGIN
91
IF
inserting
THEN
92
INSERT
INTO
frame_audit(bowler_id,game_id,frame_id,
93
new_strike,new_spare,
94
new_score,change_date,operation)
95
VALUES
(:new.bowler_id,:new.game_id,:new.frame_id,:new.strike,:new.spare,:new.score,SYSDATE,
'
insert
'
);
96
97
ELSIF updating
THEN
98
INSERT
INTO
frame_audit(bowler_id,game_id,frame_id,
99
old_strike,new_strike,old_spare,new_spare,
100
old_score,new_score,change_date,operation)
101
VALUES
(:new.bowler_id,:new.game_id,:new.frame_id,:old.strike,:new.strike,:old.spare,:new.spare,:old.score,:new.score,SYSDATE,
'
update
'
);
102
103
ELSIF deleting
THEN
104
INSERT
INTO
frame_audit(bowler_id,game_id,frame_id,
105
old_strike,old_spare,
106
old_score,change_date,operation)
107
VALUES
(:old.bowler_id,:old.game_id,:old.frame_id,:old.strike,:old.spare,:old.score,SYSDATE,
'
insert
'
);
108
109
END
IF
;
110
END
audit_frames;
111
112
--
在创建之后，后来使用的时候报了一个触发器无效且未通过认证的错误
113
--
解决办法是找到触发器-》右键查看-》找到相应的错误（insert->inserting）.问题解决。
114
115
116
INSERT
INTO
frame (bowler_id,game_id,frame_id,strike)
117
VALUES
(
1
,
2
,
3
,
'
y
'
);
118
119
120
SELECT
*
FROM
frame;
121
122
UPDATE
frame
SET
strike
=
'
N
'
,
123
spare
=
'
y
'
124
WHERE
bowler_id
=
1
125
AND
game_id
=
2
126
AND
frame_id
=
3
;
127
128
DELETE
frame
WHERE
bowler_id
=
1
;
129
130
COMMIT
;
131
SELECT
*
FROM
frame;
132
SELECT
*
FROM
frame_audit;
133
134
/*
创建更加有针对性的触发器：只是在修改记录的时候触发
*/
135
/*
************Begin****************
*/
136
CREATE
OR
REPLACE
TRIGGER
audit_update
137
AFTER
UPDATE
OF
strike,spare ,score
ON
frame
138
REFERENCING OLD
AS
prior_to_cheat NEW
AS
after_cheat
139
FOR
EACH ROW
140
WHEN
(
141
prior_to_cheat.strike
!=
after_cheat.strike
142
OR
prior_to_cheat.spare
!=
after_cheat.spare
143
OR
prior_to_cheat.score
!=
after_cheat.score)
144
BEGIN
145
INSERT
INTO
frame_audit(bowler_id,game_id,frame_id,
146
old_strike,new_strike,old_spare,new_spare,
147
old_score,new_score,change_date,operation)
148
VALUES
(:prior_to_cheat.bowler_id,:prior_to_cheat.game_id,:prior_to_cheat.frame_id,:after_cheat.strike,:prior_to_cheat.strike,:after_cheat.spare,:prior_to_cheat.spare,:after_cheat.score,:prior_to_cheat.score,SYSDATE,
'
update
'
);
149
END
;
150
--
测试语句
151
INSERT
INTO
frame (bowler_id,game_id,frame_id,strike)
152
VALUES
(
1
,
1
,
1
,
'
y
'
);
153
UPDATE
frame
SET
strike
=
strike;
154
SELECT
*
FROM
frame;
155
SELECT
*
FROM
frame_audit;
156
/*
*******************END*************************
*/
157
158
/*
*********************************
*/
159
--
增加有好性，在击球全中和2次全中的选手加10分
160
/*
**************BEGIN****************
*/
161
162
CREATE
OR
REPLACE
TRIGGER
set_score
163
BEFORE
INSERT
ON
frame
164
FOR
EACH ROW
165
WHEN
(NEW.score
IS
NOT
NULL
)
166
BEGIN
167
IF
:New.strike
=
'
y
'
OR
:new.spare
=
'
y
'
168
THEN
169
:new.score :
=
:new.score
+
10
;
170
END
IF
;
171
END
;
172
173
--
测试语句
174
INSERT
INTO
frame (bowler_id,game_id,frame_id,strike,score)
175
VALUES
(
2
,
2
,
1
,
'
y
'
,
10
);
176
177
INSERT
INTO
frame (bowler_id,game_id,frame_id,strike,score)
178
VALUES
(
2
,
2
,
2
,
'
y
'
,
9
);
179
180
INSERT
INTO
frame (bowler_id,game_id,frame_id,strike,score)
181
VALUES
(
3
,
1
,
1
,
'
n
'
,
9
);
182
SELECT
*
FROM
frame;
183
SELECT
*
FROM
frame_audit;
184
/*
****************分数校验**********
*/
185
CREATE
OR
REPLACE
TRIGGER
validate_score
186
AFTER
INSERT
OR
UPDATE
ON
frame
187
FOR
EACH ROW
188
BEGIN
189
IF
:New.strike
=
'
y
'
AND
:new.score
<
10
190
THEN
191
raise_application_error(
-
20001
,
'
ERROR:score for strike must be >=10
'
);
192
ELSIF :new.spare
=
'
y
'
AND
:new.score
<
10
193
THEN
194
raise_application_error(
-
20001
,
'
ERROR:Score for spare must be >=10
'
);
195
ELSIF :new.strike
=
'
y
'
AND
:new.spare
=
'
y
'
196
THEN
197
raise_application_error(
-
20001
,
'
ERROR: Cannot Enter Spare and strike
'
);
198
199
END
IF
;
200
END
;
201
--
测试语句
202
INSERT
INTO
frame (bowler_id,game_id,frame_id,strike,score)
203
VALUES
(
5
,
1
,
1
,
'
y
'
,
6
);
204
205
SELECT
*
FROM
frame;
206
SELECT
*
FROM
frame_audit;
207
/*
*************END*******************
*/
208
209
/*
*********************************
*/
210
/*
********************************
*/
211
/*
*********一个简单的快递系统*****************
*/
212
/*
********************************
*/
213
/*
******************************
*/
214
--
如果表存在删除表让后再创建，不存在直接创建表
215
DECLARE
216
I
INTEGER
;
217
BEGIN
218
SELECT
COUNT
(
*
)
INTO
I
FROM
USER_TABLES
WHERE
TABLE_NAME
=
'
DELIVERY
'
;
--
注意表名大写
219
IF
I
>
0
THEN
220
DBMS_OUTPUT.PUT_LINE(
'
该表已存在!删除后，创建表
'
);
221
EXECUTE
IMMEDIATE
'
DROP TABLE DELIVERY
'
;
222
ELSE
223
DBMS_OUTPUT.PUT_LINE(
'
该表不存在，创建表
'
);
224
END
IF
;
225
EXECUTE
IMMEDIATE
'
CREATE TABLE delivery
226
(
227
delivery_id NUMBER,
228
delivery_start DATE,
229
delivery_end DATE,
230
area_id NUMBER,
231
driver_id NUMBER
232
)
'
;
233
END
;
234
--
测试语句
235
SELECT
*
FROM
delivery;
236
--
创建area表
237
DECLARE
238
I
INTEGER
;
239
BEGIN
240
SELECT
COUNT
(
*
)
INTO
I
FROM
USER_TABLES
WHERE
TABLE_NAME
=
'
AREA
'
;
--
注意表名大写
241
IF
I
>
0
THEN
242
DBMS_OUTPUT.PUT_LINE(
'
该表已存在!删除后，创建表
'
);
243
EXECUTE
IMMEDIATE
'
DROP TABLE AREA
'
;
244
ELSE
245
DBMS_OUTPUT.PUT_LINE(
'
该表不存在，创建表
'
);
246
END
IF
;
247
EXECUTE
IMMEDIATE
'
CREATE TABLE area
248
(
249
area_id NUMBER,
250
area_desc varchar(30)
251
)
'
;
252
END
;
253
--
测试语句
254
SELECT
*
FROM
area;
255
256
--
创建driver表
257
DECLARE
258
I
INTEGER
;
259
BEGIN
260
SELECT
COUNT
(
*
)
INTO
I
FROM
USER_TABLES
WHERE
TABLE_NAME
=
'
DRIVER
'
;
--
注意表名大写
261
IF
I
>
0
THEN
262
DBMS_OUTPUT.PUT_LINE(
'
该表已存在!删除后，创建表
'
);
263
EXECUTE
IMMEDIATE
'
DROP TABLE DRIVER
'
;
264
ELSE
265
DBMS_OUTPUT.PUT_LINE(
'
该表不存在，创建表
'
);
266
END
IF
;
267
EXECUTE
IMMEDIATE
'
CREATE TABLE driver
268
(
269
driver_id NUMBER,
270
driver_name varchar2(30)
271
)
'
;
272
END
;
273
--
测试语句
274
SELECT
*
FROM
driver;
275
276
--
创建3个序列为表提供唯一标识符
277
CREATE
SEQUENCE delivery_id_seq;
278
CREATE
SEQUENCE area_id_seq;
279
CREATE
SEQUENCE driver_id_seq;
280
281
--
用一个视图同时显示信息
282
CREATE
OR
REPLACE
VIEW
delivery_info
AS
283
SELECT
d.delivery_id,
284
d.delivery_start,
285
d.delivery_end,
286
a.area_desc,
287
dr.driver_name
288
FROM
delivery d ,area a,driver dr
289
WHERE
a.area_id
=
d.area_id
290
AND
dr.driver_id
=
d.driver_id;
291
292
--
创建instead of insert 触发器
293
CREATE
OR
REPLACE
TRIGGER
delivery_info_insert
294
INSTEAD
OF
INSERT
295
ON
delivery_info
296
DECLARE
297
--
cursour to get the driver by name
298
CURSOR
curs_get_driver_id (cp_driver_name
VARCHAR2
)
299
IS
300
SELECT
driver_id
301
FROM
302
driver
303
WHERE
driver_name
=
cp_driver_name;
304
v_driver_id
NUMBER
;
305
--
cursor to get area by name
306
CURSOR
curs_get_area_id (cp_area_desc
VARCHAR2
)
307
IS
308
SELECT
area_id
309
FROM
area
310
WHERE
area_desc
=
cp_area_desc;
311
312
v_area_id
NUMBER
;
313
BEGIN
314
/*
make sure the delivery_end value is null
*/
315
316
IF
:new.delivery_end
IS
NOT
NULL
317
THEN
318
raise_application_error(
-
20000
,
'
Delivery end date value must be null when delivery created
'
);
319
END
IF
;
320
321
/*
try to get the driver id by the name if not found
322
*then create a new brand driver id from the sequence
323
*
*/
324
OPEN
curs_get_driver_id(
UPPER
(:new.driver_name));
325
326
FETCH
curs_get_driver_id
INTO
v_driver_id;
327
328
IF
curs_get_driver_id
%
NOTFOUND
329
THEN
330
SELECT
driver_id_seq.nextval
331
INTO
v_driver_id
332
FROM
dual;
333
334
INSERT
INTO
driver
335
(
336
driver_id,driver_name
337
)
338
VALUES
(v_driver_id,
UPPER
(:new.driver_name));
339
END
IF
;
340
341
CLOSE
curs_get_driver_id;
342
/*
try to get the area id by the name if not found
343
*then create a new brand area id from the sequence
344
*
*/
345
346
OPEN
curs_get_area_id(
UPPER
(:new.area_desc));
347
348
FETCH
curs_get_area_id
349
INTO
v_area_id;
350
IF
curs_get_area_id
%
NOTFOUND
351
THEN
352
SELECT
area_id_seq.nextval
353
INTO
v_area_id
354
FROM
dual;
355
356
INSERT
INTO
area
357
(area_id ,area_desc)
358
VALUES
(v_area_id,
UPPER
(:new.area_desc));
359
END
IF
;
360
361
CLOSE
curs_get_area_id;
362
/*
create the delivery entry
363
*/
364
INSERT
INTO
delivery
365
(delivery_id,delivery_start,delivery_end,area_id,driver_id)
366
VALUES
(delivery_id_seq.nextval,NVL(:new.delivery_start,SYSDATE),
NULL
,v_area_id,v_driver_id);
367
368
END
;
369
370
/*
************create instead of insert trigger end******************************
*/
371
372
--
测试语句
373
INSERT
INTO
delivery_info(delivery_id,
374
delivery_start,delivery_end,area_desc,driver_name)
375
VALUES
(
NULL
,
NULL
,
NULL
,
'
local college
'
,
'
big ted
'
);
376
377
SELECT
*
FROM
delivery;
378
SELECT
*
FROM
area;
379
SELECT
*
FROM
driver;
380
