Oracle 触发器 创建

在创建Oracle 触发器的时候需要注意一点，需要触发器的末尾添加上“/”（斜杠），Oracle 是认"/"为触发器的结束符。

CREATE OR REPLACE TRIGGER "T_C_DATA_TR"BEFORE INSERT ON "T_C_DATA_TEMP"FOR EACH ROWwhen (new."ID" IS NULL)BEGIN        SELECT "T_C_DATA_SQ".nextval        INTO :new."ID" FROM dual;END/

示例：

create table T_C_DATA(  msisdn     VARCHAR2(32),  channel_id VARCHAR2(16),  time       DATE,  ua         VARCHAR2(64),  platform   VARCHAR2(20),  act        VARCHAR2(2),  result     VARCHAR2(8),  province   VARCHAR2(4),  imsi       VARCHAR2(32),  imei       VARCHAR2(32),  app        VARCHAR2(64));insert into T_C_DATA (MSISDN, CHANNEL_ID, TIME, UA, PLATFORM, ACT, RESULT, PROVINCE, IMSI, IMEI, APP)values ('13606029971', null, to_date('24-05-2011 17:00:35', 'dd-mm-yyyy hh24:mi:ss'), 'NokiaE52-1', '0', '2', '0', '0', '460006006399161', '359327034255640', 'fjdm1.0.0.001.005_CTS60V31_JT');insert into T_C_DATA (MSISDN, CHANNEL_ID, TIME, UA, PLATFORM, ACT, RESULT, PROVINCE, IMSI, IMEI, APP)values ('13606029971', null, to_date('24-05-2011 17:00:44', 'dd-mm-yyyy hh24:mi:ss'), 'NokiaE52-1', '0', '3', '0', '0', '460006006399161', '359327034255640', null);insert into T_C_DATA (MSISDN, CHANNEL_ID, TIME, UA, PLATFORM, ACT, RESULT, PROVINCE, IMSI, IMEI, APP)values ('13466573225', null, to_date('24-05-2011 17:00:52', 'dd-mm-yyyy hh24:mi:ss'), 'zte-me/mobile', '99', '1', '1', '0', '460020666231846', '351510043766602', null);insert into T_C_DATA (MSISDN, CHANNEL_ID, TIME, UA, PLATFORM, ACT, RESULT, PROVINCE, IMSI, IMEI, APP)values ('15160074046', null, to_date('24-05-2011 17:04:11', 'dd-mm-yyyy hh24:mi:ss'), 'NOKIAN95', '0', '2', '0', '0', '460021600084350', '355501017319672', 'fjdm1.0.0.001.005_CTS60V31_JT');insert into T_C_DATA (MSISDN, CHANNEL_ID, TIME, UA, PLATFORM, ACT, RESULT, PROVINCE, IMSI, IMEI, APP)values ('15160074046', null, to_date('24-05-2011 17:07:18', 'dd-mm-yyyy hh24:mi:ss'), 'NOKIAN95', '0', '3', '0', '0', '460021600084350', '355501017319672', null);create table T_C_DATA_TEMP(  id         number(11),  msisdn     VARCHAR2(32),  channel_id VARCHAR2(16),  time       DATE,  ua         VARCHAR2(64),  platform   VARCHAR2(20),  act        VARCHAR2(2),  result     VARCHAR2(8),  province   VARCHAR2(4),  imsi       VARCHAR2(32),  imei       VARCHAR2(32),  app        VARCHAR2(64),   primary key (ID));create sequence T_C_DATA_SQminvalue 1maxvalue 999999999999999999999999999start with 1increment by 1cache 20;CREATE OR REPLACE TRIGGER "T_C_DATA_TR"BEFORE INSERT ON "T_C_DATA_TEMP"FOR EACH ROWwhen (new."ID" IS NULL)BEGIN        SELECT "T_C_DATA_SQ".nextval        INTO :new."ID" FROM dual;END;/ ---如果把这个斜杠去掉，在创建或修改触发器的时，会认为后面的SQL命令还是属于当前这个触器。rename  T_C_DATA  to  T_C_DATA_BK2  ;rename  T_C_DATA_TEMP  to  T_C_DATA  ;CREATE OR REPLACE TRIGGER "T_C_DATA_TR"BEFORE INSERT ON "T_C_DATA"FOR EACH ROWwhen (new."ID" IS NULL)BEGIN        SELECT "T_C_DATA_SQ".nextval        INTO :new."ID" FROM dual;END ;INSERT   INTO T_C_DATA (msisdn,channel_id,time,ua,platform,act,result,province,imsi,imei,app)SELECT  bk2.msisdn,bk2.channel_id,bk2.time,bk2.ua,bk2.platform,bk2.act,bk2.result,bk2.province,bk2.imsi,bk2.imei,bk2.appFROM   T_C_DATA_BK2 bk2;commit

?