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.

My Personal Notes arrow_drop_up

Save