Oracle创建触发器详解及例子

来源：互联网发布：淘宝影响权重的因素编辑：程序博客网时间：2024/06/05 10:03

触发器详解

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);--动态sqlBEGIN  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;

0 0