before insert or update of code on bigguest_flycompany
for each row
declare
-- local variables here
iupdatorid varchar2(20);irequestip varchar2(20);
Cursor companyinfo is select c.request_ip,c.updator_id from company c where c.cm_id=:new.BIG_ID;
begin
for ci in companyinfo LOOP
begin
iupdatorid:=ci.updator_id;
irequestip:=ci.request_ip;
end;
end LOOP;
if (:new.code<>:old.code) or (:new.code is null and :old.code is not null) or (:new.code is not null and :old.code is null) then
insert into sys_log values(me_sequence.nextval,:new.BIG_ID,1,iupdatorid,sysdate,'第三方协议','此为插入','航空公司ID:'||:new.fly_id||'协议码:'||:new.code,sys_context('userenv','ip_address'),'bigguest_flycompany',irequestip);
end if;
end TR_bigguest_flycompany;
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
create or replace trigger TR_COMPANY
before /**insert or**/
update or delete of name, SUBCOMPANY, USERNAME, BUSINESSLINE, CUSTOMERCATEGORY, TMCBUSINESSINFOS, TMCBUSINESSSCOPE, TMCBUSINESSNATURE, LIMIT, LXR, CONTACTPHONE, lxrdh, PAY_DAY, TYPE, address, SSGJ, PROVINCE, CITY, remark, shortName, zjhm, phone, THIRD_STATUS, SALES_MAN, status, www, CUSTOMERLEVEL, customerManager, salesManager on company
for each row
declare
begin
begin
if updating then
if (:new.status = 3 or :new.status = 0 or :new.status = 4 or
:new.status = 5) then
--当状态是 删除 禁用 激活 未审核 的时候不同步 待启用(0)的时候同步
return;
end if;
--修改公司名称(往异动日志表表插入数据)
if (:new.name <> :old.name) or
(:new.name is null and :old.name is not null) or
(:new.name is not null and :old.name is null) then
insert into sys_log
values
(me_sequence.nextval,
:new.cm_id,
1,
:new.updator_id,
sysdate,
'公司名',
:old.name,
:new.name,
sys_context('userenv', 'ip_address'),
'company',
:new.request_ip);
end if;
--判断修改备注 (往异动日志表表插入数据)
if (:new.remark <> :old.remark) or
(:new.remark is null and :old.remark is not null) or
(:new.remark is not null and :old.remark is null) then
insert into sys_log
values
(me_sequence.nextval,
:new.cm_id,
1,
:new.updator_id,
sysdate,
'备注',
:old.remark,
:new.remark,
sys_context('userenv', 'ip_address'),
'company',
:new.request_ip);
end if;
--判断修改简称 (往异动日志表表插入数据)
if (:new.shortName <> :old.shortName) or
(:new.shortName is null and :old.shortName is not null) or
(:new.shortName is not null and :old.shortName is null) then
insert into sys_log
values
(me_sequence.nextval,
:new.cm_id,
1,
:new.updator_id,
sysdate,
'公司简称',
:old.shortName,
:new.shortName,
sys_context('userenv', 'ip_address'),
'company',
:new.request_ip);
end if;
--判断修改联系人(往异动日志表表插入数据)
if (:new.lxr <> :old.lxr) or
(:new.lxr is null and :old.lxr is not null) or
(:new.lxr is not null and :old.lxr is null) then
insert into sys_log
values
(me_sequence.nextval,
:new.cm_id,
1,
:new.updator_id,
sysdate,
'公司联系人',
:old.lxr,
:new.lxr,
sys_context('userenv', 'ip_address'),
'company',
:new.request_ip);
end if;
--判断修改联系电话(往异动日志表表插入数据)
if (:new.lxrdh <> :old.lxrdh) or
(:new.lxrdh is null and :old.lxrdh is not null) or
(:new.lxrdh is not null and :old.lxrdh is null) then
insert into sys_log
values
(me_sequence.nextval,
:new.cm_id,
1,
:new.updator_id,
sysdate,
'联系人手机',
:old.lxrdh,
:new.lxrdh,
sys_context('userenv', 'ip_address'),
'company',
:new.request_ip);
end if;
--判断修改证件号码(往异动日志表表插入数据)
if (:new.zjhm <> :old.zjhm) or
(:new.zjhm is null and :old.zjhm is not null) or
(:new.zjhm is not null and :old.zjhm is null) then
insert into sys_log
values
(me_sequence.nextval,
:new.cm_id,
1,
:new.updator_id,
sysdate,
'证件号码',
:old.zjhm,
:new.zjhm,
sys_context('userenv', 'ip_address'),
'company',
:new.request_ip);
end if;
--判断修改公司地址(往异动日志表表插入数据)
if (:new.COMPANYADDRESS <> :old.COMPANYADDRESS) or
(:new.COMPANYADDRESS is null and :old.COMPANYADDRESS is not null) or
(:new.COMPANYADDRESS is not null and :old.COMPANYADDRESS is null) then
insert into sys_log
values
(me_sequence.nextval,
:new.cm_id,
1,
:new.updator_id,
sysdate,
'公司地址',
:old.COMPANYADDRESS,
:new.COMPANYADDRESS,
sys_context('userenv', 'ip_address'),
'company',
:new.request_ip);
end if;
--修改发票配送地址
if (:new.address <> :old.address) or
(:new.address is null and :old.address is not null) or
(:new.address is not null and :old.address is null) then
insert into sys_log
values
(me_sequence.nextval,
:new.cm_id,
1,
:new.updator_id,
sysdate,
'发票配送地址',
:old.address,
:new.address,
sys_context('userenv', 'ip_address'),
'company',
:new.request_ip);
end if;
if (:new.phone <> :old.phone) or
(:new.phone is null and :old.phone is not null) or
(:new.phone is not null and :old.phone is null) then
insert into sys_log
values
(me_sequence.nextval,
:new.cm_id,
1,
:new.updator_id,
sysdate,
'公司电话',
:old.phone,
:new.phone,
sys_context('userenv', 'ip_address'),
'company',
:new.request_ip);
end if;
if (:new.THIRD_STATUS <> :old.THIRD_STATUS) or
(:new.THIRD_STATUS is null and :old.THIRD_STATUS is not null) or
(:new.THIRD_STATUS is not null and :old.THIRD_STATUS is null) then
insert into sys_log
values
(me_sequence.nextval,
:new.cm_id,
1,
:new.updator_id,
sysdate,
'是否第三方协议',
:old.THIRD_STATUS,
:new.THIRD_STATUS,
sys_context('userenv', 'ip_address'),
'company',
:new.request_ip);
end if;
if (:new.SALES_MAN <> :old.SALES_MAN) or
(:new.SALES_MAN is null and :old.SALES_MAN is not null) or
(:new.SALES_MAN is not null and :old.SALES_MAN is null) then
insert into sys_log
values
(me_sequence.nextval,
:new.cm_id,
1,
:new.updator_id,
sysdate,
'业务员',
:old.SALES_MAN,
:new.SALES_MAN,
sys_context('userenv', 'ip_address'),
'company',
:new.request_ip);
end if;
if (:new.www <> :old.www) or
(:new.www is null and :old.www is not null) or
(:new.www is not null and :old.www is null) then
insert into sys_log
values
(me_sequence.nextval,
:new.cm_id,
1,
:new.updator_id,
sysdate,
'公司网址',
:old.www,
:new.www,
sys_context('userenv', 'ip_address'),
'company',
:new.request_ip);
end if;
if (:new.status <> :old.status) or
(:new.status is null and :old.status is not null) or
(:new.status is not null and :old.status is null) then
insert into sys_log
values
(me_sequence.nextval,
:new.cm_id,
1,
:new.updator_id,
sysdate,
'状态',
:old.status,
:new.status,
sys_context('userenv', 'ip_address'),
'company',
:new.request_ip);
end if;
if (:new.PAY_DAY <> :old.PAY_DAY) or
(:new.PAY_DAY is null and :old.PAY_DAY is not null) or
(:new.PAY_DAY is not null and :old.PAY_DAY is null) then
insert into sys_log
values
(me_sequence.nextval,
:new.cm_id,
1,
:new.updator_id,
sysdate,
'付款条件',
(select p.name from payday p where p.id = :old.PAY_DAY),
(select p.name from payday p where p.id = :new.PAY_DAY),
sys_context('userenv', 'ip_address'),
'company',
:new.request_ip);
end if;
elsif deleting then
update SUPPLIER s set s.name = 'vv' where s.id = 1136;
end if;
EXCEPTION
WHEN NO_DATA_FOUND THEN
update company c
set c.erpzt = 1, c.erpmessage = '未找到'
where c.CM_ID = :new.CM_ID;
WHEN OTHERS THEN
update company c
set c.erpzt = 1, c.erpmessage = '异常'
where c.CM_ID = :new.CM_ID;
end;
end TR_COMPANY;