MySQL Forums
Forum List  »  Triggers

MySQL ERROR: Can't update table SQL trigger
Posted by: Sal Rosselli
Date: March 22, 2020 09:19AM

I am trying to implement a trigger after the insert on a table in order to update a value on another table. Basically, I need to update the time everytime an appointment is added.

The problem is that I get an error when I try to insert on appuntamento (appointment table). This is the error that I get:

Error Code: 1442. Can't update table 'prenotazione' in stored function/trigger because it is already used by statement which invoked this stored function/trigger. 0.000 sec

Those are my tables:

Appointment table:

CREATE TABLE `appuntamento` (
`idAppuntamento` int NOT NULL AUTO_INCREMENT,
`Prenotazione` int NOT NULL,
`Servizio` int NOT NULL,
`Impiegato` int NOT NULL,
PRIMARY KEY (`idAppuntamento`),
KEY `Servizio_idx` (`Servizio`),
KEY `Impiegato_idx` (`Impiegato`),
KEY `prenotaz_1` (`Prenotazione`),
CONSTRAINT `impiegato_1` FOREIGN KEY (`Impiegato`) REFERENCES `impiegato` (`idImpiegato`),
CONSTRAINT `prenotaz_1` FOREIGN KEY (`Prenotazione`) REFERENCES `prenotazione` (`idPrenotazione`),
CONSTRAINT `servizio_1` FOREIGN KEY (`Servizio`) REFERENCES `servizio` (`idServizio`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8

Services table:

`CREATE TABLE `servizio` (
`idServizio` int NOT NULL AUTO_INCREMENT,
`TipoServizio` varchar(45) NOT NULL,
`Descrizione` varchar(200) DEFAULT NULL,
`Durata` int NOT NULL,
`Prezzo` decimal(5,2) DEFAULT NULL,
PRIMARY KEY (`idServizio`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8``

Reservation table:

CREATE TABLE `prenotazione` (
`idPrenotazione` int NOT NULL AUTO_INCREMENT,
`Cliente` int NOT NULL,
`Data` date NOT NULL,
`OraInizio` time NOT NULL,
`OraFine` time NOT NULL DEFAULT '00:00:00',
`CostoTotale` decimal(3,2) NOT NULL DEFAULT '0.00',
PRIMARY KEY (`idPrenotazione`),
KEY `Cliente_idx` (`Cliente`),
CONSTRAINT `cliente_1` FOREIGN KEY (`Cliente`) REFERENCES `cliente` (`idCliente`)
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8

Trigger:

DELIMITER $$
CREATE TRIGGER update_hour
AFTER INSERT ON appuntamento
FOR EACH ROW
BEGIN
DECLARE durata int;
SELECT Durata INTO @durata
FROM servizio
WHERE NEW.Servizio = idServizio;

UPDATE prenotazione
SET OraFine = OraInizio + @durata
WHERE NEW.Prenotazione = prenotazione.idPrenotazione;
END;
$$
DELIMITER ;

This is the insert on Appuntamento (maybe this is the problem):

INSERT INTO Appuntamento( Prenotazione, Servizio, Impiegato) VALUES
((SELECT idPrenotazione from prenotazione WHERE idPrenotazione = '11'),
(SELECT idServizio from servizio WHERE TipoServizio='Acconciatura Uomo'),
(SELECT idImpiegato from impiegato WHERE idImpiegato='2'));

Every service has a duration time, I need to update the reservation endTime (oraFine) every time an appointment for that reservation is added.

So, for example after I insert into prenotazione with time '10:30:00' I would like to add Service.Duration to prenotazione.EndTime

Options: ReplyQuote


Subject
Views
Written By
Posted
MySQL ERROR: Can't update table SQL trigger
122
March 22, 2020 09:19AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.