触发器 调用存储过程 实例:

CREATE OR REPLACE TRIGGER TAB_AMERCE_balance_new
before insert or delete or update of BL_AUDITED ON TAB_AMERCE for each row
declare
-- local variables here
FCount_pre number(1); --网点帐户是否开启
FCount_Next number(1); --网点帐户是否开启
IsSecend_pre number(1); --是否是二级网点
IsSecend_Next number(1); --是否是二级网点
Secend_precenter varchar(30); --二级网点所属中心
Secend_Nextcenter varchar(30); --二级网点所属中心
begin
if inserting then
if nvl(:new.BL_AUDITED, 0) = 1 then
select count(*)
into FCount_pre
from dual a
where exists (select /*+index(b TAB_BALANCE_ACCOUNT_I)*/
1
from tab_balance_account b
where b.center_name = :new.AMERCE_CENTER
and b.site_name = nvl(:new.SITE_NAME, '*')
and b.bl_open = 1
and b.start_date <= :new.OPT_DATE);
select count(*)
into FCount_Next
from dual a
where exists (select /*+index(b TAB_BALANCE_ACCOUNT_I)*/
1
from tab_balance_account b
where b.center_name = :new.reward_center
and b.site_name = nvl(:new.REWARD_NAME, '*')
and b.bl_open = 1
and b.start_date <= :new.OPT_DATE);
select count(*)
into IsSecend_pre
from dual a
where exists (select 1
from tab_site s
where s.site_name = :new.SITE_NAME
and s.type = '二级网点');

select count(*)
into IsSecend_next
from dual a
where exists (select 1
from tab_site s
where s.site_name = :new.reward_name
and s.type = '二级网点');

if FCount_pre > 0 then
if (nvl(:new.SITE_NAME, '*') <> '*') and
(nvl(:new.AMERCE_MONEY, 0) <> 0) then
tab_balance_detail_insert('罚款',
substr(:new.AMERCE_REASON, 1, 200),
substr(:new.BILL_CODE, 1, 20),
- :new.AMERCE_MONEY,
- :new.AMERCE_MONEY,
:new.OPT_DATE,
:new.AMERCE_CENTER,
:new.SITE_NAME,
'系统',
'系统',
'系统');
if IsSecend_pre > 0 and nvl(:new.AMERCE_REASON, '*') <> '代录单费用' then
select SUPERIOR_FINANCE_CENTER
into Secend_precenter
from tab_site s
where s.site_name = :new.AMERCE_CENTER;
tab_balance_detail_insert('罚款',
substr(:new.AMERCE_REASON, 1, 200),
substr(:new.BILL_CODE, 1, 20),
- :new.AMERCE_MONEY,
- :new.AMERCE_MONEY,
:new.OPT_DATE,
Secend_precenter,
:new.AMERCE_CENTER,
'系统',
'系统',
'系统');
end if;

if :new.OPT_site = '总部' then
if nvl(Secend_precenter, '*') <> '*' then
tab_balance_detail_insert('罚款',
substr(:new.AMERCE_REASON, 1, 200),
substr(:new.BILL_CODE, 1, 20),
- :new.AMERCE_MONEY,
- :new.AMERCE_MONEY,
:new.OPT_DATE,
'总部',
Secend_precenter,
'系统',
'系统',
'系统');
else
tab_balance_detail_insert('罚款',
substr(:new.AMERCE_REASON, 1, 200),
substr(:new.BILL_CODE, 1, 20),
- :new.AMERCE_MONEY,
- :new.AMERCE_MONEY,
:new.OPT_DATE,
'总部',
:new.AMERCE_CENTER,
'系统',
'系统',
'系统');
end if;
end if;
end if;

end if;
if FCount_Next > 0 then
if (nvl(:new.reward_name, '*') <> '*') and
(nvl(:new.reward_money, 0) <> 0) then
tab_balance_detail_insert('罚款',
substr(:new.AMERCE_REASON, 1, 200),
substr(:new.BILL_CODE, 1, 20),
:new.reward_money,
:new.reward_money,
:new.OPT_DATE,
:new.reward_center,
:new.reward_name,
'系统',
'系统',
'系统');
if IsSecend_next > 0 then
select SUPERIOR_FINANCE_CENTER
into Secend_Nextcenter
from tab_site s
where s.site_name = :new.reward_center;
tab_balance_detail_insert('罚款',
substr(:new.AMERCE_REASON, 1, 200),
substr(:new.BILL_CODE, 1, 20),
:new.reward_money,
:new.reward_money,
:new.OPT_DATE,
Secend_Nextcenter,
:new.reward_center,
'系统',
'系统',
'系统');
end if;
if :new.OPT_site = '总部' then
if nvl(Secend_Nextcenter, '*') <> '*' then
tab_balance_detail_insert('罚款',
substr(:new.AMERCE_REASON, 1, 200),
substr(:new.BILL_CODE, 1, 20),
:new.reward_money,
:new.reward_money,
:new.OPT_DATE,
'总部',
Secend_Nextcenter,
'系统',
'系统',
'系统');
else
tab_balance_detail_insert('罚款',
substr(:new.AMERCE_REASON, 1, 200),
substr(:new.BILL_CODE, 1, 20),
:new.reward_money,
:new.reward_money,
:new.OPT_DATE,
'总部',
:new.reward_center,
'系统',
'系统',
'系统');
end if;
end if;
end if;
end if;
end if;
elsif updating and nvl(:new.AMERCE_REASON, '*') <> '代录单费用' then
--先获取对应的帐户是否开启
select count(*)
into FCount_pre
from dual a
where exists (select /*+index(b TAB_BALANCE_ACCOUNT_I)*/
1
from tab_balance_account b
where b.center_name = :new.AMERCE_CENTER
and b.site_name = nvl(:new.SITE_NAME, '*')
and b.bl_open = 1
and b.start_date <= :new.OPT_DATE);
select count(*)
into FCount_Next
from dual a
where exists (select /*+index(b TAB_BALANCE_ACCOUNT_I)*/
1
from tab_balance_account b
where b.center_name = :new.reward_center
and b.site_name = nvl(:new.REWARD_NAME, '*')
and b.bl_open = 1
and b.start_date <= :new.OPT_DATE);

