/*****************************教研库 同步学校过程*********************************************************/
/**********************************学校 insert trigger****************************************************/
delimiter $
create trigger jyd_preposition.add_school_info_trigger
after insert on jyd_preposition.school_info for each row
BEGIN
DECLARE school_id VARCHAR(15);
DECLARE school_add VARCHAR(200);
set school_add = NEW.zdmc;
set school_id = NEW.xxdm;
call bdschool.add_school_info(school_add,school_id);
END
delimiter
/**********************************学校 update trigger****************************************************/
delimiter $
create trigger jyd_preposition.update_school_info_trigger
after update on jyd_preposition.school_info for each row
begin
DECLARE w_id1 varchar(180) character set utf8;
DECLARE w_id2 varchar(15) character set utf8;
DECLARE w_id3 varchar(15) character set utf8;
SET w_id1 = NEW.xxdz;
SET w_id2 = NEW.xxmc;
SET w_id3 = NEW.xxdm;
call bdschool.update_school_info(w_id1,w_id2,w_id3);
end$
delimiter
/**********************************学校 delete trigger****************************************************/
delimiter $
create trigger jyd_preposition.del_school_info_trigger
after delete on jyd_preposition.school_info for each row
begin
DECLARE w_id varchar(15) character set utf8;
SET w_id = OLD.xxdm;
DELETE FROM bdschool.sys_dict WHERE dict_code=w_id;
end$
delimiter
/**********************************学校 insert procedure****************************************************/
delimiter $
create procedure bdschool.add_school_info(in str varchar(15))
begin
DECLARE w_id int default 0;
DECLARE w_id1 int default 0;
DECLARE w_id3 varchar(15) default 'schoolType';
if (str is not null) then
if(str1 is not null) then
set @w_id = (select b.dict_code from bdschool.sys_dict b where 1=1 and b.code='cityName' and b.dict_name=str);
set @w_id1= (select count(b.id) from bdschool.sys_dict b where 1=1 and b.code='schoolType');
set @w_id3= 'schoolType';
insert into bdschool.sys_dict(create_date,modify_date,code,description,dict_code,dict_name,order_id,remark,status)
select NOW(),j.jfsj,@w_id3,j.zdmc,j.xxdm,j.xxmc,@w_id1+1,@w_id,1 from jyd_preposition.school_info j where 1=1 AND j.xxdm=str1;
end if;
end if;
end$
delimiter
/**********************************学校 update procedure****************************************************/
delimiter $
create procedure bdschool.update_school_info(in str varchar(15), in str1 varchar(15), in str2 varchar(15))
begin
if (str is not null) then
IF(str1 is not null) then
IF(str2 is not null) then
UPDATE bdschool.sys_dict SET modify_date=NOW(),description=str,dict_name=str1 WHERE dict_code=str2;
end if;
end if;
end if;
end$
delimiter
//*************查看数据库编码方式******************//
show variables like 'character%'
select version()
笔记-mysql 过程和触发器
最新推荐文章于 2024-09-07 08:07:39 发布