触发器 的常用

CREATE OR REPLACE TRIGGER trg_base_authority_insert
before insert ON T_BASE_AUTHORITY
for each row
declare
integrity_error exception;
errno integer;
errmsg char(200);

begin
if :new.A_ID is null or :new.A_ID = 0 then
select SEQUENCE_AUTHORITY.NEXTVAL INTO :new.A_ID from dual;
insert into t_base_authorityrel(r_id, a_id)
values('1', :new.A_ID);
end if;
exception
when integrity_error then
raise_application_error(errno, errmsg);
end;

-----------------------------------------------------------------------

CREATE OR REPLACE TRIGGER Tri_wms_MONTH_DETAIL_back
after insert or delete or update of ORDER_FREIGHT on t_wms_month_detail
for each row
--作用:产生月结明细后对账单数据进行回写 成已产生月结，及月结编号
-- add by lkx 2018/03/17
declare
-- local variables here
begin
if inserting then
update t_wms_account_list a
set a.bl_month = 1,
a.account_code = :new.account_code,
a.modifier = :new.CREATE_MAN,
a.modifier_code = :new.CREATE_MAN_code,
a.modify_site = :new.CREATE_site,
a.modify_site_code = :new.CREATE_site_code,
a.modify_date = :new.CREATE_date
where a.order_bill = :new.order_bill
and ORDER_TYPE = :new.ORDER_TYPE;

update t_wms_month_detail_temp b
set b.bl_month = 1,b.to_month_date = sysdate
where b.order_bill = :new.order_bill
and ORDER_TYPE = :new.ORDER_TYPE;
end if;

if updating then
if nvl(:new.ORDER_FREIGHT, 0) <> nvl(:old.ORDER_FREIGHT, 0) then
update t_wms_month b
set b.money = nvl(b.money, 0) +(nvl(:new.ORDER_FREIGHT, 0) - nvl(:old.ORDER_FREIGHT, 0))
where b.account_code = :new.account_code;
end if;
end if;
if deleting then
update t_wms_account_list a
set a.bl_month = 0,
a.account_code = null,
a.modifier = '系统',
a.modifier_code = '删除回写',
a.modify_site = '系统',
a.modify_site_code = '系统',
a.modify_date = sysdate
where a.order_bill = :old.order_bill
and ORDER_TYPE = :old.ORDER_TYPE;

update t_wms_month_detail_temp b
set b.bl_month = 0, b.to_month_date = null
where b.order_bill = :old.order_bill
and ORDER_TYPE = :old.ORDER_TYPE;
end if;
end Tri_wms_MONTH_DETAIL_back;

 

 

 

 

---------------------------循环--------------------------------------------------------

CREATE OR REPLACE PROCEDURE p_minus_stock_quantity(vOrderBill in varchar2) is
--此储存过程用于 出库订单减少库存
begin
declare
cursor var_orderGoods is
select * from t_wms_order_detail a where a.order_bill = vOrderBill;
row_dept var_orderGoods%rowtype; --指定行指针

v_goodsQuantity number(10);
begin
for row_dept in var_orderGoods loop
--获取出库商品库存
select x.quantity
into v_goodsQuantity
from dual
left join (select nvl(a.quantity, 0) quantity
from t_wms_stock_goods a
where a.owner_site_code = row_dept.send_site_code
and a.store_locat_code = row_dept.store_locat_code
and a.goods_code = row_dept.Goods_code) x
on 1 = 1;

--出库后减少库存
if v_goodsQuantity < nvl(row_dept.QUANITY, 0) then
raise_application_error(-20018,
'订单号"' || vOrderBill || '"中商品条码【' ||
row_dept.Goods_code || '】库存不足出库，请补货后再操作！');
else
update t_wms_stock_goods a
set a.quantity = a.quantity - nvl(row_dept.QUANITY, 0)
where a.owner_site_code = row_dept.send_site_code
and a.store_locat_code = row_dept.store_locat_code
and a.goods_code = row_dept.Goods_code;
end if;

end loop;
end;
end;

-----------------------------错误提示------------------------------------
CREATE OR REPLACE TRIGGER trg_base_authority_insert
before insert ON T_BASE_AUTHORITY
for each row
declare
integrity_error exception;
errno integer;
errmsg char(200);

begin
if :new.A_ID is null or :new.A_ID = 0 then
select SEQUENCE_AUTHORITY.NEXTVAL INTO :new.A_ID from dual;
insert into t_base_authorityrel(r_id, a_id)
values('1', :new.A_ID);
end if;
exception
when integrity_error then
raise_application_error(errno, errmsg);
end;

