Trigger calculo consumo

[igebe]

Buenos días amigos, antes de nada decir que llevo usando este foro de forma anonima un par de meses y que vuestras contribuciones me ayudan muchisimo tanto en Java como en MySQL. Muchas gracias!

Hoy decidí registrarme a ver si encuentro una solución al problema que me surgió.

Estoy realizando el proyecto de fin de ciclo de ASIR y cree una base de datos basada en una empresa de autobuses.

Os pongo las dos tablas que he creado y os detallo el problema que me surge.

Código SQL:

CREATE TABLE vehiculos
(
Cod_Veh  INT NOT NULL,
Matricula VARCHAR(7),
Modelo VARCHAR(20),
Capacidad_deposito INT,
Km_totales INT,
Ultimo_repostaje DATE,
Consumo_medio_ultimo_deposito FLOAT,
 
PRIMARY KEY (Cod_Veh)
 
);
 
 
DROP TABLE IF EXISTS repostajes;
 
CREATE TABLE repostajes
(
Cod_Rep   INT   NOT NULL,
Cod_Veh  INT NOT NULL,
Fecha DATE,
Kilometros INT,
Litros_repostados INT,
 

Pues bien, necesito crear un trigger que me permita insertar el consumo medio desde el ultimo repostaje, para eso necesito un trigger de este estilo:

Código SQL:

CREATE TRIGGER repostajes
BEFORE UPDATE ON repostajes
FOR EACH ROW SET
                vehiculos.Km_totales=NEW.Kilometros, 
                vehiculos.Ultimo_repostaje=NEW.Fecha,
                vehiculos.Consumo_medio_ultimo_deposito=(NEW.Litros_repostados * 100) / (Kmultimorepostaje - Kmpenultimo repostaje);
 

Lo que necesito saber es si hay alguna forma que al introducir un nuevo repostaje, mire esos km que acabas de meter y les reste el mismo campo del anterior repostaje con el mismo codigo de vehiculo.

Estoy muy verde en MySQL porque el tema de automatización lo dimos en el tercer trimestre, que por motivos de saludo no pude asistir.

Muchas gracias y un saludo!

[gnzsoloyo]

Estoy muy verde en MySQL

Estás posteando en Oracle, no en MySQL...

Que MySQL hoy le pertenezca a Oracle, no implica que sean lo mismo.
Cada uno de los DBMS tiene sitaxis distintivas, tanto en la creacion de las tablas, como en funciones, lenguaje procedural y otros elementos.

¿Estás usando MySQL u Oracle?

Si es MySQL, muevo tu post al foro correspondiente.

[igebe]

Hola, es sobre MySQL, perdón por el error de novato y las etiquetas. Muchas gracias!

[gnzsoloyo]

Movido a MySQL.

Respecto a esto:

Lo que necesito saber es si hay alguna forma que al introducir un nuevo repostaje, mire esos km que acabas de meter y les reste el mismo campo del anterior repostaje con el mismo codigo de vehiculo.

Si la resta de datos que mencionas se debe hacer sobre la misma tabla, no, no es posible en MySQL, porque de ahcerse generaría un loop infinito.

El problema es así:
Cuando se dispara un TRIGGER se genera un bloqueo de escritura sobre la tabla, que queda en propiedad del proceso que disparó el trigger. Eso implica que no se pueden hacer llamadas DML sobre la misma tabla donde se está ejecutando el trigger (ten en cuenta que un UPDATE/INSERT/DELETE abre un nuevo hilo de proceso que no comparte código con el del TRIGGER en ejecución).

Ahora bien, el loop que menciono es porque como el trigger que planteas es por un UPDATE, si pudiese llamar a una modificacion en otro registro de la tabla, sería un UPDATE que a su vez dispararía nuevamente el trigger en otro hilo, que ejecutaría un UPDATE sobre la misma tabla, el cual dispararía el trigger, ... y así hasta el infinito.
Y no funcionaría tampoco si uno es INSERT y otro UPDATE, porque el UPDATE encontraría bloqueada la tabla a causa del trigger en INSERT, con lo que no podría ejecutarse jamás.

Por esa situacion, MySQL no permite ese tipo de llamadas, y sólo puede generar acciones DML sobre otras tablas, siempre y cuando esas no generen a su vez llamadas a las del trigger.

En definitiva, por seguridad de procesos los triggers y los SP en general tienen restricciones que el DBMS no deja violar.

por otro lado, a mi entender tu caso no necesita de un trigger, sino de un stored procedure para realizar el ISERT/UPDATE en la tabla de repostajes. En él sí podrías hacer toda la logica que necesitas sin necesidad de complicarlo de más.

¿Lo consideraste?

Sin conocer mas detalles del proyecto que presentas no puedo decirte demasiado, pero tu caso me recuerda un proyecto en el que trabajé hace unos años, y que tenia una problemática pareceida. En ese caso la solucion no la hicimos por triggers, y de hehco ni siquiera teníamos que restar nada a ningun registro. Un repostaje es una carga nueva, y cualquier dato que neesitaramos lo obteniamos por consultas...

Piensalo.

[igebe]

Lo había considerado, pero según tengo entendido los procedimientos debo llamarlos para que se ejecuten y no se ejecutan automaticamente.

No se si es posible que se ejecute un procedimiento al realizar una inserción o una actualización en la tabla. Probé un trigger que ejecutara el procedimiento, pero según lo que me has dicho del proceso bloqueando la modificación me imagino que es imposible.

Gracias!

[gnzsoloyo]

Creo que no has entendido del todo la propuesta del SP...
La idea no es que se dispare automáticamente para realziar la tarea cuando hayas hecho la inserción del repostado. Lo que te propongo es que en lugar de hacer el INSERT solitario de un repostado, lo pongas todo en un SP, incluyendo el INSERT de ese repostado.

Basicamente la idea es que envies los datos a insertar por medio de un SP (te comento que en aplicaciones comerciales no se hacen acciones a la base que no sean en SP, por razones de seguriad).

El SP deberá:

1) Recibir los parametros de entrada del repostado, mas uno o dos de salida para excepciones y fallos.
2) Insertar el repostado en su tabla.
3) Si fue exitoso, verificar si debe realizar una resta en el registro anterior.
4) En caso de corresponder, Ejecutar el update al registro encontrado.
5) Devolver por parametro el exito de la operacion o un codigo de error según sea el fallo.

¿Se entiende la idea?

En ese contexto, el "automatismo" se logra por encapsulamiento de la logica completa en un SP.

Nunca te olvices que un SP es valioso no solo por agregar seguridad a los procesos de base, sino porque permite realizar N operaciones a la base en forma atómica, permitiendo control de transacciones mucho más fino y validaciones multiples sin realizar peticiones adicionales a la base esde la aplicación.

[igebe]

No me lo había planteado así, cuando vuelva a tener la oportunidad, esta noche o mañana, lo intento y te comento vale?

Muchas gracias por tu tiempo! ;)