MySQL触发器:如何在不满足条件时抛出错误

Question

DB模式：

book(bno,title, author,year,press,price,total,stock)

borrow(cno,bno,borrow_data,return_date)

书表中的样本数据：

1数据库系统概念-亚伯拉罕2011年McGrawHill 99.005 4

2现代操作系统Andrew 2011 Pearson 75.00 3 1

4计算机网络Tanenbaum 2000 Pearson 58.00 4 3\

借阅表中的样本数据：

1 2018-01 2018-01 2018-01

2018-01-06 2018-01-10

2 2018-02-03 2018-02-08

4 2018-02-05 2018-03-01

创建表的SQL：

CREATE TABLE book (bno INT NOT NULL AUTO_INCREMENT,
                   title VARCHAR(100),
                   author VARCHAR(40),
                   year YEAR,
                   press VARCHAR(60),
                   price DECIMAL(6,2),
                   total INT CHECK ((total >= 0)),
                   stock INT CHECK ((stock >= 0)),
                   PRIMARY KEY (bno));

CREATE TABLE borrow (cno INT,
                     bno INT,
                     borrow_date DATE,
                     return_date DATE,
                     CHECK (borrow_date < return_date));

INSERT INTO book
VALUES (1,'Database System Concepts','Abraham',2011,'McGrawHill',99.00,5,4),
       (2,'Modern Operating Systems','Andrew',2009,'Pearson',75.00,3,1),
       (3,'Computer Network','Tanenbaum',2000,'Pearson',58.00,4,3);
   
INSERT INTO borrow
VALUES (1,1,'2018-1-1','2018-1-14'),
       (1,2,'2018-1-6','2018-1-10'),
       (2,2,'2018-2-3','2018-2-8'),
       (3,3,'2018-2-5','2018-3-1'); 

要求：

当插入新记录以借入表时，检查bno是否存在于图书中，如果库存大于0，则按stock-1更新图书表中的库存，并完成插入操作；否则抛出错误。

我不知道为什么它不适用于我的代码。任何帮助都将不胜感激！！

CREATE TRIGGER borrow_insert_trigger
BEFORE INSERT ON borrow
FOR EACH ROW
BEGIN
  IF EXISTS (SELECT bno 
             FROM book
             WHERE NEW.bno = book.bno) 
  AND (SELECT stock
       FROM book
       WHERE NEW.bno = book.bno)>0
  THEN UPDATE book
       SET stock = stock-1
       WHERE NEW.bno = book.bno
  ELSE SIGNAL SQLSTATE '45000'
       SET MESSAGE_TEXT = 'Book doesn\'t exist or out of stock'
  END IF
END;

The error:
You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'ELSE SIGNAL SQLSTATE '45000'
   SET MESSAGE_TEXT = 'Book doesn\'t exist or o' at line 14

Answer 1

必须使用分号(;)来终止触发器正文中的每个语句。

CREATE TRIGGER borrow_insert_trigger
BEFORE INSERT ON borrow
FOR EACH ROW
BEGIN
  IF EXISTS (SELECT bno
             FROM book
             WHERE NEW.bno = book.bno)
  AND (SELECT stock
       FROM book
       WHERE NEW.bno = book.bno)>0
  THEN UPDATE book
       SET stock = stock-1
       WHERE NEW.bno = book.bno;
                               ^ here
  ELSE SIGNAL SQLSTATE '45000'
       SET MESSAGE_TEXT = 'Book doesn\'t exist or out of stock';
                                                               ^ here
  END IF;
        ^ here, because IF/THEN/ELSE/END IF counts as a compound statement
END;

即使在代码块中终止语句也是许多编程语言的共同特性，例如C、Java、PHP等。

您应该阅读有关复合语句语法的MySQL手册，特别是本页：https://dev.mysql.com/doc/refman/8.0/en/begin-end.html。