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元等您想捐的金額吧,狠狠點擊下面給點支持吧,站長非常感激您!手機微信長按不能支付解決辦法:請將微信支付二維碼保存到相冊,切換到微信,然后點擊微信右上角掃一掃功能,選擇支付二維碼完成支付。
【本文對您有幫助就好】元
