每次都是insert第二条以后才能找到记录,也就是说第一次insert的触发操作:new能取到值,但执行查询不能到结果(找不到第一条记录)全文如下:create or replace trigger TIF_FAS_ATND_REC
after insert on fas_atnd_rec
for each row
declare maxhours number;
myvalue number;
totalvalue number;
countN int;
-- local variables here
cursor attendanceCur is select * from FAS_ATND_REC where
:new.ATRC_WKER_FK_IX01 = FAS_ATND_REC.ATRC_WKER_FK_IX01 and to_char(:new.ATND_REC_WKR_OUT_DATE,'YYYY-MM-DD') = to_char(FAS_ATND_REC.ATND_REC_WKR_OUT_DATE,'YYYY-MM-DD') ;
pragma autonomous_transaction;
begin
totalvalue := 0;
for cur_result in attendanceCur loop
begin
insert into fordebug(OUTTIME) values(cur_result.ATND_REC_WKR_OUT_DATE);
myvalue := to_number(
to_date(to_char(cur_result.atnd_rec_wkr_out_date,'yyyy-mm-dd hh:mi:ss'),'yyyy-mm-dd hh:mi:ss')
-
to_date(to_char(cur_result.atnd_rec_wkr_in_date,'yyyy-mm-dd hh:mi:ss'),'yyyy-mm-dd hh:mi:ss')
);
totalvalue := totalvalue + myvalue;
end ;
end loop;
totalvalue := totalvalue * 24;
--if totalvalue=0 then
--begin
-- insert into fordebug(OUTTIME) values(:new.ATND_REC_WKR_OUT_DATE);
--end;
--end if;
select WKR_DLY_MAX_WORK_HOURS into maxHours from Fas_Wkr
where :new.ATRC_WKER_FK_IX01 = WKER_PK;
select count(*) into countN from FAS_WORK_HOUR_REC where :new.ATRC_WKER_FK_IX01 = FAS_WORK_HOUR_REC.WKHR_WKER_FK_IX01
and to_char(FAS_WORK_HOUR_REC.WORK_HOUR_REC_DATE,'YYYY-MM-DD') = to_char(:new.ATND_REC_WKR_OUT_DATE,'YYYY-MM-DD'); if totalvalue > maxHours then
begin
if countN>0 then
begin
update FAS_WORK_HOUR_REC whr
set whr.work_hour_rec_val=maxHours
where :new.ATRC_WKER_FK_IX01= whr.WKHR_WKER_FK_IX01
and to_char(whr.WORK_HOUR_REC_DATE,'YYYY-MM-DD') = to_char(:new.ATND_REC_WKR_OUT_DATE,'YYYY-MM-DD');
end;
else
begin
insert into FAS_WORK_HOUR_REC(FAS_WORK_HOUR_REC.WKHR_PK,FAS_WORK_HOUR_REC.WORK_HOUR_REC_DATE,FAS_WORK_HOUR_REC.WORK_HOUR_REC_VAL,FAS_WORK_HOUR_REC.WKHR_WKER_FK_IX01)
values(SEQ_WORK_HOUR_REC_ID.NEXTVAL,:new.ATND_REC_WKR_OUT_DATE,maxHours,:new.ATRC_WKER_FK_IX01);
end;
end if;
end;
else
begin
if countN>0 then
begin
update FAS_WORK_HOUR_REC whr
set whr.work_hour_rec_val=totalvalue
where :new.ATRC_WKER_FK_IX01= whr.WKHR_WKER_FK_IX01
and to_char(whr.WORK_HOUR_REC_DATE,'YYYY-MM-DD') = to_char(:new.ATND_REC_WKR_OUT_DATE,'YYYY-MM-DD');
end;
else
begin
insert into FAS_WORK_HOUR_REC(FAS_WORK_HOUR_REC.WKHR_PK,FAS_WORK_HOUR_REC.WORK_HOUR_REC_DATE,FAS_WORK_HOUR_REC.WORK_HOUR_REC_VAL,FAS_WORK_HOUR_REC.WKHR_WKER_FK_IX01)
values(SEQ_WORK_HOUR_REC_ID.NEXTVAL,:new.ATND_REC_WKR_OUT_DATE,totalvalue,:new.ATRC_WKER_FK_IX01);
end;
end if;
end;
end if;
commit;
end TIE_FAS_ATND_REC;请高人指点,先谢过!
after insert on fas_atnd_rec
for each row
declare maxhours number;
myvalue number;
totalvalue number;
countN int;
-- local variables here
cursor attendanceCur is select * from FAS_ATND_REC where
:new.ATRC_WKER_FK_IX01 = FAS_ATND_REC.ATRC_WKER_FK_IX01 and to_char(:new.ATND_REC_WKR_OUT_DATE,'YYYY-MM-DD') = to_char(FAS_ATND_REC.ATND_REC_WKR_OUT_DATE,'YYYY-MM-DD') ;
pragma autonomous_transaction;
begin
totalvalue := 0;
for cur_result in attendanceCur loop
begin
insert into fordebug(OUTTIME) values(cur_result.ATND_REC_WKR_OUT_DATE);
myvalue := to_number(
to_date(to_char(cur_result.atnd_rec_wkr_out_date,'yyyy-mm-dd hh:mi:ss'),'yyyy-mm-dd hh:mi:ss')
-
to_date(to_char(cur_result.atnd_rec_wkr_in_date,'yyyy-mm-dd hh:mi:ss'),'yyyy-mm-dd hh:mi:ss')
);
totalvalue := totalvalue + myvalue;
end ;
end loop;
totalvalue := totalvalue * 24;
--if totalvalue=0 then
--begin
-- insert into fordebug(OUTTIME) values(:new.ATND_REC_WKR_OUT_DATE);
--end;
--end if;
select WKR_DLY_MAX_WORK_HOURS into maxHours from Fas_Wkr
where :new.ATRC_WKER_FK_IX01 = WKER_PK;
select count(*) into countN from FAS_WORK_HOUR_REC where :new.ATRC_WKER_FK_IX01 = FAS_WORK_HOUR_REC.WKHR_WKER_FK_IX01
and to_char(FAS_WORK_HOUR_REC.WORK_HOUR_REC_DATE,'YYYY-MM-DD') = to_char(:new.ATND_REC_WKR_OUT_DATE,'YYYY-MM-DD'); if totalvalue > maxHours then
begin
if countN>0 then
begin
update FAS_WORK_HOUR_REC whr
set whr.work_hour_rec_val=maxHours
where :new.ATRC_WKER_FK_IX01= whr.WKHR_WKER_FK_IX01
and to_char(whr.WORK_HOUR_REC_DATE,'YYYY-MM-DD') = to_char(:new.ATND_REC_WKR_OUT_DATE,'YYYY-MM-DD');
end;
else
begin
insert into FAS_WORK_HOUR_REC(FAS_WORK_HOUR_REC.WKHR_PK,FAS_WORK_HOUR_REC.WORK_HOUR_REC_DATE,FAS_WORK_HOUR_REC.WORK_HOUR_REC_VAL,FAS_WORK_HOUR_REC.WKHR_WKER_FK_IX01)
values(SEQ_WORK_HOUR_REC_ID.NEXTVAL,:new.ATND_REC_WKR_OUT_DATE,maxHours,:new.ATRC_WKER_FK_IX01);
end;
end if;
end;
else
begin
if countN>0 then
begin
update FAS_WORK_HOUR_REC whr
set whr.work_hour_rec_val=totalvalue
where :new.ATRC_WKER_FK_IX01= whr.WKHR_WKER_FK_IX01
and to_char(whr.WORK_HOUR_REC_DATE,'YYYY-MM-DD') = to_char(:new.ATND_REC_WKR_OUT_DATE,'YYYY-MM-DD');
end;
else
begin
insert into FAS_WORK_HOUR_REC(FAS_WORK_HOUR_REC.WKHR_PK,FAS_WORK_HOUR_REC.WORK_HOUR_REC_DATE,FAS_WORK_HOUR_REC.WORK_HOUR_REC_VAL,FAS_WORK_HOUR_REC.WKHR_WKER_FK_IX01)
values(SEQ_WORK_HOUR_REC_ID.NEXTVAL,:new.ATND_REC_WKR_OUT_DATE,totalvalue,:new.ATRC_WKER_FK_IX01);
end;
end if;
end;
end if;
commit;
end TIE_FAS_ATND_REC;请高人指点,先谢过!
要用:new