Mysql5.7.21 Navicat觸發器創建

CREATE TRIGGER m_trigger AFTER UPDATE ON table1 
FOR EACH ROW
BEGIN
IF( old.status!= new.status)
THEN
SELECT COUNT(id) FROM table1 WHERE stats='Y' INTO @yCount;

SELECT max(num) from table2 INTO @maxYCount;
IF( @yCount> @maxYCount)
THEN
insert into table2 (num) select count(1) from table1 b where b.statu='Y';
END IF;
END IF;
END

註：mysql中變數不用事先聲明，用時直接‘@變數名’使用。

1、set @num=1;  set @num:=1; 

2、SELECT COUNT(id) FROM table1 WHERE stats='Y' INTO @yCount;