Oracle创建触发器详解及例子

触发器详解 1。触发器与触发该触发器的SQL语句同属于一个事务，触发器不允许发出任何事务控制语句，如 commit、rollback、savepoint或者set transaction，它只能随着外部事务的提交、回滚而提交、回滚。（但在oracle8i 以及更高的版本中，你可以创建作为自治事务而执行的触发器，在这种情况下，触发器可以做提交或回滚操作，而与触发该触发器的外部SQL所在的事务无关） 2。与上面相同，在触发器内调用的所有过程、函数也都不能有事务控制语句，除非它们被声明为自治事务（ declare pragma autonomous_transaction;）。 1.创建表结构 CREATE TABLE TLK_P_NEWS_MDS( ID VARCHAR2(1000 BYTE) NOT NULL, NEWSID VARCHAR2(1000 BYTE), MANDATESCOPE VARCHAR2(1000 BYTE), MANDATETYPE VARCHAR2(2 BYTE) ); Create unique Index NEWS_MDS_Index1 On TLK_P_NEWS_MDS(ID) ; Create Index NEWS_MDS_Index2 On TLK_P_NEWS_MDS(NEWSID); Create Index NEWS_MDS_Index3 On TLK_P_NEWS_MDS(MANDATESCOPE);

2.创建触发器 CREATE OR REPLACE TRIGGER tr_p_news AFTER DELETE OR INSERT OR UPDATE ON tlk_p_news FOR EACH row DECLARE integrity_error EXCEPTION; errno INTEGER; errmsg CHAR(200); BEGIN IF inserting THEN proc_mandatescope(:new.id,’News’,:new.item_mandatescope); elsif updating THEN proc_mandatescope(:old.id,’News’,:new.item_mandatescope); elsif deleting THEN proc_mandatescope(:old.id,’News’,NULL); END IF; EXCEPTION WHEN integrity_error THEN raise_application_error(errno, errmsg); END; 3.创建存储过程 create or replace PROCEDURE PROC_MANDATESCOPE( DATAID IN VARCHAR2, TABLENAME IN VARCHAR2, MANDATESCOPE IN CLOB ) AS AV_SPLIT VARCHAR2(2) := ‘;’; V_MANDATESCOPE CLOB; V_LENGTH NUMBER; V_START NUMBER; V_INDEX NUMBER; V_STR VARCHAR2(1000); V_MANDATETYPE VARCHAR2(2); V_SQL VARCHAR2(1000);–动态sql BEGIN V_SQL:=’DELETE FROM TLK_P_’||TABLENAME||’_mds WHERE ‘||TABLENAME||’ID =”’||DATAID||””; EXECUTE IMMEDIATE(V_SQL); IF MANDATESCOPE IS NOT NULL THEN V_MANDATESCOPE := REPLACE(MANDATESCOPE,’,’,’;’); V_LENGTH := LENGTH(V_MANDATESCOPE); V_START :=1; V_INDEX :=0; V_STR :=”; IF INSTR(V_MANDATESCOPE ,AV_SPLIT) >0 THEN WHILE(V_START <= V_LENGTH) LOOP V_INDEX := INSTR(V_MANDATESCOPE, AV_SPLIT, V_START); IF V_INDEX = 0 THEN V_MANDATETYPE := SUBSTR(V_MANDATESCOPE, V_START,1); V_STR :=SUBSTR(V_MANDATESCOPE, V_START); V_START := V_LENGTH + 1; ELSE V_MANDATETYPE := SUBSTR(V_MANDATESCOPE, V_START, 1); V_STR :=SUBSTR(V_MANDATESCOPE, V_START, V_INDEX - V_START); V_START := V_INDEX + 1; END IF; V_SQL:=’INSERT INTO TLK_P_’||TABLENAME||’_mds (ID,’||TABLENAME||’ID,MANDATESCOPE,MANDATETYPE) VALUES(”’||SYS_GUID()||”’,”’||DATAID||”’,”’||V_STR||”’,”’||V_MANDATETYPE||”’)’; EXECUTE IMMEDIATE(V_SQL); END LOOP; ELSE V_MANDATETYPE := SUBSTR(V_MANDATESCOPE, V_START,1); V_SQL :=’INSERT INTO TLK_P_’||TABLENAME||’_mds (ID,’||TABLENAME||’ID,MANDATESCOPE,MANDATETYPE) VALUES(”’||SYS_GUID()||”’,”’||DATAID||”’,”’||V_MANDATESCOPE||”’,”’||V_MANDATETYPE||”’)’; DBMS_OUTPUT.PUT_LINE(‘INPUT1:’||V_SQL ); –監視SQL語句 EXECUTE IMMEDIATE(V_SQL); END IF; –提交 END IF; END PROC_MANDATESCOPE;