Trigger de mise à jour des colonnes

Version imprimable

salut,

comment mettre respectivement a jour les colonnes "name_nearest_place" et "distance_nearest_place" avec les colonnes "name" et "distance" de la table gs_object_data_xxxxxxxx.

Code:

1
2
3
4
5
6
7
8
9
10
 
CREATE TRIGGER `t_gs_object_data_xxxxxx` AFTER INSERT ON `gs_object_data_xxxxxx`
 FOR EACH ROW BEGIN
INSERT INTO nearest_marker(name,distance)
SELECT get_distance_nearest(new.lat, new.lng, marker_lat, marker_lng) as distance, marker_name
FROM gs_user_markers_share ORDER BY distance DESC LIMIT 1;
UPDATE gs_object_data_xxxxxx
SET name_nearest_place  = xxxxx,
distance_nearest_place  = yyyyy ;
END

merci

Il faudra faire un SELECT d'abord
Stocker les champs marker_name, distance dans des variables
Puis les réutiliser dans les requêtes INSERT et UPDATE

Comme ceci :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
 
CREATE TRIGGER t_gs_object_data_xxxxxx AFTER INSERT ON gs_object_data_xxxxxx
FOR EACH ROW 
BEGIN
 
	DECLARE alpha_distance decimal;
	DECLARE alpha_name varchar(100);
 
	SELECT marker_name, get_distance_nearest(new.lat, new.lng, marker_lat, marker_lng) as distance
	INTO alpha_distance, alpha_name
	FROM gs_user_markers_share
	ORDER BY distance DESC
	LIMIT 1;
 
	INSERT INTO nearest_marker( name, distance ) VALUES ( alpha_name, alpha_distance );
	UPDATE gs_object_data_xxxxxx SET name_nearest_place = alpha_name, distance_nearest_place = alpha_distance;
END

Est-ce qu'il ne manque pas un WHERE dans ton UPDATE ?

04/09/2019, 23h36
manzeki
-- Pourquoi ne pas stocker les positions en utilisant le type géospatial POINT
-- Distance entre deux points : ST_Distance()
-- Par exemple (marker_lat, marker_lng) peuvent être stockées dans un seul champ de type POINT comment ceci
Code:

1 2 3 4 5 6 CREATE TABLE t_gs_object_data_xxxxxx ( .... marker_name varchar(100), position POINT DEFAULT NULL, ... );
-- ce qui donnerait :
Code:

1 2 3 4 5 SELECT marker_name, ST_Distance( new.position, position ) as distance INTO alpha_distance, alpha_name FROM gs_user_markers_share ORDER BY distance DESC LIMIT 1;