Mysql创建触发器语法错误使用DELIMITER $$

Question

我试图通过这段代码防止列上的重复值，但出现语法错误:

if exists ( select * from tbl_baivietsp t ' at line 1

DELIMITER $$
CREATE TRIGGER key_as_linksp ON tbl_baivietsp
AFTER INSERT
AS
if exists ( select * from tbl_baivietsp t inner join INSERTED i on i.LINK_SP 
LIKE CONCAT('%', t.LINK_SP ,'%'))
begin
  rollback TRANSACTION
  RAISERROR ('Duplicate Data', 16, 1);
end
go
$$
DELIMITER ;

下类后真的不知道问题出在哪里，感谢您花时间帮助我!

Answer 1

你已经用 TSQL (Microsoft SQL Server) 编写了一个触发器代码；但是你正在使用 MySQL。建议查看触发器文档:https://dev.mysql.com/doc/refman/8.0/en/trigger-syntax.html

DELIMITER $$

/* AFTER INSERT comes before the table name on which Trigger is being created */
CREATE TRIGGER key_as_linksp AFTER INSERT ON tbl_baivietsp

FOR EACH ROW BEGIN /* Instead of AS, Trigger block starts with FOR EACH ROW BEGIN */

  IF EXISTS ( select 1 from tbl_baivietsp t 
              inner join INSERTED i 
                on i.LINK_SP LIKE CONCAT('%', t.LINK_SP ,'%')) THEN /* THEN is missing */
      /* Throw Exception */
      SIGNAL SQLSTATE '45000' 
        SET MESSAGE_TEXT = 'Duplicate Data';
  END IF; /* we use END IF instead of END to end an IF block */

END $$  /* Trigger block ends with END clause */
DELIMITER ;

对于 MySQL，我们使用 SIGNAL .. SET MESSAGE_TEXT ..在触发器内抛出异常。