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.