CODEWITHSUNDEEP
mysqltriggers
bellahahaha
bellahahaha

Reputation: 1

MySQL trigger: how to throw an error when condition is not met

DB schema:

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

borrow(cno,bno,borrow_data,return_date)

Sample data in book table:
1 Database System Concepts Abraham 2011 McGrawHill 99.00 5 4
2 Modern Operating Systems Andrew 2011 Pearson 75.00 3 1
4 Computer Network Tanenbaum 2000 Pearson 58.00 4 3\

Sample data in borrow table:
1 1 2018-01-01 2018-01-14
1 2 2018-01-06 2018-01-10
2 2 2018-02-03 2018-02-08
3 4 2018-02-05 2018-03-01

SQL to create the table:

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');

Requirement:
When inserting a new record to borrow table, check if bno exists in book and if stock is greater than 0, if so, update the stock in book table by stock-1 and finish the insert operation; else throw an error

I am not sure why it doesn't work with my code. Any help would be appreciated!!

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

Upvotes: 0

Views: 1545

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 563021

You must use a semicolon (;) to terminate each statement in the body of your trigger.

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;

Terminating statements even within blocks of code is a common feature of many programming languages, such as C, Java, PHP, etc.

You should read the MySQL manual about compound statement syntax, especially this page: https://dev.mysql.com/doc/refman/8.0/en/begin-end.html

Upvotes: 3

Related Questions