2013-01-31
今天學習了點oracle，觸發器的簡單使用，需要解決的問題是如下，還是英文描述吧。
the triggers are perfect is change auditing.The example is of Paranoid Pam(who runs a bowling alley and have been receiving complaints about people cheating on their scores).she recently complete an oracle application to catch the cheaters.
simply say, she just want to find who and when and how change the data.
next, you will say my PL/SQL code.in the write i have use some may useful method to make it.
souce code:
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 VARCHAR2(1),
57
new_score VARCHAR2(1),
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 VARCHAR2(1),
76
new_score VARCHAR2(1),
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
132
SELECT
*
FROM
frame;
133
134
SELECT
*
FROM
frame_audit;
代碼可以放在pl/sql的sql腳本中運行，可以看到相應的結果。
更多文章、技術交流、商務合作、聯系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
