mysql“插入后"触发器基于其他字段来计算一个字段 [英] mysql 'after insert' trigger to calculate a field based on other fields

问题描述

我正在尝试创建一个触发器，该触发器将根据用户输入的经度/经度列来更新GEOMETRY列.我的触发器看起来像这样-

I am trying to create a trigger that will update a GEOMETRY column based on lat/lng columns entered by the user. My trigger looks like so --

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值的新行时，出现以下错误-

However, I get the following error when I insert a new row with lng, lat values --

错误1442(HY000):无法更新存储函数/触发器中的表'tbl'，因为调用该存储函数/触发器的语句已使用该表.

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.

我不能创建这样的触发器吗?如果没有，那么自动化的方法是什么?

Can I not create such a trigger? If not, what is the way to automate this?

推荐答案

尝试在INSERT触发器之前使用并修改所需的值，例如-

Try to use BEFORE INSERT trigger and modify value you need, e.g. -

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 |
+----+------+------+------+

这篇关于mysql“插入后"触发器基于其他字段来计算一个字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！