SQL Trigger | Book Management Database
Last Updated :
01 Feb, 2019
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.
Share your thoughts in the comments
Please Login to comment...