La requête de déclenchement ne peut pas être exécutée en raison d'une erreur de syntaxe

Question

Il y a une erreur de syntaxe à la ligne 5, le problème est introuvable J'ai essayé de changer les noms des variables mais en vain Le chercheur d'erreurs n'aide pas non plus, il pointe simplement vers ce "". L'emplacement exact de l'erreur de syntaxe est introuvable. MySQL 8.0

CREATE TRIGGER disaster_reports_auto 
AFTER UPDATE 
ON disaster_reports_tbl FOR EACH ROW 
BEGIN
SET @disasterid = (SELECT disaster_reports_id FROM disaster_reports_tbl e WHERE e.disaster_reports_id = NEW.disaster_reports_id);

SET @dtreported = (SELECT date_time_reported FROM disaster_reports_tbl e WHERE e.date_time_reported = NEW.date_time_reported);

SET @timeincident = (SELECT time_incident FROM disaster_reports_tbl e WHERE e.time_incident = NEW.time_incident);

SET @dateincident = (SELECT date_incident FROM disaster_reports_tbl e WHERE e.date_incident = NEW.date_incident);

SET @region = (SELECT region FROM disaster_reports_tbl e WHERE e.region = NEW.region);

SET @subject = (SELECT subject FROM disaster_reports_tbl e WHERE e.subject = NEW.subject);

SET @type = (SELECT incident_type FROM disaster_reports_tbl e WHERE e.incident_type = NEW.incident_type);

SET @scale = (SELECT scale_disaster FROM disaster_reports_tbl e WHERE e.scale_disaster = NEW.scale_disaster);

SET @overview = (SELECT overview FROM disaster_reports_tbl e WHERE e.overview = NEW.overview);

SET @injured = (SELECT injured FROM disaster_reports_tbl e WHERE e.injured = NEW.injured);

SET @missing = (SELECT missing FROM disaster_reports_tbl e WHERE e.missing = NEW.missing);

SET @death = (SELECT deaths FROM disaster_reports_tbl e WHERE e.deaths = NEW.deaths);

SET @province = (SELECT province FROM disaster_reports_tbl e WHERE e.province = NEW.province);

SET @municipality = (SELECT municipality FROM disaster_reports_tbl e WHERE e.municipality = NEW.municipality);

SET @photo = (SELECT photo FROM disaster_reports_tbl e WHERE e.photo = NEW.photo);

SET @valid = (SELECT validity FROM disaster_reports_tbl e WHERE e.validity = NEW.validity);
 
IF NOT EXISTS(SELECT * FROM disaster_reports_verified_tbl n WHERE n.disaster_reports_id = @disasterid) THEN
 IF (@valid = '1') THEN
    INSERT INTO disaster_reports_verified_tbl VALUES ('', @disasterid, @dtreported, @timeincident, @dateincident, @region, @subject, @type, @scale, @overview, @injured, @missing, @death, @province, @municipality, @photo);
    END IF;
END IF;
END

Réécrivez le code et la requête sera exécutée avec succès

Answer 1

J'ai formaté le code du programme. Veuillez vérifier les éléments suivants et voir si cela fonctionne :

CREATE TRIGGER disaster_reports_auto 
AFTER UPDATE 
ON disaster_reports_tbl FOR EACH ROW 
BEGIN
    DECLARE disasterid INT;
    DECLARE dtreported DATETIME;
    DECLARE timeincident TIME;
    DECLARE dateincident DATE;
    DECLARE region VARCHAR(255);
    DECLARE subject VARCHAR(255);
    DECLARE type VARCHAR(255);
    DECLARE scale VARCHAR(255);
    DECLARE overview VARCHAR(255);
    DECLARE injured INT;
    DECLARE missing INT;
    DECLARE death INT;
    DECLARE province VARCHAR(255);
    DECLARE municipality VARCHAR(255);
    DECLARE photo VARCHAR(255);
    DECLARE valid INT;

    SELECT disaster_reports_id INTO disasterid FROM disaster_reports_tbl e WHERE e.disaster_reports_id = NEW.disaster_reports_id;
    SELECT date_time_reported INTO dtreported FROM disaster_reports_tbl e WHERE e.date_time_reported = NEW.date_time_reported;
    SELECT time_incident INTO timeincident FROM disaster_reports_tbl e WHERE e.time_incident = NEW.time_incident;
    SELECT date_incident INTO dateincident FROM disaster_reports_tbl e WHERE e.date_incident = NEW.date_incident;
    SELECT region INTO region FROM disaster_reports_tbl e WHERE e.region = NEW.region;
    SELECT subject INTO subject FROM disaster_reports_tbl e WHERE e.subject = NEW.subject;
    SELECT incident_type INTO type FROM disaster_reports_tbl e WHERE e.incident_type = NEW.incident_type;
    SELECT scale_disaster INTO scale FROM disaster_reports_tbl e WHERE e.scale_disaster = NEW.scale_disaster;
    SELECT overview INTO overview FROM disaster_reports_tbl e WHERE e.overview = NEW.overview;
    SELECT injured INTO injured FROM disaster_reports_tbl e WHERE e.injured = NEW.injured;
    SELECT missing INTO missing FROM disaster_reports_tbl e WHERE e.missing = NEW.missing;
    SELECT deaths INTO death FROM disaster_reports_tbl e WHERE e.deaths = NEW.deaths;
    SELECT province INTO province FROM disaster_reports_tbl e WHERE e.province = NEW.province;
    SELECT municipality INTO municipality FROM disaster_reports_tbl e WHERE e.municipality = NEW.municipality;
    SELECT photo INTO photo FROM disaster_reports_tbl e WHERE e.photo = NEW.photo;
    SELECT validity INTO valid FROM disaster_reports_tbl e WHERE e.validity = NEW.validity;

    IF NOT EXISTS(SELECT * FROM disaster_reports_verified_tbl n WHERE n.disaster_reports_id = disasterid) THEN
        IF (valid = 1) THEN
            INSERT INTO disaster_reports_verified_tbl VALUES ('', disasterid, dtreported, timeincident, dateincident, region, subject, type, scale, overview, injured, missing, death, province, municipality, photo);
        END IF;
    END IF;
END