Error in Trigger "AFTER INSERT ON ..." from MySql

Question

Error in Trigger "AFTER INSERT ON ..." from MySql

Navigation

#1 by (0 votes)

1

I'm having problems with creating a trigger in mysql, but I do not know why it's causing me an error. The table in which I want to insert once the trigger is activated is the following:

CREATE TABLE 'reportes' (
  'id' int(11) NOT NULL,
  'tipoCambio' varchar(10) NOT NULL,
  'fechaCambio' date NOT NULL,
  'rut' varchar(10) NOT NULL,
  'nombre' varchar(20) NOT NULL,
  'idCredencial' int(11) NOT NULL,
  'reserva' varchar(80) NOT NULL,
  'horario' varchar(20) NOT NULL
)

And the trigger is this:

DELIMITER //

CREATE TRIGGER agregaReserva AFTER INSERT ON reserva
FOR EACH ROW
BEGIN

DECLARE tRut VARCHAR(10);
SELECT u.rut INTO @tRut FROM reserva r 
INNER JOIN credencial c ON r.idCredencial = c.id
INNER JOIN usuario u ON c.id = u.idCredencial 
WHERE r.id = NEW.id;

DECLARE tFechaAct DATE;
SELECT CURDATE() INTO @tFechaAct;

DECLARE tNombre VARCHAR(20);
SELECT u.nombre INTO @tNombre FROM reserva r 
INNER JOIN credencial c ON r.idCredencial = c.id
INNER JOIN usuario u ON c.id = u.idCredencial 
WHERE r.id = NEW.id;

DECLARE tReserva VARCHAR(80);
SELECT CONCAT(tr.tipo, ' Nº ', tr.numeroDispositivo) AS reserva 
INTO @tReserva FROM tiporeserva tr 
INNER JOIN reserva r ON tr.id = r.idTipoReserva
WHERE r.id = NEW.id;

DECLARE tHorario VARCHAR(20);
SELECT CONCAT(h.horaComienzo, ' - ', h.horaFin) 
INTO @tHorario FROM horario h
INNER JOIN reserva r ON h.bloque = r.bloqueHorario
WHERE r.id = NEW.id

INSERT INTO 'reportes'('tipoCambio', 'fechaCambio', 'rut', 'nombre', 'idCredencial', 'reserva', 'horario') VALUES ('Reserva', @tFechaAct, @tRut, @tNombre, NEW.idCredencial, @tReserva, @tHorario);
END //

Finally, the error he mentions is:

Error MySQL has said:

1064 - Something is wrong in its syntax near 'DECLARE tFechaAct DATE; SELECT CURDATE () INTO @tFechaAct;

DECLARE tName VA 'on line 11 Open new phpMyAdmin window

mysql mariadb phpmyadmin trigger

asked by Alfredo 26.05.2018 в 07:50

source

1 answer

Disable / Enable according to a JAVASCRIPT data How can I store data from a datagridview to a MySql database?

score 0 · Accepted Answer

The error you have is that you do not declare the local variables at the beginning of the block, MySQL requires to place all the DECLARE at the beginning of the block. To solve this error you must place your code in the following way:

Your trigger:

DELIMITER //

CREATE TRIGGER agregaReserva AFTER INSERT ON reserva
FOR EACH ROW
BEGIN

DECLARE tRut VARCHAR(10);
DECLARE tFechaAct DATE;
DECLARE tNombre VARCHAR(20);
DECLARE tReserva VARCHAR(80);
DECLARE tHorario VARCHAR(20);

SELECT u.rut INTO @tRut FROM reserva r 
INNER JOIN credencial c ON r.idCredencial = c.id
INNER JOIN usuario u ON c.id = u.idCredencial 
WHERE r.id = NEW.id;

SELECT CURDATE() INTO @tFechaAct;

SELECT u.nombre INTO @tNombre FROM reserva r 
INNER JOIN credencial c ON r.idCredencial = c.id
INNER JOIN usuario u ON c.id = u.idCredencial 
WHERE r.id = NEW.id;

SELECT CONCAT(tr.tipo, ' Nº ', tr.numeroDispositivo) AS reserva 
INTO @tReserva FROM tiporeserva tr 
INNER JOIN reserva r ON tr.id = r.idTipoReserva
WHERE r.id = NEW.id;

SELECT CONCAT(h.horaComienzo, ' - ', h.horaFin) 
INTO @tHorario FROM horario h
INNER JOIN reserva r ON h.bloque = r.bloqueHorario
WHERE r.id = NEW.id;

INSERT INTO 'reportes'('tipoCambio', 'fechaCambio', 'rut', 'nombre', 'idCredencial', 'reserva', 'horario') VALUES ('Reserva', @tFechaAct, @tRut, @tNombre, NEW.idCredencial, @tReserva, @tHorario);
END //

Observation: Analyzing your code I deeply notice two things. The first is that you must place a primary key to your table reports. The second is that you will not be able to create your trigger yet because you have to solve the fact that you ask MySQL to insert a value NEW.idCredencial but do not indicate how to obtain it. This will probably generate a new error.

I would suggest you check how to increase a field in a trigger or place idCredency as autoincrementable after creating your report table since in your code samples that idCredencial do not have to follow a specific pattern. This would solve the NEW.idCredenial because you would not have to take into account the idCredencial field in the insert of the trigger since it would be autoincrementable. A less headache.

I hope it helps. A greeting!