Please use this link to download the database: https://drive.google.com/drive/fo
ID: 3732725 • Letter: P
Question
Please use this link to download the database:
https://drive.google.com/drive/folders/1HpMGn0nm7gn3EGDqI67PfzyumWWwr0Jg
Question: Please read and complete all the steps below.
The goal is to put insert, update and delete triggers on the Sales.OrderDetails table in the InsideTSQL2008
database. Whenever there is a change to order detail information, update the sales summary table in the
SalesDataMart database. Calculate the total sales value for an ordered item using this formula ((qty *
unitprice) * (1 – discount)).
To reference a table in another database, qualify the table with the database name. For example, if I am in the
InsideTSQL2008 database and I want to reference the SalesSummary table in the SalesDataMart database I
would say:
SELECT * FROM SalesDataMart..SalesSummary;
Notice the double dots, I didn’t specify the table owner. If you know the table is in the dbo schema you could
say:
SELECT * FROM SalesDataMart.dbo.SalesSummary;
Make sure to follow the steps in order below.
1. Necessary style* (10 points)
2. Create a new database called SalesDataMart (0.5 points)
3. Create a SalesSummary table in the SalesDataMart database with the following columns (0.5 points)
a. Year – int
b. Quarter – int
c. ProductId – int
d. TotalSales - money
4. Write a stored proc named SyncSales that processes the current data in the Sales.OrderDetails table in
the InsideTSQL2008 database and updates the SalesDataMart SalesSummary table – be sure that
InsideTSQL2008 database is in its original condition. (4.2 points)
5. Execute your stored proc from #4 (0.5 points)
6. Create the appropriate triggers to keep the SalesSummary table in the SalesDataMart synchronized
with activity in the Sales.OrderDetails table in the InsideTSQL2008 database. (15 points)
7. Perform the following actions (1 point)
a. Duplicate the order and orderdetail information for orderid 10248 (it will need a new orderid)
b. Delete the order and orderdetail information for orderid 10249
c. Update the orderdetail for orderid 10250 by doubling the quantity ordered for each product
8. Write a query returning the rows for the products included in the orders (10248, 10249, 10250) from
SalesSummary in the SalesDataMart. Sort the results in descending order by the year, quarter and
productid. The results match expected outcomes. (10 points)
*Necessary style
Every object that you create (table, trigger, etc.) must include a check for an existing object with the same
name, and if the object exists then drop it. You will also need to include GO commands in the appropriate
locations. A good test is to execute your script multiple times – you should be able to do that without any
errors and always producing the same expected results.
Explanation / Answer
Please find my answer:
--INSERT TRIGGER
USE [InsideTSQL2008]
GO
/****** Object: Trigger [Sales].[insertTrigger] Script Date: 18-03-2018 02:28:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [Sales].[insertTrigger] ON [Sales].[OrderDetails] AFTER INSERT
AS
BEGIN
Insert into SalesDataMart..SalesSummary Select * from inserted
END
GO
--UPDATE TRIGGER
USE [InsideTSQL2008]
GO
/****** Object: Trigger [Sales].[updateTrigger] Script Date: 18-03-2018 02:17:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [Sales].[updateTrigger] ON [Sales].[OrderDetails] AFTER UPDATE
AS
BEGIN
Update SalesDataMart..SalesSummary
Set unitprice = i.unitprice,
qty = i.qty,
discount = i.discount
from SalesDataMart..SalesSummary inner join inserted i on SalesDataMart..SalesSummary.orderid = i.orderid
and SalesDataMart..SalesSummary.productid = i.productid
END
GO
--DELETE TRIGGER
USE [InsideTSQL2008]
GO
/****** Object: Trigger [Sales].[deleteTrigger] Script Date: 18-03-2018 02:23:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [Sales].[deleteTrigger] ON [Sales].[OrderDetails] AFTER DELETE
AS
BEGIN
Delete SalesDataMart..SalesSummary from SalesDataMart..SalesSummary Sales, deleted del where Sales.Orderid = del.orderid
END
GO
***********************
--Duplicate the order information for orderid 10248
SET IDENTITY_INSERT Sales.Orders ON;
Insert into Sales.Orders
(
orderid,custid,empid,orderdate,requireddate,shippeddate,shipperid,freight,shipname,shipaddress,shipcity,shipregion,shippostalcode,shipcountry
)
Select '999',custid,empid,orderdate,requireddate,shippeddate,shipperid,freight,shipname,shipaddress,shipcity,shipregion,shippostalcode,shipcountry
from Sales.Orders where orderid = 10248
SET IDENTITY_INSERT Sales.Orders OFF;
--Duplicate the orderdetail information for orderid 10248
Insert into Sales.OrderDetails
(
orderid,productid,unitprice,qty,discount)
Select '999',productid,unitprice,qty,discount from Sales.OrderDetails where orderid = 10248
--Delete the orderdetail information for orderid 10249
Delete from Sales.OrderDetails where orderid = 10249
--Delete the order information for orderid 10249
Delete from Sales.Orders where orderid = 10249
--Update the orderdetail for orderid 10250 by doubling the quantity ordered for each product
Update Sales.OrderDetails Set qty = (2 * qty) where orderid = 10250
--query returning the rows for the products included in the orders 10248, 10249, 10250
--from SalesSummary in the SalesDataMart.
Select prod.* from Production.Products Prod INNER JOIN SalesDataMart..SalesSummary Sales ON Prod.productid = Sales.Productid
where orderid in (10248, 10249, 10250) ORDER BY Productid desc
Please upvote if it helped you!!