Oracle 触发器 创建

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

CREATE OR REPLACE TRIGGER "T_C_DATA_TR"
BEFORE INSERT ON "T_C_DATA_TEMP"
FOR EACH ROW
when (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_SQ
minvalue 1
maxvalue 999999999999999999999999999
start with 1
increment by 1
cache 20;

CREATE OR REPLACE TRIGGER "T_C_DATA_TR"
BEFORE INSERT ON "T_C_DATA_TEMP"
FOR EACH ROW
when (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 ROW
when (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.app
FROM   T_C_DATA_BK2 bk2;

commit