觸發查詢無法執行，語法錯誤

Question

第 5 行有文法錯誤，找不到問題所在 我嘗試更改變數名稱但無濟於事 錯誤查找器也沒有幫助，它只是指向這個「」。 找不到語法錯誤的確切位置。 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

重寫程式碼，它將成功執行查詢

Answer 1

我已經格式化了程式碼。請檢查以下內容並看看它是否有效：

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