Question

Create a table book_store with columns Book_id VARCHAR(255) NOT NULL,

Book_Name VARCHAR(255) NOT NULL,

Book_genre VARCHAR(255) NOT NULL,

Status VARCHAR(255) NOT NULL,

PRIMARY KEY (Book_id)

Create a table book with columns Book_id VARCHAR(255) NOT NULL,

Book_Name VARCHAR(255) NOT NULL,

Book_release integer,

Book_price integer ,

Publisher Varchar(10),

Book_genre VARCHAR(255) NOT NULL,

PRIMARY KEY (Book_id)

CREATE TABLE price_logs with columns id INT(11) NOT NULL AUTO_INCREMENT,

Book_id VARCHAR(255) NOT NULL,

Old_Book_price DOUBLE NOT NULL,

New_Book_price DOUBLE NOT NULL,

updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

updated_by varchar(30) NOT NULL,

PRIMARY KEY (id),

KEY Book (Book_id),

CONSTRAINT price_logs_ibfk_1 FOREIGN KEY (Book_id) REFERENCES Book (Book_id) ON DELETE CASCADE ON UPDATE CASCADE

Update Trigger: Create a trigger to record/insert the price in price_logs table before updating the book table . Also record the user who updated the price.

Answer 1

Hello, Student below is your sql queries to your questions, Please make sure to like my work, it really motivates me.

1) Create a table book_store:-

CREATE TABLE book_store(

Book_id VARCHAR(255) NOT NULL,

Book_Name VARCHAR(255) NOT NULL,

Book_genre VARCHAR(255) NOT NULL,

Status VARCHAR(255) NOT NULL,

PRIMARY KEY (Book_id)

);

2)

CREATE TABLE book(

Book_id VARCHAR(255) NOT NULL,

Book_Name VARCHAR(255) NOT NULL,

Book_release integer,

Book_price integer ,

Publisher Varchar(10),

Book_genre VARCHAR(255) NOT NULL,

PRIMARY KEY (Book_id)

);

3)CREATE TABLE price_logs:-

CREATE TABLE price_logs(

id INT(11) NOT NULL AUTO_INCREMENT,

Book_id VARCHAR(255) NOT NULL,

Old_Book_price DOUBLE NOT NULL,

New_Book_price DOUBLE NOT NULL,

updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

updated_by varchar(30) NOT NULL,

PRIMARY KEY (id),

KEY Book (Book_id),

CONSTRAINT price_logs_ibfk_1 FOREIGN KEY (Book_id) REFERENCES Book (Book_id) ON DELETE CASCADE ON UPDATE CASCADE

);

Update Trigger:

CREATE TRIGGER price_logs_trigger

BEFORE INSERT

ON price_logs FOR EACH ROW

BEGIN

  DECLARE uUser varchar(50)

SELECT USER( ) INTO uUser;

END;

Again, Please do not forget to like my work>3

Thank you!!