CODEWITHSUNDEEP
mysqlsqltriggers
punkish
punkish

Reputation: 15248

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?

Upvotes: 3

Views: 11592

Answers (2)

Devart
Devart

Reputation: 121922

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

Upvotes: 6

Derek
Derek

Reputation: 23228

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.

Upvotes: 0

Related Questions