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.

Database System Concepts

7th Edition

ISBN:9780078022159

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

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

Chapter1: Introduction

Section: Chapter Questions

Problem 1PE

See similar textbooks

Related questions

Concept explainers

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

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

Trending now

This is a popular solution!

Step by step

Solved in 3 steps

SEE SOLUTION Check out a sample Q&A here

Knowledge Booster

Learn more about

Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, computer-science and related others by exploring similar questions and additional content below.