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

सर्वशक्तिशाली इकलौता

If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to contribute@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.

Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.



Improved By : mayankchauhan



Article Tags :
Practice Tags :


Be the First to upvote.


Please write to us at contribute@geeksforgeeks.org to report any issue with the above content.