数据表结构例如:
/*==============================================================*/
/* Table: T_DEMO_HEAD */
/*==============================================================*/
create table T_DEMO_HEAD (
EMS_HEAD_ID CHAR(6) not null,
CORP_EMS_NO VARCHAR2(6) not null,
EMS_APPR_NO VARCHAR2(20),
MODIFY_TIMES INT not null,
CURRENT_FLAG CHAR(1) default '1'
constraint CKC_CURRENT_FLAG_T_DEMO_H check (CURRENT_FLAG is null or ( CURRENT_FLAG in ('0','1') ))
);alter table T_DEMO_HEAD
add constraint PK_T_DEMO_HEAD primary key (EMS_HEAD_ID);自编号CORP_EMS_NO和变更次数MODIFY_TIMES是业务主键
现需通过触发器处理当前标记项CURRENT_FLAG的赋值问题,例如已有如下数据:
EMS_HEAD_ID CORP_EMS_NO MODIFY_TIMES CURRENT_FLAG
080501 080501 1 0
080502 080501 2 0
080503 080501 3 0
080504 080501 4 1
现需删除EMS_HEAD_ID =’080504’即 MODIFY_TIMES=4这条记录,通过触发器自动将变更次数3的CURRENT_FLAG值改为1,即删除第N次的记录,自动将前一次(N-1)的CURRENT_FLAG值改为1;
我建立了存储过程:
create or replace procedure PROC_DEMO_CURRENT_FLAG(OLD_CORP_EMS_NO varchar2,OLD_MODIFYTIMES integer)
IS
begin
declare
I_temp integer;
V_OLD_CORP_EMS_NO varchar2(6);
I_OLD_MODIFYTIMES integer;
begin
V_OLD_CORP_EMS_NO := OLD_CORP_EMS_NO;
I_OLD_MODIFYTIMES := OLD_MODIFYTIMES - 1;
select count(*) into I_temp from T_DEMO_HEAD
where T_DEMO_HEAD.CORP_EMS_NO=V_OLD_CORP_EMS_NO and T_DEMO_HEAD.MODIFY_TIMES=I_OLD_MODIFYTIMES;
if I_temp=1 then
update T_DEMO_HEAD
set CURRENT_FLAG= '1'
where T_DEMO_HEAD.CORP_EMS_NO=V_OLD_CORP_EMS_NO and T_DEMO_HEAD.MODIFY_TIMES=I_OLD_MODIFYTIMES;
end if;
end;
exception
when NO_DATA_FOUND then
dbms_output.put_line('不能修改当前标记');
when others then
dbms_output.put_line('错误号'||SQLCODE);
end PROC_DEMO_CURRENT_FLAG;
/
建立了触发器
-- Before delete trigger "TDB_T_DEMO_HEAD" for table "T_DEMO_HEAD"
create trigger TDB_T_DEMO_HEAD before delete
on T_DEMO_HEAD for each row
declare
integrity_error exception;
errno integer;
errmsg char(200);
dummy integer;
found boolean;begin
/*以下处理删除时表头的当前标记*/
PROC_DEMO_CURRENT_FLAG(:old.CORP_EMS_NO,:old.MODIFY_TIMES);
-- Errors handling
exception
when integrity_error then
raise_application_error(errno, errmsg);
end;
/
可不起作用,请各位帮忙,谢谢。
/*==============================================================*/
/* Table: T_DEMO_HEAD */
/*==============================================================*/
create table T_DEMO_HEAD (
EMS_HEAD_ID CHAR(6) not null,
CORP_EMS_NO VARCHAR2(6) not null,
EMS_APPR_NO VARCHAR2(20),
MODIFY_TIMES INT not null,
CURRENT_FLAG CHAR(1) default '1'
constraint CKC_CURRENT_FLAG_T_DEMO_H check (CURRENT_FLAG is null or ( CURRENT_FLAG in ('0','1') ))
);alter table T_DEMO_HEAD
add constraint PK_T_DEMO_HEAD primary key (EMS_HEAD_ID);自编号CORP_EMS_NO和变更次数MODIFY_TIMES是业务主键
现需通过触发器处理当前标记项CURRENT_FLAG的赋值问题,例如已有如下数据:
EMS_HEAD_ID CORP_EMS_NO MODIFY_TIMES CURRENT_FLAG
080501 080501 1 0
080502 080501 2 0
080503 080501 3 0
080504 080501 4 1
现需删除EMS_HEAD_ID =’080504’即 MODIFY_TIMES=4这条记录,通过触发器自动将变更次数3的CURRENT_FLAG值改为1,即删除第N次的记录,自动将前一次(N-1)的CURRENT_FLAG值改为1;
我建立了存储过程:
create or replace procedure PROC_DEMO_CURRENT_FLAG(OLD_CORP_EMS_NO varchar2,OLD_MODIFYTIMES integer)
IS
begin
declare
I_temp integer;
V_OLD_CORP_EMS_NO varchar2(6);
I_OLD_MODIFYTIMES integer;
begin
V_OLD_CORP_EMS_NO := OLD_CORP_EMS_NO;
I_OLD_MODIFYTIMES := OLD_MODIFYTIMES - 1;
select count(*) into I_temp from T_DEMO_HEAD
where T_DEMO_HEAD.CORP_EMS_NO=V_OLD_CORP_EMS_NO and T_DEMO_HEAD.MODIFY_TIMES=I_OLD_MODIFYTIMES;
if I_temp=1 then
update T_DEMO_HEAD
set CURRENT_FLAG= '1'
where T_DEMO_HEAD.CORP_EMS_NO=V_OLD_CORP_EMS_NO and T_DEMO_HEAD.MODIFY_TIMES=I_OLD_MODIFYTIMES;
end if;
end;
exception
when NO_DATA_FOUND then
dbms_output.put_line('不能修改当前标记');
when others then
dbms_output.put_line('错误号'||SQLCODE);
end PROC_DEMO_CURRENT_FLAG;
/
建立了触发器
-- Before delete trigger "TDB_T_DEMO_HEAD" for table "T_DEMO_HEAD"
create trigger TDB_T_DEMO_HEAD before delete
on T_DEMO_HEAD for each row
declare
integrity_error exception;
errno integer;
errmsg char(200);
dummy integer;
found boolean;begin
/*以下处理删除时表头的当前标记*/
PROC_DEMO_CURRENT_FLAG(:old.CORP_EMS_NO,:old.MODIFY_TIMES);
-- Errors handling
exception
when integrity_error then
raise_application_error(errno, errmsg);
end;
/
可不起作用,请各位帮忙,谢谢。
建立了存储过程:
create or replace procedure PROC_DEMO_I_CURRENT_FLAG(NEW_CORP_EMS_NO varchar2,NEW_MODIFYTIMES integer) as
begin declare
I_temp integer;
V_NEW_CORP_EMS_NO varchar2(6);
I_NEW_MODIFYTIMES integer; begin
V_NEW_CORP_EMS_NO := NEW_CORP_EMS_NO;
I_NEW_MODIFYTIMES := NEW_MODIFYTIMES;
select count(*) into I_temp from T_DEMO_HEAD
where T_DEMO_HEAD.CORP_EMS_NO=V_NEW_CORP_EMS_NO and T_DEMO_HEAD.MODIFY_TIMES<I_NEW_MODIFYTIMES;
if I_temp=1 then
update T_DEMO_HEAD
set CURRENT_FLAG= '0'
where T_DEMO_HEAD.CORP_EMS_NO=V_NEW_CORP_EMS_NO and T_DEMO_HEAD.MODIFY_TIMES<I_NEW_MODIFYTIMES;
end if;
end; exception
when NO_DATA_FOUND then
dbms_output.put_line('不能修改当前标记');
when others then
dbms_output.put_line('错误号'||SQLCODE);
end PROC_DEMO_I_CURRENT_FLAG;
/
建立了触发器 :
-- Before insert trigger "TIB_T_DEMO_HEAD" for table "T_DEMO_HEAD"
create trigger TIB_T_DEMO_HEAD before insert
on T_DEMO_HEAD for each row
declare
integrity_error exception;
errno integer;
errmsg char(200);
dummy integer;
found boolean;begin
/*以下处理新增时表头的当前标记,注意declare I_temp integer;*/ PROC_DEMO_I_CURRENT_FLAG(:NEW.CORP_EMS_NO,:NEW.MODIFY_TIMES);-- Errors handling
exception
when integrity_error then
raise_application_error(errno, errmsg);
end;
/
你调用的过程里面为什么连个COMMIT也没有啊
这个更新容易把表锁住