写了个触发器用sys用户登录pl/sql developer
在db_xtwh用户下写了个触发器
create or replace trigger db_xtwh.TRI_UPDATE_WHTOAH
after insert on db_xtwh.t_xt_gnzy
for each rowbeginif inserting then
insert into db_xtwh.for_trigger(gn_dm,mc,mc_j,sjgn_dm,zn_dm,cd_an_dm,gns_lx,cd_xh,lr_sj,xg_sj) values (:new.gn_dm,:new.mc,:new.mc_j,:new.sjgn_dm,:new.zn_dm,:new.cd_an_dm,:new.gns_lx,:new.cd_xh,:new.lr_sj,:new.xg_sj);
end if ;end db_xtwh.TRI_UPDATE_WHTOAH;编译没报错,但是触发器的状态不对,select owner,object_name,object_type,status from dba_objects where object_name ='TRI_UPDATE_WHTOAH';
结果中status状态时Invalid。
往表db_xtwh.t_xt_gnzy插入数据就报:ORA-04098: 触发器 'DB_XTWH.TRI_UPDATE_WHTOAH' 无效且未通过重新验证请给点思路,谢谢。
在db_xtwh用户下写了个触发器
create or replace trigger db_xtwh.TRI_UPDATE_WHTOAH
after insert on db_xtwh.t_xt_gnzy
for each rowbeginif inserting then
insert into db_xtwh.for_trigger(gn_dm,mc,mc_j,sjgn_dm,zn_dm,cd_an_dm,gns_lx,cd_xh,lr_sj,xg_sj) values (:new.gn_dm,:new.mc,:new.mc_j,:new.sjgn_dm,:new.zn_dm,:new.cd_an_dm,:new.gns_lx,:new.cd_xh,:new.lr_sj,:new.xg_sj);
end if ;end db_xtwh.TRI_UPDATE_WHTOAH;编译没报错,但是触发器的状态不对,select owner,object_name,object_type,status from dba_objects where object_name ='TRI_UPDATE_WHTOAH';
结果中status状态时Invalid。
往表db_xtwh.t_xt_gnzy插入数据就报:ORA-04098: 触发器 'DB_XTWH.TRI_UPDATE_WHTOAH' 无效且未通过重新验证请给点思路,谢谢。
before insert on zz_test
declare
-- local variables here
begin
update A set A.c2='insert';
commit;
end tri_test;
conn db_xtwh/passwd;create or replace trigger TRI_UPDATE_WHTOAH
after insert on t_xt_gnzy
for each row
begin
insert into for_trigger(gn_dm,mc,mc_j,sjgn_dm,zn_dm,cd_an_dm,gns_lx,cd_xh,lr_sj,xg_sj)
values (:new.gn_dm,:new.mc,:new.mc_j,:new.sjgn_dm,:new.zn_dm,:new.cd_an_dm,:new.gns_lx,:new.cd_xh,:new.lr_sj,:new.xg_sj);
end db_xtwh.TRI_UPDATE_WHTOAH;
/
CREATE OR REPLACE TRIGGER IMSI2MOBILE_UserPoints_trig
AFTER INSERT OR UPDATE OR DELETE ON imsi2mobile
FOR EACH ROW
DECLARE
v_singlepoints NUMBER(18);
BEGIN
IF UPDATING THEN
UPDATE UserPoints SET mobile=:new.mobile WHERE mobile=:old.mobile;
ELSIF DELETING THEN
DELETE FROM UserPoints WHERE mobile=:old.mobile;
ELSIF INSERTING THEN
SELECT singlepoints INTO v_singlepoints FROM opera_points_map WHERE useropera='TO_REGISTER';
INSERT INTO UserPoints(mobile, usertype, points,grade, cdate) VALUES(:new.mobile,1,v_singlepoints,1,:new.cdate);
END IF;
END;
/
create or replace trigger db_xtwh.TRI_UPDATE_WHTOAH
after insert or update or delete
on db_xtwh.t_xt_gnzy
for each row
declare gnzy db_xtwh.t_xt_gnzy%ROWTYPE;
beginif deleting then
delete from db_xtwh.for_trigger where :old.gn_dm = gn_dm;
end if ;if inserting then
insert into db_xtwh.for_trigger(gn_dm,mc,mc_j,sjgn_dm,zn_dm,cd_an_dm,gns_lx,cd_xh,lr_sj,xg_sj) values (:new.gn_dm,:new.mc,:new.mc_j,:new.sjgn_dm,:new.zn_dm,:new.cd_an_dm,:new.gns_lx,:new.cd_xh,:new.lr_sj,:new.xg_sj);
---select * from db_xtwh.t_xt_gnzy dt where dt.gn_dm = :new.gn_dm;
end if ;if updating then
select * into gnzy from db_xtwh.t_xt_gnzy where gn_dm = :new.gn_dm;
update db_xtwh.for_trigger set mc = gnzy.mc,mc_j = gnzy.mc_j,
sjgn_dm = gnzy.sjgn_dm,zn_dm = gnzy.zn_dm,cd_an_dm = gnzy.cd_an_dm,gns_lx = gnzy.gns_lx,
cd_xh = gnzy.cd_xh,lr_sj = gnzy.lr_sj, xg_sj = gnzy.xg_sj
where gn_dm = gnzy.gn_dm;
end if ;end db_xtwh.TRI_UPDATE_WHTOAH;
进去 triger 里面 打开TRI_UPDATE_WHTOAH,编译下,看报什么错误!
-- 哎:你别乱套了好不好?
谢谢。