2019年4月

说明：运行完成，会生成一个删除原有触发器，生成新的触发器的sql,方便选择性的运行

delimiter ////

        set @definner = 'root@127.0.0.1';

        SET SESSION group_concat_max_len = 100000000000;

        select group_concat(
                                        CONCAT('use ',dbname,char(10),'#######No.',(@rowNum:=@rowNum+1),',old definner=',obj_defer,',new definner=',@definner,'#######',char(10),'delimiter ****',char(10),
                                                                        'DROP TRIGGER if exists `',trig_name,'`;',char(10),
                                                                        'CREATE DEFINER=',@definner ,' TRIGGER `',trig_name,'` ',timing,' ',evnt,' ON `',tb_name,'`',char(10),
                                                                        'FOR EACH ROW',char(10),
                                                                        sql_body,';',char(10),
                                                                        '****',char(10),
                                                                        'delimiter ;',char(10),char(10)
                                                        )
                        separator '')
                                                           as s from (
        select TRIGGER_SCHEMA as dbname,TRIGGER_NAME as trig_name,EVENT_MANIPULATION as evnt,EVENT_OBJECT_TABLE as tb_name,ACTION_STATEMENT as sql_body,ACTION_TIMING as timing,DEFINER as obj_defer from `information_schema`.`TRIGGERS`
        ,
        (Select (@rowNum :=0) ) b
        ) x
           where obj_defer !=@definner and obj_defer!=replace(@definner,'`','')
        
        into dumpfile '/user/tmp/aaa.sql';
        
////