错误 1064 - 触发器创建期间"附近的语法错误

Question

我收到这个错误：错误代码：1064。您的SQL语法有错误；查看与MySQL服务器版本对应的手册，了解在第7行"附近使用的正确语法

关于以下查询：

DELIMITER //
create trigger UpdateTrigger 
after update
on reserva for each row
begin
IF NEW.reserva_valida = 0 THEN
DELETE FROM companhia_aerea.venda 
where companhia_aerea.venda = NEW.reserva_id;
END IF;
end;
DELIMITER ;

这就是我创建两个相关表的方式：

RESERVA:

CREATE TABLE IF NOT EXISTS `companhia_aerea`.`reserva` (
`reserva_id` INT NOT NULL AUTO_INCREMENT,
`reserva_data_validade` DATE NOT NULL,
`reserva_valida` TINYINT NOT NULL,
`reserva_voo_id` INT NOT NULL,
`reserva_venda_id` INT NOT NULL,
PRIMARY KEY (`reserva_id`),
INDEX `fk_reserva_voo1_idx` (`reserva_voo_id` ASC) VISIBLE,
INDEX `fk_reserva_venda1_idx` (`reserva_venda_id` ASC) VISIBLE,
CONSTRAINT `fk_reserva_voo1`
FOREIGN KEY (`reserva_voo_id`)
REFERENCES `companhia_aerea`.`voo` (`voo_id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_reserva_venda1`
FOREIGN KEY (`reserva_venda_id`)
REFERENCES `companhia_aerea`.`venda` (`venda_id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;

VENDA:

DROP TABLE IF EXISTS companhia_aerea.venda;
CREATE TABLE IF NOT EXISTS `companhia_aerea`.`venda` (
`venda_id` INT NOT NULL AUTO_INCREMENT,
`reserva_id` INT NOT NULL,
`venda_parcelas` INT NOT NULL,
PRIMARY KEY (`venda_id`))
ENGINE = InnoDB;

Answer 1

您需要引用delete的where子句中的列，而不是表。表别名将有所帮助：

DELIMITER //
create trigger UpdateTrigger 
after update
on reserva for each row
begin
IF NEW.reserva_valida = 0 THEN
DELETE v FROM companhia_aerea.venda v
WHERE v.reserva_id = NEW.reserva_id;
END IF;
end;
DELIMITER ;