*******100分*******trigger使用for update的困惑?
imdt 2002-04-12 08:26:03 create or repalce trigger Insert_sr
before insert on service_record
declare
old_id varchar2(14);
num_id number(14);
new_id varchar2(14);
BEGIN
--
---第一部分,选出最大id值
select max(srid) into old_id
from SRID_TB
where to_char(SRID_DAT,'yyyymmdd')=to_char(SYSDATE,'yyyymmdd');
--第二部分,产生新的id
if (old_id is not null) or (old_id='') then
--old_id 加一
num_id:=to_number(old_id);
num_id:=num_id +1;
new_id:=to_char(num_id);
UPDATE SRID_TB
SET SRID=new_id
WHERE SRID=old_id;
else
--NEW_id
new_id:=to_char(SYSDATE,'yyyymmdd');
new_id:=new_id || '000001';
INSERT INTO SRID_TB VALUES(new_id,SYSDATE);
end if;
:NEW.SR_ID:=new_id;
-----第三部分、试图进行异常处理
/*WHEN NO_DATA_FOUND THEN
--NEW_id
new_id:=to_char(SYSDATE,'yyyymmdd');
new_id:=new_id || '000001';
INSERT INTO SRID_TB VALUES(new_id,SYSDATE);
:NEW.SR_ID:=new_id;
*/
END;