A database for inventory and transaction of Apple store has been developed. There are three tables in this database – Inventory, Transaction, Inventory_history. You can use the following script to create the tables. 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) ); 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.
A
in this database – Inventory, Transaction, Inventory_history. You can use the following script to
create the tables.
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)
);
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.
![](/static/compass_v2/shared-icons/check-mark.png)
In this question we have to write a SQL query trigger for the updating of quantity column in the inventory table after a row is inserted in the transaction table.
Let's solve and hope this helps, if you find any query you may utilize threaded quaestion feature.
Trending now
This is a popular solution!
Step by step
Solved in 2 steps
![Blurred answer](/static/compass_v2/solution-images/blurred-answer.jpg)
![Database System Concepts](https://www.bartleby.com/isbn_cover_images/9780078022159/9780078022159_smallCoverImage.jpg)
![Starting Out with Python (4th Edition)](https://www.bartleby.com/isbn_cover_images/9780134444321/9780134444321_smallCoverImage.gif)
![Digital Fundamentals (11th Edition)](https://www.bartleby.com/isbn_cover_images/9780132737968/9780132737968_smallCoverImage.gif)
![Database System Concepts](https://www.bartleby.com/isbn_cover_images/9780078022159/9780078022159_smallCoverImage.jpg)
![Starting Out with Python (4th Edition)](https://www.bartleby.com/isbn_cover_images/9780134444321/9780134444321_smallCoverImage.gif)
![Digital Fundamentals (11th Edition)](https://www.bartleby.com/isbn_cover_images/9780132737968/9780132737968_smallCoverImage.gif)
![C How to Program (8th Edition)](https://www.bartleby.com/isbn_cover_images/9780133976892/9780133976892_smallCoverImage.gif)
![Database Systems: Design, Implementation, & Manag…](https://www.bartleby.com/isbn_cover_images/9781337627900/9781337627900_smallCoverImage.gif)
![Programmable Logic Controllers](https://www.bartleby.com/isbn_cover_images/9780073373843/9780073373843_smallCoverImage.gif)