触发器中的MySql语法错误:我的触发器代码中的错误是什么

Question

我有一个基本的触发器语法错误。我是mysql的初学者。请帮帮我。我有两张桌子。

表1-

MariaDB [lab5]> create table library_2
-> (id int AUTO_INCREMENT primary key,
-> Book_name varchar(20),
-> Details varchar(50));

表2-

MariaDB [lab5]> create table library_audit2
-> (id int AUTO_INCREMENT primary key,
-> Book_Name varchar(20) not null,
-> Details varchar(50) default null,
-> change_date date,
-> library_id int,
-> foreign key(library_id) REFERENCES library_2(id));

触发器-

MariaDB [lab5]> DELIMITER $$
MariaDB [lab5]> create trigger BeforeLibraryDelete
-> BEFORE DELETE
-> ON library_2 FOR EACH ROW
-> BEGIN
-> delete from library_audit2 lib where lib.library_id=OLD.id;
-> END $$

错误-

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'lib where lib.library_id = :old.id;
END' at line 5

第5行的错误是什么？

Answer 1

您的删除需要指定要从中删除的表：

delete lib
-------^ the table alias goes between the `delete` and the `from`
from library_audit2 lib
where lib.library_id = OLD.id;