Say there is a DB with 3 tables
- Books table - contain
- Inventory - A list of
book_ids in the inventory
- Logs - A list of log strings.
Every time I add a new
book_id to the inventory, I want the title of the book to be inserted in the logs table with a trigger. Here is the code, that'll insert the
book_id to the logs. How to modify this to make the trigger add the title of the book in the logs instead of its id?
CREATE TABLE book ( book_id INT AUTO_INCREMENT PRIMARY KEY, title VARCHAR(32) ); CREATE TABLE inventory ( book_id INT, FOREIGN KEY (book_id) REFERENCES book(book_id) ); CREATE TABLE logs ( log_message VARCHAR(64) ); CREATE TRIGGER book_added AFTER INSERT ON inventory FOR EACH ROW INSERT INTO logs values(new.book_id);
Note: I'm new to SQL and these tables are altered and simplified so ignore the bad design.