日期:2014-05-17 浏览次数:20951 次
CREATE OR REPLACE TRIGGER TRG_BCPBS_AGENT_INFO --代理机构信息表 类型是6 招标代理机构
AFTER INSERT OR UPDATE ON bcpbs.t_agent_info
FOR EACH ROW
DECLARE
var_inc_oldid NUMBER(11); --企业id
var_inc_id NUMBER(11); --企业id
var_org_no VARCHAR2(32);
BEGIN
--转化代理机构
var_org_no:=bcpbs.fun_enterprise_organization_no(:NEW.organization_no);
---根据组织机构代码查询bics企业表中是否存在代理机构信息
SELECT id
INTO var_inc_oldid
FROM bics.t_inc_info
WHERE encode = var_org_no
AND inc_type = 6;
DELETE FROM bics.t_inc_info WHERE id = var_inc_oldid;
IF var_inc_oldid IS NOT NULL THEN --如果存在,删除该条记录
var_inc_id := var_inc_oldid;
ELSE
var_inc_id :=bics.seq_inc.nextval;
END IF;
---向企业表插入一条代理机构数据
INSERT INTO bics.t_inc_info
(is_link,
phase,
success,
encode,
inc_name,
inc_type,
license_code,
leager_alone,
legal,
link_name,
link_call,
link_certno,
id,
symbol,
register_address,
info_source,
system_code,
operate_op,
operate_name,
operate_date,
subjection
)
VALUES
(
9,
3,
1,
var_org_no,
:NEW.agent_name,
6,
:NEW.agent_certificate,
1,
:NEW.representative_name,
:NEW.linkman,
:NEW.linkman_phone,
:NEW.linkman_identityno,
var_inc_id,
decode(:NEW.status,0,1,1,9),
:NEW.register_address,
decode(:NEW.info_source, 0, 2, 1, 1, 9),
12,
0,
'BCPBS数据同步',
sysdate,
decode(:NEW.subjection, 0, 1, 1, 2, 2, 3, 3, 4, 1)
);
END;