需要先做个视图 create or replace view vw_ayear as select "YR","STARTFLAG","STARTDATE","CLOSETAG","TURNFLAG" from ayear--创建触发器 CREATE OR REPLACE TRIGGER ayear_TR INSTEAD OF INSERT ON vw_ayear FOR EACH ROW DECLARE TEMP INT ; BEGIN select 0 into TEMP from dual; select count(*) into TEMP from ayear a where a.yr=:new.yr and a.startflag=:new.startflag; IF TEMP <> 0 THEN UPDATE ayear a SET a.startdate = :new.startdate , a.closetag=:new.closetag , a.turnflag=:new.turnflag where a.yr=:new.yr and a.startflag=:new.startflag; ELSE insert into ayear values(:new.yr,:new.startflag,:new.startdate,:new.closetag,:new.turnflag); END IF; END;测试sql insert into vw_ayear values('2007','1',to_date('2007-04-01','yyyy-MM-dd'),'0','0')不做视图是不行的当然本人水平有限希望有牛人提出更好的想法
create or replace view vw_ayear as
select "YR","STARTFLAG","STARTDATE","CLOSETAG","TURNFLAG" from ayear--创建触发器
CREATE OR REPLACE TRIGGER ayear_TR
INSTEAD OF INSERT
ON vw_ayear
FOR EACH ROW
DECLARE TEMP INT ;
BEGIN
select 0 into TEMP from dual;
select count(*) into TEMP from ayear a where a.yr=:new.yr and a.startflag=:new.startflag;
IF TEMP <> 0 THEN
UPDATE ayear a SET a.startdate = :new.startdate , a.closetag=:new.closetag , a.turnflag=:new.turnflag
where a.yr=:new.yr and a.startflag=:new.startflag;
ELSE
insert into ayear values(:new.yr,:new.startflag,:new.startdate,:new.closetag,:new.turnflag);
END IF;
END;测试sql
insert into vw_ayear values('2007','1',to_date('2007-04-01','yyyy-MM-dd'),'0','0')不做视图是不行的当然本人水平有限希望有牛人提出更好的想法