mysql 'after insert' trigger to calculate a field based on other fields
11,103
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 |
+----+------+------+------+
Comments
-
punkish almost 2 years
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?
-
punkish over 12 yearshmmm... how can I update the NEW.column1 with values from other columns unless those columns are populated first? See, the application allows users to enter lat/lng values. The Point() is calculated using those lat/lng values. UPDATE: actually, this suggestion does work. All I have to do is prefix all values with NEW. Thanks.