連續建立多個Oracle觸發器失敗，單個建立才成功的解決方案

標籤：pid   word   成功   strong   nocache   har   觸發器   default   const   

當用到自增序列，需要用到觸發器的時候，當我連續執行建立多個觸發器時，總是報編譯通過，但存在警告或錯誤。

---1.應用資訊drop table app_info cascade constraints;create table APP_INFO(  id      NUMBER(9)   not null,  appid    VARCHAR2(255)        default NULL,  appkey    VARCHAR2(255)       default NULL,  md5_key          VARCHAR2(255)    default NULL,  aes_key    VARCHAR2(255)      default NULL,  permission    VARCHAR2(3000)     default NULL,  email    VARCHAR2(255)       default NULL,  password    VARCHAR2(255)      default NULL,  ras_public_key     VARCHAR2(255)  default NULL,  constraint APP_INFO primary key (id));drop sequence app_info_id;create sequence app_info_idincrement by 1start with 1nomaxvaluenominvaluenocache;CREATE OR REPLACE TRIGGER addAppInfo BEFORE INSERT ON App_INFO FOR EACH ROW   BEGIN      SELECT app_info_id.nextval INTO :new.id  FROM dual;   END; ---2.unit資訊drop table unit_info cascade constraints;create table UNIT_INFO(  UNIT_ID        NUMBER(9)  not null,  USERID          VARCHAR2(255)        default NULL,  UNIT_NAME      VARCHAR2(128)        default NULL,  UNIT_LINKMAN      VARCHAR2(64)    default NULL,  ORGANIZATION_CODE        VARCHAR2(64)     default NULL,  constraint UNIT_INFO primary key (UNIT_ID));drop sequence unit_info_id;create sequence unit_info_idincrement by 1start with 1nomaxvaluenominvaluenocache;CREATE OR REPLACE TRIGGER addUnitInfo BEFORE INSERT ON UNIT_INFO FOR EACH ROW   BEGIN      SELECT unit_info_id.nextval INTO :new.unit_id  FROM dual;   END; 

錯誤如下：

這種建立是失敗的，因為觸發器需要先編譯，每建立一個觸發器，需要以“/”結束，然後才能執行下一個。

正確的做法應該是這樣的;

---1.應用資訊drop table app_info cascade constraints;create table APP_INFO(  id      NUMBER(9)   not null,  appid    VARCHAR2(255)        default NULL,  appkey    VARCHAR2(255)       default NULL,  md5_key          VARCHAR2(255)    default NULL,  aes_key    VARCHAR2(255)      default NULL,  permission    VARCHAR2(3000)     default NULL,  email    VARCHAR2(255)       default NULL,  password    VARCHAR2(255)      default NULL,  ras_public_key     VARCHAR2(255)  default NULL,  constraint APP_INFO primary key (id));drop sequence app_info_id;create sequence app_info_idincrement by 1start with 1nomaxvaluenominvaluenocache;CREATE OR REPLACE TRIGGER addAppInfo BEFORE INSERT ON App_INFO FOR EACH ROW   BEGIN      SELECT app_info_id.nextval INTO :new.id  FROM dual;   END; /---2.unit資訊drop table unit_info cascade constraints;create table UNIT_INFO(  UNIT_ID        NUMBER(9)  not null,  USERID          VARCHAR2(255)        default NULL,  UNIT_NAME      VARCHAR2(128)        default NULL,  UNIT_LINKMAN      VARCHAR2(64)    default NULL,  ORGANIZATION_CODE        VARCHAR2(64)     default NULL,  constraint UNIT_INFO primary key (UNIT_ID));drop sequence unit_info_id;create sequence unit_info_idincrement by 1start with 1nomaxvaluenominvaluenocache;CREATE OR REPLACE TRIGGER addUnitInfo BEFORE INSERT ON UNIT_INFO FOR EACH ROW   BEGIN      SELECT unit_info_id.nextval INTO :new.unit_id  FROM dual;   END; /

 注意：預存程序也是一樣的。

連續建立多個Oracle觸發器失敗，單個建立才成功的解決方案