create or replace trigger SEAL_USE_APPLY_SEQUENCE_OLD
before insert on seal_use_apply
for each row
declare
-- local variables here
V_MESSION_NUM NUMBER(12);
begin
---判断 如果本年度本类型申请单存在该任务号 则将当前待存数据任务号加1再存
SELECT T.GW_YEAR_NUMBER INTO V_MESSION_NUM FROM seal_use_apply T
WHERE
T.GW_YEAR = :NEW.GW_YEAR
AND
T.GW_YEAR_NUMBER = :NEW.GW_YEAR_NUMBER
AND
T.APPLY_TYPE = :NEW.APPLY_TYPE;
--------如果重任务号了 则将待存任务号加1
IF V_MESSION_NUM > 0 THEN
:NEW.GW_YEAR_NUMBER := :NEW.GW_YEAR_NUMBER+1;
END IF;
exception
--异常处理代码块
when no_data_found then
dbms_output.put_line('发生系统异常:未找到有效的数据!');
before insert on seal_use_apply
for each row
declare
-- local variables here
V_MESSION_NUM NUMBER(12);
begin
---判断 如果本年度本类型申请单存在该任务号 则将当前待存数据任务号加1再存
SELECT T.GW_YEAR_NUMBER INTO V_MESSION_NUM FROM seal_use_apply T
WHERE
T.GW_YEAR = :NEW.GW_YEAR
AND
T.GW_YEAR_NUMBER = :NEW.GW_YEAR_NUMBER
AND
T.APPLY_TYPE = :NEW.APPLY_TYPE;
--------如果重任务号了 则将待存任务号加1
IF V_MESSION_NUM > 0 THEN
:NEW.GW_YEAR_NUMBER := :NEW.GW_YEAR_NUMBER+1;
END IF;
exception
--异常处理代码块
when no_data_found then
dbms_output.put_line('发生系统异常:未找到有效的数据!');
end SEAL_USE_APPLY_SEQUENCE_OLD;
--触发器将自增序列值填充到指定字段
---升级时 提取系统最大任务号 (即升级前模拟申请一个表单 序列初始值要比取到的任务号大 保证不重复);
create sequence SEAL_USE_APPLY_SEQUENCE
minvalue 1
maxvalue 99999999999
start with 50
increment by 1
cache 20;
---创建了一个触发器 将任务号分发工作转移至数据库端 解决并发问题;
create or replace trigger seal_use_apply_trigger
before insert on seal_use_apply
for each row
DECLARE
BEGIN
select SEAL_USE_APPLY_SEQUENCE.nextval into :NEW.GW_YEAR_NUMBER from dual;
end
seal_use_apply_trigger;