Navicat : Make a view and some field can be updated each time inputted

Question

i have three table 1. tbl_book - id_book (PK) - book - qty

2. tbl_borrow_book

   • id_borrow (PK)
   • id_book
   • qty_borrow

3. tbl_book_return

   • id_return (PK)
   • id_borrow

ok, here the question.

i want to make a view, when i input data in tbl_borrow_book then qty in tbl_book will be decreased. and when i input data in tbl_book_return with the same book which was borrowed, then qty in tbl_book will be increased by the same qty.

thanks in advanced...sorry for bad english

Answer 1

Although you tagged it as a JavaScript, but I guess you are asking a SQL question. So I am going to answer it with a SQL solution

You can achieve your goal by using mysql trigger. Here is examples.

The following trigger will reduce the qty in tbl_book after you add any records to tbl_borrow_book:

CREATE TRIGGER `book_borrowed` AFTER INSERT ON `tbl_borrow_book` 
FOR EACH ROW

BEGIN

UPDATE `tbl_book` SET `qty` = `qty` - NEW.`qty_borrow` WHERE `id_book` = NEW.`id_book` LIMIT 1;

END; 

And the following trigger will increase the qty of book in tbl_book when there is any data inserted to tbl_book_return:

CREATE TRIGGER `book_returned` AFTER INSERT ON `tbl_book_return` 
FOR EACH ROW 

BEGIN

DECLARE number_of_book, book_id INT;

SET number_of_book = 
(SELECT
    `tbl_borrow_book`.`qty_borrow`
FROM 
    `tbl_borrow_book` 
INNER JOIN `tbl_book_return` ON `tbl_borrow_book`.`id_borrow` = `tbl_book_return`.`id_borrow` 
WHERE
    `tbl_borrow_book`.`id_borrow` = NEW.`id_borrow` LIMIT 1);

SET book_id = 
(SELECT
    `tbl_borrow_book`.`id_book`
FROM 
    `tbl_borrow_book` 
INNER JOIN `tbl_book_return` ON `tbl_borrow_book`.`id_borrow` = `tbl_book_return`.`id_borrow` 
WHERE
    `tbl_borrow_book`.`id_borrow` = NEW.`id_borrow` LIMIT 1);

UPDATE `tbl_book` SET `qty` = `qty` + number_of_book WHERE `id_book` = book_id;

END;

Please note that although the 2nd trigger works, however, it is not optimized. The two SELECT statements may be combined into one. I will update this answer if I know how to do so.