-- 根据部门ID查找单位名

drop function if exists updateDep;

delimiter $

create function updateDep(depid varchar(255)) returns varchar(255)

begin

    declare is_company_dep varchar(55);

    declare companyname varchar(255);

    declare pid varchar(255);

    declare i int;

    set pid = depid;

    set i = 0;

    while i<100 do

        select EXT2,DEPARTMENTNAME,PARENTDEPARTMENTID into is_company_dep,companyname,pid from ORGDEPARTMENT where id = pid ;

        set i = i + 1;

        if is_company_dep = 1 then set i = 101;

        END IF ;

    END WHILE;

    return companyname;

end

$

delimiter ;

调用：

-- 触发器 修改成员

drop trigger if exists t_org_user_update;

delimiter $

create trigger t_org_user_update

before update on ORGUSER

for each row

begin

    DECLARE oldcname varchar(255);

    DECLARE newcname varchar(255);

    DECLARE cname varchar(255);

    DECLARE nowname varchar(255);

    select COMPANYNAMES from ORGUSER where id = old.id into cname;

    set oldcname = updateDep(old.departmentid);

    set newcname = updateDep(new.departmentid);

    set nowname = REPLACE(cname,oldcname,newcname);

    set new.COMPANYNAMES = nowname;

    insert into APP_ORG_LISTENER values(null,2,1,old.ID,old.ROLEID,unix_timestamp(now()),1);

end

$

delimiter ;