Answered: There are three tables in this database (see image). Write MYSQL code for: (1) Create a trigger “insert_inventory” on table “Inventory”. The trigger is fired…

Database System Concepts

7th Edition

ISBN: 9780078022159

Author: Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Publisher: McGraw-Hill Education

See similar textbooks

Concept explainers

SQL

SQL stands for Structured Query Language, is a form of communication that uses queries structured in a specific format to store, manage & retrieve data from a relational database.

Queries

A query is a type of computer programming language that is used to retrieve data from a database. Databases are useful in a variety of ways. They enable the retrieval of records or parts of records, as well as the performance of various calculations prio…

Question

There are three tables in this database (see image). Write MYSQL code for:

(1) Create a trigger “insert_inventory” on table “Inventory”. The trigger is fired after a
row is inserted in table “Inventory”. After a row is inserted in table “inventory”, the
“itemid”, the insertion time, and the action is inserted in table “Inventory_history”.
The action is set to ‘add an item’. The oldprice is set to null.
Test your trigger by inserting a row into Inventory and displaying the contents of
Inventory_history.
(2) Create a trigger “change_quantity” on table “Transaction”. The trigger is fired after
a row is inserted in table “Transaction”. After a row is inserted in table
“Transaction”, update the “quantity” in table “Inventory”. For example, if 3 iWatch
are sold, then the quantity of iWatch in table “Inventory” is decreased by 3.
Test your trigger by inserting a row into Transaction and displaying the contents of
the relevant row in Inventory.
(3) Create a trigger “change_price” on table “Inventory”. The trigger is fired before a
change is made to the “Inventory” table. Before the price of an item is changed, the
“itemid”, the item’s old price, the action, and the time of change are inserted into
the table “Inventory_history”. The action is set to “price change”.
Test your trigger by updating a row in Inventory and displaying the contents of
Inventory_history.

create table Inventory (
itemid varchar(20) primary key, name varchar(30),
price decimal(6,2),
quantity int
);
create table Transaction (
transid int auto_increment primary key,
itemid varchar(20),
quantity int,
time datetime,
foreign key (itemid) references Inventory (itemid)
);
create table Inventory_history (
id int auto_increment primary key,
itemid varchar(20),
action varchar(20),
oldprice decimal(6,2),
time datetime,
foreign key (itemid) references Inventory(itemid)
);

Expert Solution

Step 1

(1)

MYSQL code for creating trigger “insert_inventory” on table “Inventory”:

----------------------------------------------------------------------------------------

CREATE TRIGGER insert_inventory
AFTER INSERT ON Inventory
FOR EACH ROW
BEGIN
INSERT INTO Inventory_history (itemid, action, oldprice, time)
VALUES (NEW.itemid, 'add an item', NULL, NOW());
END;

----------------------------------------------------------------------------------------

To test the trigger, you can insert a row into the Inventory table:

----------------------------------------------------------------------------------------

INSERT INTO Inventory (itemid, name, price, quantity)
VALUES ('1001', 'iPhone 12', 799.99, 50);

----------------------------------------------------------------------------------------

Then, you can display the contents of the Inventory_history table to verify that a new row has been inserted:

----------------------------------------------------------------------------------------

SELECT * FROM Inventory_history;

----------------------------------------------------------------------------------------

The output will be like this:

----------------------------------------------------------------------------------------

id	itemid	action	oldprice	time
1	1001	add an item	NULL	2023-02-24 10:00:00

----------------------------------------------------------------------------------------

Related questions

Concept explainers