select count(*)
into IsSecend_pre
from dual a
where exists (select 1
from tab_site s
where s.site_name = :new.SITE_NAME
and s.type = '二级网点');

select count(*)
into IsSecend_next
from dual a
where exists (select 1
from tab_site s
where s.site_name = :new.reward_name
and s.type = '二级网点');

if nvl(:new.BL_AUDITED, 0) = 1 then
if nvl(:old.BL_AUDITED, 0) = 0 then
if FCount_pre > 0 then
if (nvl(:new.SITE_NAME, '*') <> '*') and
(nvl(:new.AMERCE_MONEY, 0) <> 0) then
tab_balance_detail_insert('罚款',
substr(:new.AMERCE_REASON, 1, 200),
substr(:new.BILL_CODE, 1, 20),
- :new.AMERCE_MONEY,
- :new.AMERCE_MONEY,
:new.OPT_DATE,
:new.AMERCE_CENTER,
:new.SITE_NAME,
:new.AUDIT_MAN,
:new.AUDIT_MAN,
:new.AUDIT_SITE);
if IsSecend_pre > 0 then
select SUPERIOR_FINANCE_CENTER
into Secend_precenter
from tab_site s
where s.site_name = :new.AMERCE_CENTER;
tab_balance_detail_insert('罚款',
substr(:new.AMERCE_REASON, 1, 200),
substr(:new.BILL_CODE, 1, 20),
- :new.AMERCE_MONEY,
- :new.AMERCE_MONEY,
:new.OPT_DATE,
Secend_precenter,
:new.AMERCE_CENTER,
:new.AUDIT_MAN,
:new.AUDIT_MAN,
:new.AUDIT_SITE);
end if;

if :new.OPT_site = '总部' then
if nvl(Secend_precenter, '*') <> '*' then
tab_balance_detail_insert('罚款',
substr(:new.AMERCE_REASON, 1, 200),
substr(:new.BILL_CODE, 1, 20),
- :new.AMERCE_MONEY,
- :new.AMERCE_MONEY,
:new.OPT_DATE,
'总部',
Secend_precenter,
:new.AUDIT_MAN,
:new.AUDIT_MAN,
:new.AUDIT_SITE);
else
tab_balance_detail_insert('罚款',
substr(:new.AMERCE_REASON, 1, 200),
substr(:new.BILL_CODE, 1, 20),
- :new.AMERCE_MONEY,
- :new.AMERCE_MONEY,
:new.OPT_DATE,
'总部',
:new.AMERCE_CENTER,
:new.AUDIT_MAN,
:new.AUDIT_MAN,
:new.AUDIT_SITE);
end if;
end if;
end if;
end if;
if FCount_Next > 0 then
if (nvl(:new.reward_name, '*') <> '*') and
(nvl(:new.reward_money, 0) <> 0) then
tab_balance_detail_insert('罚款',
substr(:new.AMERCE_REASON, 1, 200),
substr(:new.BILL_CODE, 1, 20),
:new.reward_money,
:new.reward_money,
:new.OPT_DATE,
:new.reward_center,
:new.reward_name,
:new.AUDIT_MAN,
:new.AUDIT_MAN,
:new.AUDIT_SITE);

