今天工作需要,想要實現將僅對狀態更新的表進行歷史記錄顯示,於是考慮在原表中建立觸發器,將更新的內容同時寫入另一張表
於是進行測試
--建立測試表CREATE TABLE `triggletest_triggle` ( `id` INT(11) NOT NULL, `name` VARCHAR(5) NULL DEFAULT NULL, PRIMARY KEY (`id`) ) COLLATE='latin1_swedish_ci' ENGINE=InnoDB --建立目標表 CREATE TABLE `triggletest` ( `seq` INT(11) NOT NULL, `id` INT(11) NOT NULL, `name` VARCHAR(5) NULL DEFAULT NULL, PRIMARY KEY (`seq`), INDEX `id` (`id`) ) COLLATE='latin1_swedish_ci' ENGINE=InnoDB; ---寫入測試數據 INSERT INTO `triggletest_triggle` VALUES(1,'A'); --建立觸發器 DROP TRIGGER if EXISTS test1 CREATE TRIGGER test1 AFTER UPDATE ON triggletest_triggle FOR EACH ROW BEGIN INSERT INTO triggletest(id) values (new.id); END
執行觸發器語句,報錯,報錯內容如下:
/* SQL錯誤(1064):You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CREATE TRIGGER test1 AFTER UPDATE ON triggletest_triggle FOR EACH ROW BEGIN ' at line 2 */ ---最終實現代碼 DROP TRIGGER if EXISTS test1; CREATE TRIGGER test1 AFTER UPDATE ON test.triggletest_triggle FOR EACH ROW BEGIN INSERT INTO triggletest(id,name) values (new.id,new.name); END;
分析,由於訪問工具HediSQL,導致無法正常創建觸發器,相同語句,在HediSQL中執行,報錯,使用shell調用mysql,直接執行程序,成功。