在

create table device_update(
sub_serial varchar(64) NOT NULL COMMENT ‘设备短序列号’,
nums int NOT NULL COMMENT ‘更新次数’,
PRIMARY key (sub_serial)
)ENGINE=innodb DEFAULT CHARSET=utf8;

delimiter |
CREATE TRIGGER tr1
AFTER UPDATE ON device_info
FOR EACH ROW
BEGIN
SET @old_update_time = OLD.update_time;
SET @new_update_time = NEW.update_time;
SET @sub_serial = NEW.sub_serial;
IF @old_update_time <> @new_update_time THEN
INSERT INTO test.device_update select @sub_serial,1 from dual ON DUPLICATE KEY UPDATE nums=nums+1;
end if;
end;
|