SQL Trigger | Book Management Database

Prerequisite – SQL Trigger | Student Database
For example, given Library Book Management database schema with Student database schema. In these databases, if any student borrows a book from library then the count of that specified book should be decremented. To do so,

Suppose the schema with some data,

mysql> select * from book_det; 
+-----+-------------+--------+ 
| bid | btitle      | copies | 
+-----+-------------+--------+ 
|   1 | Java        |     10 | 
|   2 | C++         |      5 | 
|   3 | MySql       |     10 | 
|   4 | Oracle DBMS |      5 | 
+-----+-------------+--------+ 
4 rows in set (0.00 sec) 

mysql> select * from book_issue; 
+------+------+--------+ 
| bid  | sid  | btitle | 
+------+------+--------+ 
1 row in set (0.00 sec)

To implement such procedure, in which if the system inserts the data into the book_issue database a trigger should automatically invoke and decrements the copies attribute by 1 so that a proper track of book can be maintained.

Trigger for the system –

create trigger book_copies_deducts 
after INSERT 
on book_issue 
for each row 
update book_det set copies = copies - 1 where bid = new.bid; 

Above trigger, will be activated whenever an insertion operation performed in a book_issue database, it will update the book_det schema setting copies decrements by 1 of current book id(bid).

Results –

mysql> insert into book_issue values(1, 100, "Java");
Query OK, 1 row affected (0.09 sec) 

mysql> select * from book_det; 
+-----+-------------+--------+ 
| bid | btitle      | copies | 
+-----+-------------+--------+ 
|   1 | Java        |      9 | 
|   2 | C++         |      5 | 
|   3 | MySql       |     10 | 
|   4 | Oracle DBMS |      5 | 
+-----+-------------+--------+ 
4 rows in set (0.00 sec) 

mysql> select * from book_issue; 
+------+------+--------+ 
| bid  | sid  | btitle | 
+------+------+--------+ 
|    1 |  100 | Java   | 
+------+------+--------+ 
1 row in set (0.00 sec)

As above results show that as soon as data is inserted, copies of the book deducts from the book schema in the system.