mysql 'after insert' trigger to calculate a field based on other fields

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

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

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?

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

EDIT

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

According to the MySQL Documentation found here: http://dev.mysql.com/doc/refman/5.0/en/stored-program-restrictions.html

Within a stored function or trigger, it is not permitted to modify a table that is already being used (for reading or writing) by the statement that invoked the function or trigger.

If you want coord to always be Point(lng, lat), you might want to just put a view overtop of the table and have that be one of the columns and then query the view instead of the table.

If I think of another workaround I'll be sure to post it.