mysql触发器修改所属用户_mysql触发器-记录更改用户信息

begindeclare ip,writedate varchar(2000);declare nowip varchar;declare nowuser varchar;declare nowinfo varchar;#使用游标记录当前连接数据库的ip地址declare _ipCursor CURSOR FORSELECT `USER`,`HOST`,INFO FROM information_

北决霓音

892人浏览 · 2021-02-03 18:18:06

北决霓音 · 2021-02-03 18:18:06 发布

begin

declare ip,writedate varchar(2000);

declare nowip varchar;

declare nowuser varchar;

declare nowinfo varchar;

#使用游标记录当前连接数据库的ip地址

declare _ipCursor CURSOR FOR

SELECT `USER`,`HOST`,INFO FROM information_schema.`PROCESSLIST`;

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET _done = 1;

#打开游标

OPEN _ipCursor;

WHILE _done<>1 DO

#从游标中获取用户、ip、info信息

FETCH _ipCursor INTO nowuser,nowip,nowinfo;

ip+=nowuser+"-"+nowip+"-"+nowinfo;

END WHILE;

CLOSE _ipCursor;

if (NEW.User_Password<>OLD.User_Password) then

insert into T_USERT_PASSWORDCHANGE_RECORD (IP,DATE,PASSWORD_BEFORE,PASSWORD_AFTER,USER_NAME,USER_ID) values (ip,now(),OLD.User_Password,NEW.User_Password,NEW.User_Name,NEW.User_ID);

end if;

end