大家帮忙给我看看我的触发器为什么不能运行啊,我想在每个月的某一天向表里面插一条记录。SQL语句都是对的,编译也能通过,但是就是不插入数据。这个例子是在今天插入记录的,但是早晨过来还是没有插入成功。
create or replace trigger insertHY
before insert on tbl_zdsy_gsjfhysfb
for each row
declare
newTime date;
oldTime date;
begin
select YCSJ into newTime from tbl_zdsy_ycsj_xx
where rybs='4' and to_char(sysdate,'yyyy-mm')=to_char(YCSJ,'yyyy-mm');
if(to_char(sysdate,'DD') ='20') then
oldTime:=newTime;
insert into tbl_zdsy_gsjfhysfb(HYBH,hymc,BIAOSHI,sbqx) values('0600','煤炭开采和洗选业','1',oldTime);
end if;
end;
create or replace trigger insertHY
before insert on tbl_zdsy_gsjfhysfb
for each row
declare
newTime date;
oldTime date;
begin
select YCSJ into newTime from tbl_zdsy_ycsj_xx
where rybs='4' and to_char(sysdate,'yyyy-mm')=to_char(YCSJ,'yyyy-mm');
if(to_char(sysdate,'DD') ='20') then
oldTime:=newTime;
insert into tbl_zdsy_gsjfhysfb(HYBH,hymc,BIAOSHI,sbqx) values('0600','煤炭开采和洗选业','1',oldTime);
end if;
end;
BEFORE INSERT ON TBL_ZDSY_GSJFHYSFB
FOR EACH ROW
DECLARE
NEWTIME DATE;
OLDTIME DATE;
BEGIN
SELECT YCSJ
INTO NEWTIME
FROM TBL_ZDSY_YCSJ_XX
WHERE RYBS = '4'
AND TO_CHAR(SYSDATE, 'yyyy-mm') = TO_CHAR(YCSJ, 'yyyy-mm');
IF (TO_CHAR(SYSDATE, 'DD') = '20') THEN
OLDTIME := NEWTIME;
INSERT INTO TBL_ZDSY_GSJFHYSFB
(HYBH, HYMC, BIAOSHI, SBQX)
VALUES
('0600', '煤炭开采和洗选业', '1', OLDTIME);
END IF;
END;
/
after insert
on tbl_zdsy_gsjfhysfb
for each row
declare
newTime date;
oldTime date; begin
select YCSJ into newTime from tbl_zdsy_ycsj_xx
where rybs='4' and to_char(sysdate,'yyyy-mm')=to_char(YCSJ,'yyyy-mm');
if(to_char(sysdate,'DD') ='20') then oldTime:=newTime;
insert into tbl_zdsy_gsjfhysfb(HYBH,hymc,BIAOSHI,sbqx) values('0600','煤炭开采和洗选业','1',oldTime); end if;
end;
before insert on tbl_zdsy_ycsj_xx
for each row
declare
newTime date;
oldTime date;
begin
if(to_char(sysdate,'DD') ='20') and (:new.rybs = '4') and (to_char(sysdate,'yyyy-mm')=to_char(:new.YCSJ,'yyyy-mm')) then
insert into tbl_zdsy_gsjfhysfb(HYBH,hymc,BIAOSHI,sbqx) values('0600','煤炭开采和洗选业','1',:new.YCSJ);
end if;
end;
用procedure,并使用job就可以了:CREATE OR REPLACE PROCEDURE INSERTHY IS
NEWTIME DATE;
OLDTIME DATE;
BEGIN
SELECT YCSJ
INTO NEWTIME
FROM TBL_ZDSY_YCSJ_XX
WHERE RYBS = '4'
AND TO_CHAR(SYSDATE, 'yyyy-mm') = TO_CHAR(YCSJ, 'yyyy-mm');
IF (TO_CHAR(SYSDATE, 'DD') = '20') THEN
OLDTIME := NEWTIME;
INSERT INTO TBL_ZDSY_GSJFHYSFB
(HYBH, HYMC, BIAOSHI, SBQX)
VALUES
('0600', '煤炭开采和洗选业', '1', OLDTIME);
END IF;
COMMIT;
END;
/DECLARE
JOBNO NUMBER;
INSTNO NUMBER;
BEGIN
DBMS_JOB.SUBMIT(JOBNO,
'INSERTHY;',
SYSDATE,
'TRUNC(SYSDATE)+1+2/(24*60)',
TRUE);
COMMIT;
END;
/
detail...
http://blog.csdn.net/simonezhlx/archive/2009/03/20/4006795.aspx