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??