create or replace trigger T_A_Update_pk_Par_seat
after insert of seat_code,et_code on par_seat
for each row
/*================================================= */
/* 功能描述:完成数据库完整性约束 */
/* 时间: 2002/3/29 */
/* 创建人: 王万里 */
/* 方法:根据外键约束修改par_seat、con_data_file、 */
/* par_commision */
/*===================================================*/
declare
v_Exist int; --判断是不是存在接收文件
v_source_file varchar2(12); --接收文件名字
v_seat_code_len int; --席位代码长度
begin
--处理击联删除
if deleting then
delete from con_data_file where seat_code=:old.seat_code and et_code=:old.et_code;
delete from par_commision where seat_code=:old.seat_code and et_code=:old.et_code;
--处理击联update
elsif updating then
update con_data_file set seat_code=:new.seat_code,et_code=:new.et_code where seat_code=:old.seat_code and et_code=:old.et_code;
update par_commision set seat_code=:new.seat_code,et_code=:new.et_code where seat_code=:old.seat_code and et_code=:old.et_code;
--处理击联insert
elsif inserting then
begin
if trim(:new.et_code)='1' then
begin
--判断是不是已经有深圳的接收文件
select count(*) into v_Exist from con_data_file where fund_code=:new.fund_code and trim(et_code)='1';
if v_Exist=0 then
begin
insert into con_data_file(source_file,fund_code,seat_code,et_code,file_kind)
values('c:\data\sjshb.dbf',:new.fund_code,:new.seat_code,:new.et_code,'3');
insert into con_data_file(source_file,fund_code,seat_code,et_code,file_kind)
values('c:\data\sjstj.dbf',:new.fund_code,:new.seat_code,:new.et_code,'5');
insert into con_data_file(source_file,fund_code,seat_code,et_code,file_kind)
values('c:\data\sjshq.dbf',:new.fund_code,:new.seat_code,:new.et_code,'1');
insert into con_data_file(source_file,fund_code,seat_code,et_code,file_kind)
values('c:\data\sjsXX.dbf',:new.fund_code,:new.seat_code,:new.et_code,'1');
end;
end if;
end;
else
--插入深圳接收文件
/*======================================================== */
/* 根据席位代码的长度设计接收文件 */
/* 如果席位代码长度是3,然后就设计其接收文件是Xx000||席位代码*/
/*==========================================================*/
begin
v_seat_code_len:=length(:new.seat_code);
v_Exist:=0;
insert into con_data_file(source_file,fund_code,seat_code,et_code,file_kind)
values('c:\data\'||'Gh'||decode(v_seat_code_len,1,'0000'||:new.seat_code,2,'000'||:new.seat_code,'3','00'||:new.seat_code,4,'0'||:new.seat_code,:new.seat_code)||'.dbf',:new.fund_code,:new.seat_code,:new.et_code,'2');
insert into con_data_file(source_file,fund_code,seat_code,et_code,file_kind)
values('c:\data\'||'QS'||decode(v_seat_code_len,1,'0000'||:new.seat_code,2,'000'||:new.seat_code,'3','00'||:new.seat_code,4,'0'||:new.seat_code,:new.seat_code)||'.dbf',:new.fund_code,:new.seat_code,:new.et_code,'4');
select count(*) into v_Exist from con_data_file where fund_code=:new.fund_code and upper(trim(source_file))=upper('show128.dbf'); --判断行情文件是不是存在
if v_Exist=0 then
begin
insert into con_data_file(source_file,fund_code,seat_code,et_code,file_kind)
values('c:\data\Show128.dbf',:new.fund_code,:new.seat_code,:new.et_code,'0');
end;
end if;
end;
end if;
end;
end if;
end T_Update_pk_Par_seat;
包错说:此类触发器的列表无效
after insert of seat_code,et_code on par_seat
for each row
/*================================================= */
/* 功能描述:完成数据库完整性约束 */
/* 时间: 2002/3/29 */
/* 创建人: 王万里 */
/* 方法:根据外键约束修改par_seat、con_data_file、 */
/* par_commision */
/*===================================================*/
declare
v_Exist int; --判断是不是存在接收文件
v_source_file varchar2(12); --接收文件名字
v_seat_code_len int; --席位代码长度
begin
--处理击联删除
if deleting then
delete from con_data_file where seat_code=:old.seat_code and et_code=:old.et_code;
delete from par_commision where seat_code=:old.seat_code and et_code=:old.et_code;
--处理击联update
elsif updating then
update con_data_file set seat_code=:new.seat_code,et_code=:new.et_code where seat_code=:old.seat_code and et_code=:old.et_code;
update par_commision set seat_code=:new.seat_code,et_code=:new.et_code where seat_code=:old.seat_code and et_code=:old.et_code;
--处理击联insert
elsif inserting then
begin
if trim(:new.et_code)='1' then
begin
--判断是不是已经有深圳的接收文件
select count(*) into v_Exist from con_data_file where fund_code=:new.fund_code and trim(et_code)='1';
if v_Exist=0 then
begin
insert into con_data_file(source_file,fund_code,seat_code,et_code,file_kind)
values('c:\data\sjshb.dbf',:new.fund_code,:new.seat_code,:new.et_code,'3');
insert into con_data_file(source_file,fund_code,seat_code,et_code,file_kind)
values('c:\data\sjstj.dbf',:new.fund_code,:new.seat_code,:new.et_code,'5');
insert into con_data_file(source_file,fund_code,seat_code,et_code,file_kind)
values('c:\data\sjshq.dbf',:new.fund_code,:new.seat_code,:new.et_code,'1');
insert into con_data_file(source_file,fund_code,seat_code,et_code,file_kind)
values('c:\data\sjsXX.dbf',:new.fund_code,:new.seat_code,:new.et_code,'1');
end;
end if;
end;
else
--插入深圳接收文件
/*======================================================== */
/* 根据席位代码的长度设计接收文件 */
/* 如果席位代码长度是3,然后就设计其接收文件是Xx000||席位代码*/
/*==========================================================*/
begin
v_seat_code_len:=length(:new.seat_code);
v_Exist:=0;
insert into con_data_file(source_file,fund_code,seat_code,et_code,file_kind)
values('c:\data\'||'Gh'||decode(v_seat_code_len,1,'0000'||:new.seat_code,2,'000'||:new.seat_code,'3','00'||:new.seat_code,4,'0'||:new.seat_code,:new.seat_code)||'.dbf',:new.fund_code,:new.seat_code,:new.et_code,'2');
insert into con_data_file(source_file,fund_code,seat_code,et_code,file_kind)
values('c:\data\'||'QS'||decode(v_seat_code_len,1,'0000'||:new.seat_code,2,'000'||:new.seat_code,'3','00'||:new.seat_code,4,'0'||:new.seat_code,:new.seat_code)||'.dbf',:new.fund_code,:new.seat_code,:new.et_code,'4');
select count(*) into v_Exist from con_data_file where fund_code=:new.fund_code and upper(trim(source_file))=upper('show128.dbf'); --判断行情文件是不是存在
if v_Exist=0 then
begin
insert into con_data_file(source_file,fund_code,seat_code,et_code,file_kind)
values('c:\data\Show128.dbf',:new.fund_code,:new.seat_code,:new.et_code,'0');
end;
end if;
end;
end if;
end;
end if;
end T_Update_pk_Par_seat;
包错说:此类触发器的列表无效
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货