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;