create or replace trigger triger_tb_corp_info
after insert or update ON tb_corp_info
for each row
begin
if inserting then
insert into TB_CORP_VIEW_GIS_TEMP (ID, NAME, PHONE, LONGITUDE, LATITUDE, CORP_ID_FOR_DEPTNAMES, COUNTRY, STREET, VILLAGE, COUNTRY_NAME, STREET_NAME, VILLAGE_NAME, MEMBERSHIP, MEMBERSHIP_NAME, IS_DELE, TYPE_ID, TYPE_NAME, TYPE1_ID, TYPE1_NAME, TYPE2_ID, TYPE2_NAME, TYPE_PIC_FLAG, STATUS, STATUS_NAME, SD_NAME, SD_TEL, CORP_GRADE, YH_NUM)
select distinct t.id,t.name,t.phone,t.longitude,t.latitude,t.id CORP_ID_FOR_DEPTNAMES,t.country,t.street,t.village,
c.name country_name,str.name street_name,v.name village_name,t.membership, m.name membership_name,t.is_dele,
t1.id type_id,t1.type_name,t2.id type1_id,t2.type_name type1_name,t3.id type2_id,t3.type_name type2_name,
case t1.type_name
when '烟花爆竹类' then 'yhbzl'
when '民爆企业类' then 'mbqyl'
when '煤矿类' then 'mkl'
when '卫生类' then 'wsl'
when '环卫类' then 'hwl'
when '质监类' then 'zjl2'
when '公安类' then 'gal'
when '消防类' then 'xfl'
when '工商类' then 'gsl'
when '住建类' then 'zjl1'
when '文体广电新闻出版类' then 'wtgdxwcbl'
when '冶金工贸类' then 'yjgml'
when '非煤矿山类' then 'fmksl'
when '园林类' then 'yll'
when '林业类' then 'lyl2'
when '旅游类' then 'lyl1'
when '农业类' then 'nyl'
when '农机类' then 'njl'
when '畜牧类' then 'xml'
when '水利类' then 'sll'
when '食药类' then 'syl'
when '危险化学品类' then 'wxhxpl'
when '交通运输类' then 'jtysl'
when '商务粮食类' then 'swlsl'
when '经贸类' then 'jml'
when '邮政类' then 'yzl'
when '教育类' then 'jyl'
when '体育类' then 'tyl'
when '环保类' then 'hbl'
when '供销类' then 'gxl'
when '民政类' then 'mzl'
when '其他类' then 'qtl'
else 'qtl' end as type_pic_flag,
t.status,st.name status_name,t.sd_name,t.sd_tel,t.corp_grade
,0 yh_num
from (select :new.id as id,
:new.add_time as add_time,
:new.add_user as add_user,
:new.audit_state as audit_state,
:new.business_address as business_address,
:new.jing_ying_xiang_mu as jing_ying_xiang_mu,
:new.city as city,
:new.close_type as close_type,
:new.close_why as close_why,
:new.cm_name as cm_name,
:new.cm_phone_num as cm_phone_num,
:new.cm_post as cm_post,
:new.cm_tel as cm_tel,
:new.cyrs as cyrs,
:new.email as email,
:new.fax_num as fax_num,
:new.gs_code as gs_code,
:new.guid as guid,
:new.is_dele as is_dele,
:new.latitude as latitude,
:new.login_count as login_count,
:new.login_name as login_name,
:new.login_time as login_time,
:new.longitude as longitude,
:new.lr_name as lr_name,
:new.lr_phone_num as lr_phone_num,
:new.lr_post as lr_post,
:new.lr_tel as lr_tel,
:new.name as name,
:new.office_address as office_address,
:new.org_code as org_code,
:new.parent_corp as parent_corp,
:new.parent_id as parent_id,
:new.password as password,
:new.password2 as password2,
:new.phone as phone,
:new.postcode as postcode,
:new.province as province,
:new.reg_address as reg_address,
:new.reg_date as reg_date,
:new.registration as registration,
:new.report_type as report_type,
:new.sd_name as sd_name,
:new.sd_phone_num as sd_phone_num,
:new.sd_post as sd_post,
:new.sd_tel as sd_tel,
:new.corp_size as corp_size,
:new.sort as sort,
:new.validity_date as validity_date,
:new.women_count as women_count,
:new.zhu_ce_zi_chan as zhu_ce_zi_chan,
:new.country as country,
:new.eco_type as eco_type,
:new.membership as membership,
:new.scale as scale,
:new.status as status,
:new.street as street,
:new.village as village,
:new.assess_level as assess_level,
:new.bu_license_path as bu_license_path,
:new.has_award_info as has_award_info,
:new.has_chemical_use as has_chemical_use,
:new.has_hea as has_hea,
:new.has_major_hazard as has_major_hazard,
:new.has_punish_info as has_punish_info,
:new.has_special_device as has_special_device,
:new.has_special_man as has_special_man,
:new.has_tailing_mine as has_tailing_mine,
:new.issuing_authority as issuing_authority,
:new.level_end_date as level_end_date,
:new.level_start_date as level_start_date,
:new.parent_corp as parent_corp,
:new.serial_number as serial_number,
:new.has_gas as has_gas,
:new.is_drill as is_drill,
:new.is_monitor as is_monitor,
:new.is_record as is_record,
:new.gu_ding_zi_chan as gu_ding_zi_chan,
:new.nian_chan_zhi as nian_chan_zhi,
:new.corp_grade as corp_grade,
:new.grade_id as grade_id,
:new.grade_num as grade_num,
:new.grade_status as grade_status,
:new.last_login_ip as last_login_ip,
:new.version as version,
:new.update_time as update_time
from dual) t
left join tb_user_dept v on t.village = v.id
left join tb_user_dept str on t.street = str.id
left join tb_user_dept c on t.country = c.id
left join tb_corp_membership m on t.membership = m.id
left join TB_CORP_TO_TYPES ty on ty.corp_id=t.id
left join tb_corp_type t3 on t3.id=ty.type_id
left join tb_corp_type t2 on t2.id=t3.parent_id
left join tb_corp_type t1 on t1.id=t2.parent_id
left join tb_corp_status st on st.id = t.status
where t.id=:new.ID;
elsif updating then
delete from tb_corp_view_gis_temp t where t.id=:new.id;
insert into TB_CORP_VIEW_GIS_TEMP (ID, NAME, PHONE, LONGITUDE, LATITUDE, CORP_ID_FOR_DEPTNAMES, COUNTRY, STREET, VILLAGE, COUNTRY_NAME, STREET_NAME, VILLAGE_NAME, MEMBERSHIP, MEMBERSHIP_NAME, IS_DELE, TYPE_ID, TYPE_NAME, TYPE1_ID, TYPE1_NAME, TYPE2_ID, TYPE2_NAME, TYPE_PIC_FLAG, STATUS, STATUS_NAME, SD_NAME, SD_TEL, CORP_GRADE, YH_NUM)
select distinct t.id,t.name,t.phone,t.longitude,t.latitude,t.id CORP_ID_FOR_DEPTNAMES,t.country,t.street,t.village,
c.name country_name,str.name street_name,v.name village_name,t.membership, m.name membership_name,t.is_dele,
t1.id type_id,t1.type_name,t2.id type1_id,t2.type_name type1_name,t3.id type2_id,t3.type_name type2_name,
case t1.type_name
when '烟花爆竹类' then 'yhbzl'
when '民爆企业类' then 'mbqyl'
when '煤矿类' then 'mkl'
when '卫生类' then 'wsl'
when '环卫类' then 'hwl'
when '质监类' then 'zjl2'
when '公安类' then 'gal'
when '消防类' then 'xfl'
when '工商类' then 'gsl'
when '住建类' then 'zjl1'
when '文体广电新闻出版类' then 'wtgdxwcbl'
when '冶金工贸类' then 'yjgml'
when '非煤矿山类' then 'fmksl'
when '园林类' then 'yll'
when '林业类' then 'lyl2'
when '旅游类' then 'lyl1'
when '农业类' then 'nyl'
when '农机类' then 'njl'
when '畜牧类' then 'xml'
when '水利类' then 'sll'
when '食药类' then 'syl'
when '危险化学品类' then 'wxhxpl'
when '交通运输类' then 'jtysl'
when '商务粮食类' then 'swlsl'
when '经贸类' then 'jml'
when '邮政类' then 'yzl'
when '教育类' then 'jyl'
when '体育类' then 'tyl'
when '环保类' then 'hbl'
when '供销类' then 'gxl'
when '民政类' then 'mzl'
when '其他类' then 'qtl'
else 'qtl' end as type_pic_flag,
t.status,st.name status_name,t.sd_name,t.sd_tel,t.corp_grade
,0 yh_num
-- ,(select tzhv1.hzd_yh_num from tb_zczb_hzd_view tzhv1,(select max(tzhv.update_time) update_time,tzhv.corp_id from tb_zczb_hzd_view tzhv group by tzhv.corp_id) tzhv2 where tzhv1.corp_id=tzhv2.corp_id and tzhv1.update_time=tzhv2.update_time and tzhv2.corp_id=t.id) yh_num
from (select :new.id as id,
:new.add_time as add_time,
:new.add_user as add_user,
:new.audit_state as audit_state,
:new.business_address as business_address,
:new.jing_ying_xiang_mu as jing_ying_xiang_mu,
:new.city as city,
:new.close_type as close_type,
:new.close_why as close_why,
:new.cm_name as cm_name,
:new.cm_phone_num as cm_phone_num,
:new.cm_post as cm_post,
:new.cm_tel as cm_tel,
:new.cyrs as cyrs,
:new.email as email,
:new.fax_num as fax_num,
:new.gs_code as gs_code,
:new.guid as guid,
:new.is_dele as is_dele,
:new.latitude as latitude,
:new.login_count as login_count,
:new.login_name as login_name,
:new.login_time as login_time,
:new.longitude as longitude,
:new.lr_name as lr_name,
:new.lr_phone_num as lr_phone_num,
:new.lr_post as lr_post,
:new.lr_tel as lr_tel,
:new.name as name,
:new.office_address as office_address,
:new.org_code as org_code,
:new.parent_corp as parent_corp,
:new.parent_id as parent_id,
:new.password as password,
:new.password2 as password2,
:new.phone as phone,
:new.postcode as postcode,
:new.province as province,
:new.reg_address as reg_address,
:new.reg_date as reg_date,
:new.registration as registration,
:new.report_type as report_type,
:new.sd_name as sd_name,
:new.sd_phone_num as sd_phone_num,
:new.sd_post as sd_post,
:new.sd_tel as sd_tel,
:new.corp_size as corp_size,
:new.sort as sort,
:new.validity_date as validity_date,
:new.women_count as women_count,
:new.zhu_ce_zi_chan as zhu_ce_zi_chan,
:new.country as country,
:new.eco_type as eco_type,
:new.membership as membership,
:new.scale as scale,
:new.status as status,
:new.street as street,
:new.village as village,
:new.assess_level as assess_level,
:new.bu_license_path as bu_license_path,
:new.has_award_info as has_award_info,
:new.has_chemical_use as has_chemical_use,
:new.has_hea as has_hea,
:new.has_major_hazard as has_major_hazard,
:new.has_punish_info as has_punish_info,
:new.has_special_device as has_special_device,
:new.has_special_man as has_special_man,
:new.has_tailing_mine as has_tailing_mine,
:new.issuing_authority as issuing_authority,
:new.level_end_date as level_end_date,
:new.level_start_date as level_start_date,
:new.parent_corp as parent_corp,
:new.serial_number as serial_number,
:new.has_gas as has_gas,
:new.is_drill as is_drill,
:new.is_monitor as is_monitor,
:new.is_record as is_record,
:new.gu_ding_zi_chan as gu_ding_zi_chan,
:new.nian_chan_zhi as nian_chan_zhi,
:new.corp_grade as corp_grade,
:new.grade_id as grade_id,
:new.grade_num as grade_num,
:new.grade_status as grade_status,
:new.last_login_ip as last_login_ip,
:new.version as version,
:new.update_time as update_time
from dual) t
left join tb_user_dept v on t.village = v.id
left join tb_user_dept str on t.street = str.id
left join tb_user_dept c on t.country = c.id
left join tb_corp_membership m on t.membership = m.id
left join TB_CORP_TO_TYPES ty on ty.corp_id=t.id
left join tb_corp_type t3 on t3.id=ty.type_id
left join tb_corp_type t2 on t2.id=t3.parent_id
left join tb_corp_type t1 on t1.id=t2.parent_id
left join tb_corp_status st on st.id = t.status
where t.id=:new.ID;
--and t.is_dele='0' and t.status='正常';
end if;
end;
/
-- Create table
create table TB_CORP_VIEW_GIS_TEMP
(
id NUMBER(10),
name VARCHAR2(256 CHAR),
phone VARCHAR2(256 CHAR),
longitude VARCHAR2(255 CHAR),
latitude VARCHAR2(255 CHAR),
corp_id_for_deptnames NUMBER(10),
country NUMBER(10),
street NUMBER(10),
village NUMBER(10),
country_name VARCHAR2(255 CHAR),
street_name VARCHAR2(255 CHAR),
village_name VARCHAR2(255 CHAR),
membership NUMBER(10),
membership_name VARCHAR2(50 CHAR),
is_dele VARCHAR2(1 CHAR),
type_id NUMBER(10),
type_name VARCHAR2(225 CHAR),
type1_id NUMBER(10),
type1_name VARCHAR2(225 CHAR),
type2_id NUMBER(10),
type2_name VARCHAR2(225 CHAR),
type_pic_flag VARCHAR2(50),
status NUMBER(10),
status_name VARCHAR2(50 CHAR),
sd_name VARCHAR2(100 CHAR),
sd_tel VARCHAR2(100 CHAR),
corp_grade VARCHAR2(256 CHAR),
yh_num NUMBER
)
tablespace USERS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 4
minextents 1
maxextents unlimited
);
-- Create/Recreate indexes
create index CORP_VIEW_GIS_TEMPCONTRORY on TB_CORP_VIEW_GIS_TEMP (COUNTRY, STREET, VILLAGE, TYPE_ID, TYPE1_ID, TYPE2_ID)
tablespace USERS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 1M
minextents 1
maxextents unlimited
);
create index CORP_VIEW_GIS_TEMPNAME on TB_CORP_VIEW_GIS_TEMP (NAME)
tablespace USERS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 2M
minextents 1
maxextents unlimited
);
create index TB_CORP_VIEW_GIS_TEMPID on TB_CORP_VIEW_GIS_TEMP (ID)
tablespace USERS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 704K
minextents 1
maxextents unlimited
);
create index TB_CORP_VIEW_GIS_TEMPIS_DELE on TB_CORP_VIEW_GIS_TEMP (IS_DELE)
tablespace USERS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 448K
minextents 1
maxextents unlimited
);
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
