1

Say there is a DB with 3 tables

  1. Books table - contain book_id and its title
  2. Inventory - A list of book_ids in the inventory
  3. 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.

2

How to modify this to make the trigger add the title of the book in the logs instead of its id?

You select what you need from the appropriate table:

INSERT INTO logs SELECT title FROM book where book_id = new.book_id;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.