CREATE trigger zk.i_user_msc_tri
AFTER INSERT on zk.i_user_msc
FOR EACH ROW
WHEN(new.prod_id = 30500401)BEGIN
insert into zk.i_user_msc_new(SERV_ID, REGION_CODE, PROD_ID, FUNC_TYPE, SERVICE_ID, OPER_TYPE, BUSI_TYPE, FUNC_PARAM,PROPERTY, BEGIN_DATE, VALID_DATE, EXPIRE_DATE, SID, SO_NBR, REMARK, PLAN_ID) values(new.SERV_ID, new.REGION_CODE, new.PROD_ID, new.FUNC_TYPE, new.SERVICE_ID, new.OPER_TYPE, new.BUSI_TYPE, new.FUNC_PARAM,new.PROPERTY, new.BEGIN_DATE, new.VALID_DATE, new.EXPIRE_DATE, new.SID, new.SO_NBR, new.REMARK, new.PLAN_ID)
END但是当插入数据时老提示“”ORA-04098:触发器无效且未通过重新验证 哪位大侠给分析下原因吧
AFTER INSERT on zk.i_user_msc
FOR EACH ROW
WHEN(new.prod_id = 30500401)BEGIN
insert into zk.i_user_msc_new(SERV_ID, REGION_CODE, PROD_ID, FUNC_TYPE, SERVICE_ID, OPER_TYPE, BUSI_TYPE, FUNC_PARAM,PROPERTY, BEGIN_DATE, VALID_DATE, EXPIRE_DATE, SID, SO_NBR, REMARK, PLAN_ID) values(new.SERV_ID, new.REGION_CODE, new.PROD_ID, new.FUNC_TYPE, new.SERVICE_ID, new.OPER_TYPE, new.BUSI_TYPE, new.FUNC_PARAM,new.PROPERTY, new.BEGIN_DATE, new.VALID_DATE, new.EXPIRE_DATE, new.SID, new.SO_NBR, new.REMARK, new.PLAN_ID)
END但是当插入数据时老提示“”ORA-04098:触发器无效且未通过重新验证 哪位大侠给分析下原因吧
before INSERT on zk.i_user_msc
FOR EACH ROW
BEGIN
if :new.prod_id == 30500401 then
insert into zk.i_user_msc_new(SERV_ID, REGION_CODE, PROD_ID, FUNC_TYPE, SERVICE_ID, OPER_TYPE, BUSI_TYPE, FUNC_PARAM,PROPERTY, BEGIN_DATE, VALID_DATE, EXPIRE_DATE, SID, SO_NBR, REMARK, PLAN_ID) values(:new.SERV_ID, :new.REGION_CODE, :new.PROD_ID, :new.FUNC_TYPE, :new.SERVICE_ID, :new.OPER_TYPE, :new.BUSI_TYPE, :new.FUNC_PARAM,:new.PROPERTY, :new.BEGIN_DATE, :new.VALID_DATE, :new.EXPIRE_DATE, :new.SID, :new.SO_NBR, :new.REMARK, :new.PLAN_ID);
END if;
end;
AFTER INSERT on zk.i_user_msc
FOR EACH ROW
BEGIN
if:new.prod_id = 30500401 then
insert into zk.i_user_msc_new(SERV_ID,REGION_CODE,PROD_ID,FUNC_TYPE,SERVICE_ID,OPER_TYPE,BUSI_TYPE,FUNC_PARAM,PROPERTY,BEGIN_DATE,VALID_DATE,EXPIRE_DATE,SID,SO_NBR,REMARK,PLAN_ID)values(:new.SERV_ID,:new.REGION_CODE,:new.PROD_ID,:new.FUNC_TYPE,:new.SERVICE_ID,:new.OPER_TYPE,:new.BUSI_TYPE,:new.FUNC_PARAM,:new.PROPERTY,:new.BEGIN_DATE,:new.VALID_DATE,:new.EXPIRE_DATE,:new.SID,:new.SO_NBR,:new.REMARK,:new.PLAN_ID);
end if
END
2 AFTER INSERT on store_parameter
3 FOR EACH ROW
4 BEGIN
5 if:new.id = 1 then
6
7 insert into store_parameter1(id)values(:new.id);
8 end if;
9 END;
10 /
Trigger created
SQL> truncate table store_parameter;
Table truncated
SQL> truncate table store_parameter1;
Table truncated
SQL> insert into store_parameter values(1);
1 row inserted
SQL> commit;
Commit complete
SQL> select * from store_parameter;
ID
--
1
SQL> select * from store_parameter1;
ID
--
1
SQL>
CREATE trigger zk.i_user_msc_tri
before INSERT on zk.i_user_msc
FOR EACH ROW
BEGIN
if :new.prod_id = 30500401 then
insert into zk.i_user_msc_new
(SERV_ID, REGION_CODE, PROD_ID,
FUNC_TYPE, SERVICE_ID, OPER_TYPE,
BUSI_TYPE, FUNC_PARAM,PROPERTY,
BEGIN_DATE, VALID_DATE, EXPIRE_DATE,
SID, SO_NBR, REMARK, PLAN_ID)
values
(:new.SERV_ID, :new.REGION_CODE,:new.PROD_ID,
:new.FUNC_TYPE, :new.SERVICE_ID,:new.OPER_TYPE,
:new.BUSI_TYPE, :new.FUNC_PARAM,:new.PROPERTY,
:new.BEGIN_DATE, :new.VALID_DATE, :new.EXPIRE_DATE,
:new.SID, :new.SO_NBR, :new.REMARK, :new.PLAN_ID);
END if;
end;
注意这三点,你的就没问题了!