Trigger- Update columns in one table on inserting in another table

Question

So, I have 2 tables.

One is books, that has the following fields. accno(Accession number), name(book name), status(Issued/Not Issued)

Second is total, that has the following fields. name(book name), count(Number of books that are 'Not Issued' in the books table

I have a form that adds books in the books table, and the default status is 'Not Issued'. I also have a form that issued books i.e. it changes the status to 'Issued'. And I have a form that returns the books i.e. it changes the status back to 'Not Issued'.

I'm looking for a trigger that updates the count in the total table everytime the bookstable is updated. Count is the number of books that are available(Not Issued) in the books table, and it is different for different books(book names).

I am totally new to triggers. I have looked arond, but I can't seem to figure a way to implement this.

Any help is appreciated. Thank you.

Tried this post yet? [link](http://stackoverflow.com/questions/15975877/mysql-trigger-on-insert-update-events) Perhaps it could help you understand mysql triggers. — Coenie Richards, Apr 18 '15 at 20:06
@CoenieRichards What is the select statement inside IF NOT EXISTS doing? — user3508140, Apr 18 '15 at 20:15
It is checking if a record exists with the new word being inserted into ext_words table. — Coenie Richards, Apr 18 '15 at 20:18
@CoenieRichards And the count by default is 1? When a new record is inserted? — user3508140, Apr 18 '15 at 20:31
@CoenieRichards I am starting to get my head around this. What about when i update the status column? I assume we would use AFTER UPDATE.. But i can't think about a way to go about it. — user3508140, Apr 18 '15 at 20:45
as per the new answer below, that should help. You have to create a new trigger for update and insert. — Coenie Richards, Apr 18 '15 at 20:50
See this post relating to 2 triggers and how to clean them up into a new stored procedure [link](http://stackoverflow.com/a/25585622/1143411) — Coenie Richards, Apr 18 '15 at 20:52

score 0 · Accepted Answer · answered Apr 18 '15 at 20:48

Looks like its an inventory system, so every time a new book comes into the library you store the inventory number into the total table and when a book is issued against the accnum the inventory is decreased by one and then its returned its increased by one.

In this case the following trigger should do the job

delimiter //
create trigger book_available after update on books
for each row 
begin
 if new.status = 'Issued' then 
  update total set `count` = `count` - 1 where name = new.book_name ;
 else
  update total set `count` = `count` + 1 where name = new.book_name ;
 end if ;
 delimiter ;

Here is a test case

mysql> select * from books ;
+--------+-----------+------------+
| accnum | book_name | status     |
+--------+-----------+------------+
|      1 | AA        | Not Issued |
|      2 | AA        | Issued     |
|      3 | BB        | Not Issued |
+--------+-----------+------------+
3 rows in set (0.00 sec)


mysql> select * from total ;
+------+-------+
| name | count |
+------+-------+
| AA   |    20 |
| BB   |    30 |
+------+-------+
2 rows in set (0.00 sec)

mysql> delimiter //

mysql> create trigger book_available after update on books
    -> for each row 
    -> begin
    ->  if new.status = 'Issued' then 
    ->   update total set `count` = `count` - 1 where name = new.book_name ;
    ->  else
    ->   update total set `count` = `count` + 1 where name = new.book_name ;
    ->  end if ;
    -> end ;//
Query OK, 0 rows affected (0.13 sec)
mysql> delimiter ;

mysql> update books set status = 'Issued' where accnum = 1 ;
Query OK, 1 row affected (0.08 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from total ;
+------+-------+
| name | count |
+------+-------+
| AA   |    19 |
| BB   |    30 |
+------+-------+
2 rows in set (0.00 sec)


mysql> update books set status = 'Not Issued' where accnum = 1 ;
Query OK, 1 row affected (0.04 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from total ;
+------+-------+
| name | count |
+------+-------+
| AA   |    20 |
| BB   |    30 |
+------+-------+
2 rows in set (0.00 sec)

I understand. One question.. Do i still have to create a trigger that adds a new record in the total table when a new book comes, a new book name i mean? AFTER INSERT trigger, that is. — user3508140, Apr 18 '15 at 20:56
If you add a new book into the system then you should add the number of books available into the `total` table, thats your inventory table. Now when any book is issued or returned the trigger will update the total value so that at given time you know how many books are available. Also say right now you have 20 copies of book `AA` and another 10 copy is purchased in that case you should update the total table to current `count + 10` , the idea is how we manage the inventory system. — Abhik Chakraborty, Apr 18 '15 at 20:59
Got it. Also i have the field name as 'name' in both the tables. How do i edit the trigger? total.name=new.books.name? — user3508140, Apr 18 '15 at 21:01
It's giving me an error when do that. "unknown column new.books.name in where clause". Any ideas? — user3508140, Apr 18 '15 at 21:17
no not that way, you need to do as `update total set count = count - 1,name= new. book_name where name = new.book_name ` same while doing the increment. — Abhik Chakraborty, Apr 18 '15 at 21:19

Trigger- Update columns in one table on inserting in another table

1 Answers1