ORA 触发器的问题,大侠请进
DROP TABLE SIMM_H_F_MASTER CASCADE CONSTRAINTS ; CREATE TABLE SIMM_H_F_MASTER ( IMSI_H VARCHAR2 (20) NOT NULL, MSISDN_H VARCHAR2 (20) NOT NULL, NETWORKID NUMBER (2) NOT NULL, PROVINCE_NUM NUMBER (2), IMSI_F VARCHAR2 (20), MSISDN_F VARCHAR2 (20), COS VARCHAR2 (3), STATUS NUMBER (1) DEFAULT 1 NOT NULL, CONSTRAINT SIMM_H_F_MASTER_PK PRIMARY KEY ( MSISDN_H, NETWORKID ) USING INDEX ) ; DROP TABLE SIMM_UPDATE_SUBS_INFO CASCADE CONSTRAINTS ; CREATE TABLE SIMM_UPDATE_SUBS_INFO( IMSI_H VARCHAR2 (20) NOT NULL, MSISDN_H VARCHAR2 (20) NOT NULL, IMSI_F VARCHAR2 (20), MSISDN_F VARCHAR2 (20), STATUS NUMBER (1) DEFAULT 1 NOT NULL, SENDOUT NUMBER (1) DEFAULT 0 NOT NULL, INSTANCE_NAME VARCHAR2 (20), LAST_MODIFIED_DAT DATE , CONSTRAINT SIMM_UPDATE_SUBS_INFO PRIMARY KEY (IMSI_H) USING INDEX ) ;CREATE OR REPLACE TRIGGER TRIG_UPDATE_SUNBS AFTER INSERT OR UPDATE OR DELETE ON SIMM_H_F_MASTER FOR EACH ROWBEGIN if inserting then if exists(SELECT IMSI_H FROM SIMM_UPDATE_SUBS_INFO WHERE IMSI_H = :NEW.IMSI_H) then delete from SIMM_UPDATE_SUBS_INFO where IMSI_H = :NEW.IMSI_H; end if; insert into SIMM_UPDATE_SUBS_INFO (IMSI_H,MSISDN_H,IMSI_F,MSISDN_F,STATUS,LAST_MODIFIED_DAT,INSTANCE_NAME,SENDOUT) values (:NEW.IMSI_H,:NEW.MSISDN_H,:NEW.IMSI_F,:NEW.MSISDN_F,:NEW.STATUS,sysdate,'SIMM-R-CUINCOM',0); end if; if deleting then update SIMM_UPDATE_SUBS_INFO set MSISDN_H=:OLD.MSISDN_H,IMSI_F=:OLD.IMSI_F,MSISDN_F=:OLD.MSISDN_F,STATUS=-1,LAST_MODIFIED_DAT=sysdate,SENDOUT=0 where IMSI_H=:OLD.IMSI_H; end if; if updating then update SIMM_UPDATE_SUBS_INFO set MSISDN_H=:NEW.MSISDN_H,IMSI_F=:NEW.IMSI_F,MSISDN_F=:NEW.MSISDN_F,STATUS=:NEW.STATUS,LAST_MODIFIED_DAT=sysdate,SENDOUT=0 where IMSI_H=:OLD.IMSI_H; end if; END
