临回家了，把这两天搞的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