我正在尝试创建一个触发器,它将根据用户输入的lat / lng列更新GEOMETRY列.我的触发器看起来像这样 –
CREATE TRIGGER `tbl.foo`
AFTER INSERT ON `tbl` FOR EACH ROW
BEGIN
UPDATE tbl
SET coord = Point(lng, lat)
WHERE id = NEW.id;
END
但是,当我插入一个带有lng,lat值的新行时,我收到以下错误 –
ERROR 1442 (HY000): Can’t update table ‘tbl’ in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
我可以不创建这样的触发器吗?如果没有,自动化的方法是什么?
解决方法:
尝试使用BEFORE INSERT触发器并修改所需的值,例如 –
CREATE TRIGGER trigger1
BEFORE INSERT
ON table1
FOR EACH ROW
BEGIN
SET NEW.column1 = 'another value';
END
编辑
CREATE TABLE table_test_trigger (
id INT(11) NOT NULL AUTO_INCREMENT,
a INT(11) DEFAULT NULL,
b INT(11) DEFAULT NULL,
c INT(11) DEFAULT NULL,
PRIMARY KEY (id)
);
DELIMITER $$
CREATE TRIGGER trigger1
BEFORE INSERT
ON table_test_trigger
FOR EACH ROW
BEGIN
SET NEW.c = NEW.a + NEW.b;
END
$$
DELIMITER ;
INSERT INTO table_test_trigger(a, b) VALUES (10, 5);
SELECT * FROM table_test_trigger;
+----+------+------+------+
| id | a | b | c |
+----+------+------+------+
| 1 | 10 | 5 | 15 |
+----+------+------+------+