解决root用户无权限创建触发器

-- 创建t_b_macstatus数据写入时往t_801_mac_event写数据的触发器
CREATE TRIGGER trigger_event BEFORE INSERT ON t_b_macstatus FOR EACH ROW
IF
	( SELECT deviceTypeId FROM t_006_mac WHERE MacId = new.MacId ) IN ( 1, 2, 4 ) THEN
		INSERT INTO t_801_mac_event (
			WITH temp AS ( SELECT deviceTypeId FROM t_006_mac WHERE MacId = new.MacId ) SELECT NULL
			,
		CASE
				( SELECT * FROM temp ) 
				WHEN 1 THEN
			IF
				( new.State = 1, '蔚来窗恢复', '蔚来窗告警' ) 
				WHEN 2 THEN
			IF
				( new.State = 1, '充电站（非）恢复', '充电站（非）告警' ) 
				WHEN 4 THEN
			IF
				( new.State = 1, '摄像头恢复', '摄像头告警' ) 
			END AS EventName,
			new.AddTime AS EventTime,
			new.macId AS MacId,
			new.State AS MacStatus,
			( SELECT * FROM temp ) AS DeviceTypeId,
			NOW() AS AddTime,
			'' AS Content,
			'' AS Topic 
		);

END IF;

-- 查询root用户操作触发器的权限
select Host,User,Trigger_priv from mysql.user where user='root';
	
-- 修改root用户操作触发器的权限
update mysql.user set Trigger_priv = 'Y' where user='root';

-- 刷新权限
flush privileges;

-- 在Navicat上右击表设置权限中勾选触发器权限

-- Linux重启MySQL
service mysqld restart

-- 查询所有触发器
SHOW TRIGGERS

-- 删除触发器
drop trigger if exists trigger_event;