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.
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
