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.