if IsSecend_next > 0 then
select SUPERIOR_FINANCE_CENTER
into Secend_Nextcenter
from tab_site s
where s.site_name = :new.reward_center;
tab_balance_detail_insert('罚款',
substr(:new.AMERCE_REASON, 1, 200),
substr(:new.BILL_CODE, 1, 20),
:new.reward_money,
:new.reward_money,
:new.OPT_DATE,
Secend_Nextcenter,
:new.reward_center,
:new.AUDIT_MAN,
:new.AUDIT_MAN,
:new.AUDIT_SITE);
end if;
if :new.OPT_site = '总部' then
if nvl(Secend_Nextcenter, '*') <> '*' then
--- 调用存储过程 tab_balance_detail_insert
tab_balance_detail_insert('罚款',
substr(:new.AMERCE_REASON, 1, 200),
substr(:new.BILL_CODE, 1, 20),
:new.reward_money,
:new.reward_money,
:new.OPT_DATE,
'总部',
Secend_Nextcenter,
:new.AUDIT_MAN,
:new.AUDIT_MAN,
:new.AUDIT_SITE);
else
tab_balance_detail_insert('罚款',
substr(:new.AMERCE_REASON, 1, 200),
substr(:new.BILL_CODE, 1, 20),
:new.reward_money,
:new.reward_money,
:new.OPT_DATE,
'总部',
:new.reward_center,
:new.AUDIT_MAN,
:new.AUDIT_MAN,
:new.AUDIT_SITE);
end if;
end if;
end if;
end if;
end if;
end if;
elsif deleting and nvl(:old.AMERCE_REASON, '*') = '代录单费用' then
if nvl(:old.BL_AUDITED, 0) = 1 then
select count(*)
into FCount_pre
from dual a
where exists (select /*+index(b TAB_BALANCE_ACCOUNT_I)*/
1
from tab_balance_account b
where b.center_name = :old.AMERCE_CENTER
and b.site_name = nvl(:old.SITE_NAME, '*')
and b.bl_open = 1
and b.start_date <= :old.OPT_DATE);
select count(*)
into FCount_Next
from dual a
where exists (select /*+index(b TAB_BALANCE_ACCOUNT_I)*/
1
from tab_balance_account b
where b.center_name = :old.reward_center
and b.site_name = nvl(:old.REWARD_NAME, '*')
and b.bl_open = 1
and b.start_date <= :old.OPT_DATE);

select count(*)
into IsSecend_next
from dual a
where exists (select 1
from tab_site s
where s.site_name = :old.reward_name
and s.type = '二级网点');
if FCount_pre > 0 then
if (nvl(:old.SITE_NAME, '*') <> '*') and
(nvl(:old.AMERCE_MONEY, 0) <> 0) then
tab_balance_detail_insert('罚款',
substr(:old.AMERCE_REASON, 1, 50) ||
'【取消】',
substr(:old.BILL_CODE, 1, 20),
:old.AMERCE_MONEY,
:old.AMERCE_MONEY,
:old.OPT_DATE,
:old.AMERCE_CENTER,
:old.SITE_NAME,
'系统',
'系统',
'系统');

if :old.OPT_site = '总部' then

tab_balance_detail_insert('罚款',
substr(:old.AMERCE_REASON, 1, 50) ||
'【取消】',
substr(:old.BILL_CODE, 1, 20),
:old.AMERCE_MONEY,
:old.AMERCE_MONEY,
:old.OPT_DATE,
'总部',
:old.AMERCE_CENTER,
'系统',
'系统',
'系统');

end if;
end if;

end if;
if FCount_Next > 0 then
if (nvl(:old.reward_name, '*') <> '*') and
(nvl(:old.reward_money, 0) <> 0) then
tab_balance_detail_insert('罚款',
substr(:old.AMERCE_REASON, 1, 50) ||
'【取消】',
substr(:old.BILL_CODE, 1, 20),
- :old.reward_money,
- :old.reward_money,
:old.OPT_DATE,
:old.reward_center,
:old.reward_name,
'系统',
'系统',
'系统');
if IsSecend_next > 0 then
select SUPERIOR_FINANCE_CENTER
into Secend_Nextcenter
from tab_site s
where s.site_name = :old.reward_center;
tab_balance_detail_insert('罚款',
substr(:old.AMERCE_REASON, 1, 50) ||
'【取消】',
substr(:old.BILL_CODE, 1, 20),
:old.reward_money,
:old.reward_money,
:old.OPT_DATE,
Secend_Nextcenter,
:old.reward_center,
'系统',
'系统',
'系统');
end if;
if :old.OPT_site = '总部' then
if nvl(Secend_Nextcenter, '*') <> '*' then
tab_balance_detail_insert('罚款',
substr(:old.AMERCE_REASON, 1, 50) ||
'【取消】',
substr(:old.BILL_CODE, 1, 20),
:old.reward_money,
:old.reward_money,
:old.OPT_DATE,
'总部',
Secend_Nextcenter,
'系统',
'系统',
'系统');
else
tab_balance_detail_insert('罚款',
substr(:old.AMERCE_REASON, 1, 50) ||
'【取消】',
substr(:old.BILL_CODE, 1, 20),
- :old.reward_money,
- :old.reward_money,
:old.OPT_DATE,
'总部',
:old.reward_center,
'系统',
'系统',
'系统');
end if;
end if;
end if;
end if;
end if;

end if;
end TAB_AMERCE_balance_new;

-----add by sd

posted @ 2018-04-11 18:55  Debugs  阅读(4596)  评论(0)  编辑  收藏  举报