Please use the following link to download the database:

https://drive.google.com/drive/folders/1HpMGn0nm7gn3EGDqI67PfzyumWWwr0Jg

Question 8:

Use triggers to update a data mart. 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 didnt 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)