How to Write a sequence of SQL Statements that include a Stored Trigger to Create an Audit Trail for Updating and Deleting Rows in a Table in your version of the Sales Rep - Customer - Invoice Database. Write a Stored Trigger linked updating a table row in the Enterprise Contracting Company Database. Enterprise Contracting Company Database Write a Stored Trigger linked deleting a table row in the Enterprise Contracting Company Database For each Trigger, a copy of the row before the update or delete operation should be placed in one or more audit tables that keep track of the history of the data. Your Script must contain SQL Statements that Test and Verify that the Triggers are working correctly. A Delete Statement removing one or more rows from a table An Update Statement changing one or more rows from a table CREATE TABLE Statement to create your version of the audit table storing the versions of the data before being deleted or updated SELECT Statements visually confirming the success of the operations SELECT Statement listing the contents of the audit table SELECT Statement listing the updated state of the individual record updated The Script : -- creating the RegionStore DataBase DROP DATABASE IF EXISTS RegionStore; CREATE DATABASE RegionStore; USE RegionStore; -- creating the Region table CREATE TABLE Region ( regionID VARCHAR(10), regionName VARCHAR(50), supervisor VARCHAR(50), CONSTRAINT PRIMARY KEY (regionID) ); -- creating the store table CREATE TABLE Store ( storeID VARCHAR (10), storeAddress VARCHAR (50), phone VARCHAR (10), Manager VARCHAR (10), regionID_FK VARCHAR (10), CONSTRAINT PRIMARY KEY (storeID), CONSTRAINT FOREIGN KEY (regionID_FK) REFERENCES REGION (regionID) ); -- creating the Employee table CREATE TABLE Employee ( empNum VARCHAR (10), empName VARCHAR (50), hireDate DATE, birthDate Date, socSecNum CHAR(9), storeID_FK VARCHAR(10), CONSTRAINT PRIMARY KEY (empNum), CONSTRAINT FOREIGN KEY (storeID_FK) REFERENCES Store(storeID) ); -- creating the Supplier table CREATE TABLE Supplier ( supplierID VARCHAR (10), supplierName VARCHAR (50), contact VARCHAR (50), phone CHAR (10), email VARCHAR (50), CONSTRAINT PRIMARY KEY (supplierID) ); -- creating the Supplies table CREATE TABLE Supplies ( supplierID_FK VARCHAR (10), storeID_FK VARCHAR (10), startDate DATE, CONSTRAINT PRIMARY KEY (supplierID_FK,storeID_FK ) ); -- creating the StateOfRegion table CREATE TABLE StateOfRegion ( stateCode CHAR(2), stateName VARCHAR(50), regionID_FK VARCHAR(10), statePopulation INT, CONSTRAINT PRIMARY KEY (stateCode), CONSTRAINT FOREIGN KEY (regionID_FK ) REFERENCES REGION(regionID) ); --INSERTING information INTO the Region table INSERT INTO region (regionID,regionName,supervisor) VALUES ('001','Alabama','Henry'); INSERT INTO Region (regionID,regionName,supervisor) VALUES ('003','wellington','katty'); INSERT INTO Region (regionID,regionName,supervisor) VALUES ('004','hamilton','ross'); INSERT INTO Region (regionID,regionName,supervisor) VALUES ('005','auckland','robert');

How to Write a sequence of SQL Statements that include a Stored Trigger to Create an Audit Trail for Updating and Deleting Rows in a Table in your version of the Sales Rep - Customer - Invoice Database. Write a Stored Trigger linked updating a table row in the Enterprise Contracting Company Database. Enterprise Contracting Company Database Write a Stored Trigger linked deleting a table row in the Enterprise Contracting Company Database For each Trigger, a copy of the row before the update or delete operation should be placed in one or more audit tables that keep track of the history of the data. Your Script must contain SQL Statements that Test and Verify that the Triggers are working correctly. A Delete Statement removing one or more rows from a table An Update Statement changing one or more rows from a table CREATE TABLE Statement to create your version of the audit table storing the versions of the data before being deleted or updated SELECT Statements visually confirming the success of the operations SELECT Statement listing the contents of the audit table SELECT Statement listing the updated state of the individual record updated The Script : -- creating the RegionStore DataBase DROP DATABASE IF EXISTS RegionStore; CREATE DATABASE RegionStore; USE RegionStore; -- creating the Region table CREATE TABLE Region ( regionID VARCHAR(10), regionName VARCHAR(50), supervisor VARCHAR(50), CONSTRAINT PRIMARY KEY (regionID) ); -- creating the store table CREATE TABLE Store ( storeID VARCHAR (10), storeAddress VARCHAR (50), phone VARCHAR (10), Manager VARCHAR (10), regionID_FK VARCHAR (10), CONSTRAINT PRIMARY KEY (storeID), CONSTRAINT FOREIGN KEY (regionID_FK) REFERENCES REGION (regionID) ); -- creating the Employee table CREATE TABLE Employee ( empNum VARCHAR (10), empName VARCHAR (50), hireDate DATE, birthDate Date, socSecNum CHAR(9), storeID_FK VARCHAR(10), CONSTRAINT PRIMARY KEY (empNum), CONSTRAINT FOREIGN KEY (storeID_FK) REFERENCES Store(storeID) ); -- creating the Supplier table CREATE TABLE Supplier ( supplierID VARCHAR (10), supplierName VARCHAR (50), contact VARCHAR (50), phone CHAR (10), email VARCHAR (50), CONSTRAINT PRIMARY KEY (supplierID) ); -- creating the Supplies table CREATE TABLE Supplies ( supplierID_FK VARCHAR (10), storeID_FK VARCHAR (10), startDate DATE, CONSTRAINT PRIMARY KEY (supplierID_FK,storeID_FK ) ); -- creating the StateOfRegion table CREATE TABLE StateOfRegion ( stateCode CHAR(2), stateName VARCHAR(50), regionID_FK VARCHAR(10), statePopulation INT, CONSTRAINT PRIMARY KEY (stateCode), CONSTRAINT FOREIGN KEY (regionID_FK ) REFERENCES REGION(regionID) ); --INSERTING information INTO the Region table INSERT INTO region (regionID,regionName,supervisor) VALUES ('001','Alabama','Henry'); INSERT INTO Region (regionID,regionName,supervisor) VALUES ('003','wellington','katty'); INSERT INTO Region (regionID,regionName,supervisor) VALUES ('004','hamilton','ross'); INSERT INTO Region (regionID,regionName,supervisor) VALUES ('005','auckland','robert');

