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 Perhaps it could help you understand mysql triggers. — Coenie Richards
– Coenie Richards, Commented Apr 18, 2015 at 20:06
@CoenieRichards What is the select statement inside IF NOT EXISTS doing? — user3508140
– user3508140, Commented Apr 18, 2015 at 20:15
It is checking if a record exists with the new word being inserted into ext_words table. — Coenie Richards
– Coenie Richards, Commented Apr 18, 2015 at 20:18
@CoenieRichards And the count by default is 1? When a new record is inserted? — user3508140
– user3508140, Commented Apr 18, 2015 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
– user3508140, Commented Apr 18, 2015 at 20:45

Abhik Chakraborty · Accepted Answer · 2015-04-18 20:48:01Z

0

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)

answered Apr 18, 2015 at 20:48

Abhik Chakraborty

44.9k6 gold badges56 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

user3508140 Over a year ago

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.

Abhik Chakraborty Over a year ago

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.

user3508140 Over a year ago

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?

Abhik Chakraborty Over a year ago

Exactly that how you can do it.

user3508140 Over a year ago

It's giving me an error when do that. "unknown column new.books.name in where clause". Any ideas?

|

Collectives™ on Stack Overflow

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

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related