mysql create触发器语法错误,使用分隔符$$

7lrncoxx  于 2021-06-17  发布在  Mysql
关注(0)|答案(1)|浏览(397)

我试图通过以下代码防止列上的值重复,但出现语法错误:
如果存在(从第1行的tbl\u baiviet'中选择*

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 ;

真的不知道下班后有什么问题,谢谢你的时间帮我!

t2a7ltrp

t2a7ltrp1#

您已经在tsql(microsoftsqlserver)中编写了一个触发器代码;但是您使用的是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 .. 在触发器内部引发异常。

相关问题