首页 / 数据库 / MySQL / MySQL基础：触发器

CREATE TABLE `vardata` （`Id` int（11） NOT NULL AUTO_INCREMENT,`name` varchar（255） DEFAULT NULL,`time` datetime NOT NULL DEFAULT "0000-00-00 00:00:00",`data` double（15,5） DEFAULT "-601.00000",PRIMARY KEY （`Id`,`time`）,UNIQUE KEY `unique` （`name`,`time`））CREATE TABLE `vardata_compute` （`Id` int（11） NOT NULL AUTO_INCREMENT,`name` varchar（255） DEFAULT NULL,`time` datetime DEFAULT NULL,`data` double（15,5） DEFAULT NULL,PRIMARY KEY （`Id`））

DROP TRIGGER IF EXISTS trigger_insert_59data;CREATE TRIGGER trigger_insert_59data AFTER INSERT ON vardata FOR EACH ROW BEGIN DECLARE time_59 DateTime; SET time_59 = NEW.time-INTERVAL 1 SECOND;IF MINUTE（NEW.time）=0 AND SECOND（NEW.time）=0 THENINSERT INTO vardata_compute SET name=NEW.name,time=time_59,data=NEW.data; END IF; END;DROP TRIGGER IF EXISTS trigger_delete_59data;CREATE TRIGGER trigger_delete_59data AFTER DELETE ON vardata FOR EACH ROW BEGIN DECLARE time_59 DateTime;SET time_59 = OLD.time-INTERVAL 1 SECOND;IF MINUTE（OLD.time）=0 AND SECOND（OLD.time）=0 THEN DELETE FROM vardata_compute WHERE time=time_59 AND name=old.name; END IF; END;DROP TRIGGER IF EXISTS trigger_update_59data;CREATE TRIGGER trigger_update_59data AFTER UPDATE ON vardata FOR EACH ROW BEGIN DECLARE time_59 DateTime;SET time_59 = NEW.time-INTERVAL 1 SECOND;IF MINUTE（NEW.time）=0 AND SECOND（NEW.time）=0 THENUPDATE vardata_compute SET data=NEW.data WHERE time=time_59 AND name=NEW.name; END IF;END;