A Guide to SQL

9th Edition

ISBN:9781111527273

Author:Philip J. Pratt

Publisher:Philip J. Pratt

Chapter7: Database Administration

Section: Chapter Questions

Problem 4SCG: Write, but do not execute, the commands to grant the following privileges: a. User Oliver must be...

See similar textbooks

Related questions

Concept explainers

Question

Write a Stored Trigger linked updating a table row in the Enterprise Contracting Company Database. Enterprise Contracting Company Database
Write a Stored Trigger linked deleting a table row in the Enterprise Contracting Company Database

For each Trigger, a copy of the row before the update or delete operation should be placed in one or more audit tables that keep track of the history of the data.

Your Script must contain SQL Statements that Test and Verify that the Triggers are working correctly.

A Delete Statement removing one or more rows from a table
An Update Statement changing one or more rows from a table

CREATE TABLE Statement to create your version of the audit table storing the versions of the data before being deleted or updated

SELECT Statements visually confirming the success of the operations

SELECT Statement listing the contents of the audit table

SELECT Statement listing the updated state of the individual record updated

The Script :

-- creating the RegionStore DataBase

DROP DATABASE IF EXISTS RegionStore;

CREATE DATABASE RegionStore;

USE RegionStore;

-- creating the Region table

CREATE TABLE Region

(

regionID VARCHAR(10),

regionName VARCHAR(50),

supervisor VARCHAR(50),

CONSTRAINT PRIMARY KEY (regionID)

);

-- creating the store table

CREATE TABLE Store

(

storeID VARCHAR (10),

storeAddress VARCHAR (50),

phone VARCHAR (10),

Manager VARCHAR (10),

regionID_FK VARCHAR (10),

CONSTRAINT PRIMARY KEY (storeID),

CONSTRAINT FOREIGN KEY (regionID_FK)

REFERENCES REGION (regionID)

);

-- creating the Employee table

CREATE TABLE Employee

(

empNum VARCHAR (10),

empName VARCHAR (50),

hireDate DATE,

birthDate Date,

socSecNum CHAR(9),

storeID_FK VARCHAR(10),

CONSTRAINT PRIMARY KEY (empNum),

CONSTRAINT FOREIGN KEY (storeID_FK)

REFERENCES Store(storeID)

);

-- creating the Supplier table

CREATE TABLE Supplier

(

supplierID VARCHAR (10),

supplierName VARCHAR (50),

contact VARCHAR (50),

phone CHAR (10),

email VARCHAR (50),

CONSTRAINT PRIMARY KEY (supplierID)

);

-- creating the Supplies table

CREATE TABLE Supplies

(

supplierID_FK VARCHAR (10),

storeID_FK VARCHAR (10),

startDate DATE,

CONSTRAINT PRIMARY KEY (supplierID_FK,storeID_FK )

);

-- creating the StateOfRegion table

CREATE TABLE StateOfRegion

(

stateCode CHAR(2),

stateName VARCHAR(50),

regionID_FK VARCHAR(10),

statePopulation INT,

CONSTRAINT PRIMARY KEY (stateCode),

CONSTRAINT FOREIGN KEY (regionID_FK )

REFERENCES REGION(regionID)

);

--INSERTING information INTO the Region table

INSERT INTO region (regionID,regionName,supervisor)

VALUES ('001','Alabama','Henry');

INSERT INTO Region (regionID,regionName,supervisor)

VALUES ('003','wellington','katty');

INSERT INTO Region (regionID,regionName,supervisor)

VALUES ('004','hamilton','ross');

INSERT INTO Region (regionID,regionName,supervisor)

VALUES ('005','auckland','robert');

Expert Solution

Trending now

This is a popular solution!

Step by step

Solved in 3 steps with 4 images

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.