Quiero crear un trigger que me formatte una cadena de caracteres antes de insertarla

[gnzsoloyo]

Sería bueno que nos aclararas un poco acerca de las "diversas razones", porque en realidad el problema es muchísimo más sencillo de resolver desde la aplicación que con un trigger, ya que el trigger comienza a funcionar desde el mismo momento en que se dispara, pero cierto nivel de validaciones tienen que ver con la creación de NEW como registro entrante, así que el error de formato puede saltar antes que el trigger comience.

Te cuento que si trabajas con .Net, eso se resuelve usando sentencias con parámetros, si usas PHP, se puede resolver con createFromFormat o date_format, por ejemplo.
En cualquier caso sería una mejor solución que usar un trigger. Incluso, cuando no pudieras usarlas, si en la sentencia usas en esos casos STR_TO_DATE(), suplirías también el problema, sin necesidad de un trigger.

En definitiva, existen muchas soluciones mucho más efectivas y prácticas que un trigger para tu problema.

En cuanto al error de sintaxis, para otras ocasiones sería mejor que postearas (copiandolo) el mensaje completo de error, ya que sirve de guía.

En tu caso la cosa puede venir por varios lados:
1) Las variables de una rutina almacenada se pueden declarar en la misma línea si y sólo si son del mismo tipo y tamaño, con una sola declaración por cada uno de los tipos.
2) Las variables de usuario que comienzan con "@" no se declaran ni son locales. Son globales por sesión de usuario, por lo que deben ser inicializadas y luego usadas, pero no mueren al terminar la rutina ni aún creándolas en ella.
3) Toda declaración de un TRIGGER lleva las cláusulas FOR EACH ROW, que en tu caso faltaban.
4) Las comillas (") encierran nombres de bases, tablas y campos. Si quieres encerrar cadenas de texto, usa apóstrofes (').

El código podría quedar:

Código sql:

CREATE TRIGGER tr_formato_fecha  BEFORE INSERT ON emp_users FOR EACH ROW
BEGIN
    DECLARE anio CHAR(4);
    DECLARE mes, dia CHAR(2);
    SET anio = MID(NEW.fecha_nacimiento, 7, 4);
    SET mes = MID(NEW.fecha_nacimiento, 4, 2);
    SET dia = MID(NEW.fecha_nacimiento, 1, 2);
    SET NEW.fecha_nacimiento = CONCAT(anio, '-', mes, '-', dia);
END;
 

De todos modos, como ya te dije, sería más sencillo usar:

Código SQL:

INSERT INTO emp_users(fecha_nacimiento) 
VALUES(STR_TO_DATE(fechadenacimiento, '%d/%m/%Y %H'));
 

Mira este ejemplo:

Código SQL:

mysql> SELECT STR_TO_DATE('23/01/2003 13:45:08', '%d/%m/%Y %H');
+---------------------------------------------------+
| STR_TO_DATE('23/01/2003 13:45:08', '%d/%m/%Y %H') |
+---------------------------------------------------+
| 2003-01-23 13:00:00                               |
+---------------------------------------------------+
1 ROW IN SET, 1 warning (0.00 sec)
 

La única condición sine qua non, es que el separador usado en la fecha tiene que ser el mismo que se indica en la función, o de lo contrario devolverá NULL:

Código sql:

mysql> SELECT STR_TO_DATE('23/01/2003 13:45:08', '%d-%m-%Y %H');
+---------------------------------------------------+
| STR_TO_DATE('23/01/2003 13:45:08', '%d-%m-%Y %H') |
+---------------------------------------------------+
| NULL                                              |
+---------------------------------------------------+
1 ROW IN SET, 1 warning (0.00 sec)