--------------控制表只能添加不能修改、删除-----------
CREATE OR REPLACE TRIGGER Tri_ora_ddl_log_CONTROL
--控制表只能添加不能修改、删除
before update or delete ON tab_ora_ddl_log
for each row
declare
-- local variables here
begin
raise_application_error(-20002, '系统日志表数据不允许修改或删除！');
end Tri_ora_ddl_log_CONTROL;

----------------正则表达式查找";"的个数-------------------------
CREATE OR REPLACE TRIGGER trg_quote_dispArea_to_list
before insert or delete or update of modify_date on TAB_QUOTE_DISP_AREA
for each row
declare
integrity_error exception;
errno integer;
errmsg char(200);
begin

if inserting then
begin
IF :new.DISP_AREA_ID IS NULL OR :new.DISP_AREA_ID=0 THEN
select SEQUENCE_DISP_AREA.NEXTVAL INTO :new.DISP_AREA_ID from dual;
END IF;
exception
when integrity_error then raise_application_error(errno, errmsg);
end;

if :new.disp_area_site is not null then
insert into TAB_QUOTE_disp_LIST
(guid,disp_area_id ,use_site ,use_site_code ,fee_type ,disp_site ,disp_site_code ,modify_date)
select sys_guid(),:new.disp_area_id,:new.use_site,:new.use_site_code,:new.fee_type,
substr(disp_area_site,
instr(disp_area_site, ';', 1, rownum) + 1,
instr(disp_area_site, ';', 1, rownum + 1) -
instr(disp_area_site, ';', 1, rownum) - 1),0,sysdate
from (select :new.disp_area_site as disp_area_site from dual)
--正则表达式查找";"的个数
connect by rownum < length(regexp_replace(disp_area_site, '[^;]',''));
--函数替换（数据量小）
--connect by rownum < length(translate(disp_area_site,';' || disp_area_site,';'));
end if;
elsif deleting then
if :old.disp_area_site is not null then
delete from TAB_QUOTE_disp_LIST where disp_area_id = :old.disp_area_id;
end if;
else
if nvl(:old.modify_date,to_date('1899-1-1 00:00:00', 'yyyy-mm-dd hh24:mi:ss')) <>
nvl(:new.modify_date,to_date('1899-1-1 00:00:00', 'yyyy-mm-dd hh24:mi:ss')) then

if :old.disp_area_site is not null then
delete from TAB_QUOTE_disp_LIST where disp_area_id = :old.disp_area_id;
end if;
if :new.disp_area_site is not null then
insert into TAB_QUOTE_disp_LIST
(guid,disp_area_id ,use_site ,use_site_code ,fee_type ,disp_site ,disp_site_code ,modify_date)
select sys_guid(),:new.disp_area_id,:new.use_site,:new.use_site_code,:new.fee_type,
substr(disp_area_site,
instr(disp_area_site, ';', 1, rownum) + 1,
instr(disp_area_site, ';', 1, rownum + 1) -
instr(disp_area_site, ';', 1, rownum) - 1),0,sysdate
from (select :new.disp_area_site as disp_area_site from dual)
connect by rownum < length(translate(disp_area_site,';' || disp_area_site,';'));
end if;

end if;
end if;
end trg_quote_dispArea_to_list;

 

 

----------------插入到多张表-------------

CREATE OR REPLACE TRIGGER MODIFY_TAB_APPLY
--for table "TAB_apply" modify record on 2012-12-29 by lzw
after update of SUM_MONEY,APPLY_COUNT ON SUTENG.TAB_APPLY
for each row
declare
-- local variables here
begin

if nvl(:new.SUM_MONEY,0.001)<>nvl(:old.SUM_MONEY,0.001) then
insert into TAB_MODIFY values(sys_guid(),:new.APPLY_NAME,sysdate,'修改','物料金额',to_char(:old.SUM_MONEY),
to_char(:new.SUM_MONEY),nvl(:new.AUDITING_MAN,'空'),' ',' ',:new.rowid,'物料申请表');
end if;
if nvl(:new.APPLY_COUNT,0.001)<>nvl(:old.APPLY_COUNT,0.001) then
insert into TAB_MODIFY values(sys_guid(),:new.APPLY_NAME,sysdate,'修改','申请数量',to_char(:old.APPLY_COUNT),
to_char(:new.APPLY_COUNT),nvl(:new.AUDITING_MAN,'空'),' ',' ',:new.rowid,'物料申请表');
end if;

end MODIFY_TAB_APPLY;