Pertanyaan pencetus tidak boleh dilaksanakan kerana ralat sintaks

Question

Terdapat ralat sintaks dalam baris 5, masalah tidak dapat ditemui Saya cuba menukar nama pembolehubah tetapi tidak berjaya Pencari ralat juga tidak membantu, ia hanya menunjuk kepada "". Lokasi sebenar ralat sintaks tidak dapat ditemui. 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

Tulis semula kod dan ia akan menjalankan pertanyaan dengan jayanya

Answer 1

Saya telah memformatkan kod program. Sila semak perkara berikut dan lihat jika ia berkesan:

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