Answered: Hello! I was given those tables(no…

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

Hello! I was given those tables(no data) and I am supossed to write sql code for the following. But I looks like I have a syntax error or there is something I have wrote wrong. (see images)

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

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

x
#1
CREATE TRIGGER insert_inventory
AFTER INSERT ON Inventory
FOR EACH ROW
BEGIN
END;
#3
INSERT INTO Inventory_history (itemid, action, oldprice, time)
VALUES (NEW.itemid, 'add an item', NULL, NOW());
CREATE TRIGGER change_price
BEFORE UPDATE ON Inventory
FOR EACH ROW
BEGIN
IF NEW.price != OLD.price THEN
INSERT INTO Inventory_history (itemid, action, oldprice, time)
VALUES (OLD.itemid, 'price change', OLD.price, NOW());
END IF;
END;

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

Here, We have to create two SQL Triggers. Requirements for those are as following:

Given table schema:

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)
);

What is a SQL Trigger?

A SQL trigger is a special type of stored procedure that is automatically executed in response to certain database events, such as the insertion, deletion, or modification of data in a table. Triggers are attached to a specific table and are defined to execute in response to a specific database event.

Step by step

Solved in 2 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.

Similar questions