create or replace trigger tri_voucher_唯一性
before insert on voucher
for each row
declare
i int := 0;
begin /*select * from (select * from voucher where occured_date <= SYSDATE AND occured_date > to_date(EXTRACT(year from sysdate)-1||'/12'||'/31','YYYY/MM/DD')) A
WHERE A.GENERAL_WORD like '06付%';*/ --取当年度所有相同总字第几字的值之和;
select count(*)
into i
from (select *
from voucher
where occured_date <= SYSDATE
AND occured_date >
to_date(EXTRACT(year from sysdate) - 1 || '/12' || '/31',
'YYYY/MM/DD')) A
Where general_word = :new.general_word
and sequence_nbr = :new.sequence_nbr; if (i > 0) then
raise_application_error(-20001,
'在本会计年度,已存在相同的序列号和总字号');
end if;
end tri_voucher_唯一性;
before insert on voucher
for each row
declare
i int := 0;
begin /*select * from (select * from voucher where occured_date <= SYSDATE AND occured_date > to_date(EXTRACT(year from sysdate)-1||'/12'||'/31','YYYY/MM/DD')) A
WHERE A.GENERAL_WORD like '06付%';*/ --取当年度所有相同总字第几字的值之和;
select count(*)
into i
from (select *
from voucher
where occured_date <= SYSDATE
AND occured_date >
to_date(EXTRACT(year from sysdate) - 1 || '/12' || '/31',
'YYYY/MM/DD')) A
Where general_word = :new.general_word
and sequence_nbr = :new.sequence_nbr; if (i > 0) then
raise_application_error(-20001,
'在本会计年度,已存在相同的序列号和总字号');
end if;
end tri_voucher_唯一性;
在before行級觸發器中,將新insert的數據記錄到一個package的變量裡,
然後再建一個after語句級觸發器試一下吧。
我在插入时,用的是存储过程,在存储过程顺利完成后,即COMMIT;C#客户端conn.close();然后紧跟又插入相同的记录;为何会出现触发器不识别的现象呢?
虽说提供的网页说明与我的问题不是一回事,但是感谢你的热心,现将分全给你.今天重开机后,ORACLE不存在我所提出的这个问题了,触发器能正常工作.连续插入新数据会报错了,好奇怪!