Mysql log trigger implementation code

Sql statement

DROP TRIGGERIFEXISTS sys_menu_edit CREATE TRIGGER sys_menu_edit BEFORE UPDATE ON sys_menu FOR EACH ROWBEGININSERT INTO `g4m`.`sys _ log` (`table_ name`, `jsonid`, `data_ json`) VALUES ('sys_menu',old.id,CONCAT ("{", CONCAT_WS (',', CONCAT_WS (old.id,'"id": ",'"), CONCAT_WS (old.CODE,'"code": "','"), CONCAT_WS (old.type_dic,'"type_dic": "'' '"), CONCAT_WS (old.NAME,'" name ":"',''), CONCAT_WS (old.pid,'"pid": "','"), CONCAT_WS (old.status_dic,'"status_dic": "','"), CONCAT_WS (old.url,'"url": "','"), CONCAT_WS (old.path,'"path": "','") CONCAT_WS (old.icon,'"icon": "',''), CONCAT_WS (old.sort,'" sort ":"',''), CONCAT_WS (old.remark,'"remark": "','"), CONCAT_WS (old.create_time,'"create_time": "','"), CONCAT_WS (old.modify_uer_id,'"modify_uer_id": "','") CONCAT_WS (old.modify_time,'"modify_time": "','"), "}")

MySQL uses triggers to implement logging

CREATE TRIGGER news_log AFTER INSERT ON news

FOR EACH ROW INSERT INTO `news_ logs` select *, now () from news where newsid = (select max (newsid) from news)

DROP TRIGGER news_log

A total of two tables, one is the original table news, the other is the log table news_logs (with an extra column of dates), you can automatically record the log when you insert it without writing code. Similarly, you can insert an update log when you update. For more information, please see the documentation of MySQL.

Mysql is supplemented by triggers to record operations

Mysql cannot perform insert,update,delete operations in one trigger, so you need to build three triggers respectively.

Insert trigger:

Delimiter $$create trigger tri_city_insertafter insert on t_xfw_city for each rowbegin insert into t_tri_city (id,name,provinceid,ctype) values (new.id,new.name,new.provinceid,1); end

Update trigger:

Delimiter $$create trigger tri_city_updateafter update on t_xfw_city for each rowbegin insert into t_tri_city (id,name,provinceid,ctype) values (new.id,new.name,new.provinceid,2); end

Delete trigger:

Delimiter $$create trigger tri_city_deleteafter delete on t_xfw_city for each rowbegin insert into t_tri_city (id,name,provinceid,ctype) values (old.id,old.name,old.provinceid,3); end

It's a bit troublesome to build three triggers for a table. I don't know if there's a better way.