Please download the TSQL2008 database from the following link: https://drive.goo
ID: 3731547 • Letter: P
Question
Please download the TSQL2008 database from the following link:
https://drive.google.com/drive/folders/1HpMGn0nm7gn3EGDqI67PfzyumWWwr0Jg
Question: Please read and complete the following steps.
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.
1. Necessary style* Please see below (5 points)
2. Create the appropriate triggers to keep the SalesSummary table in the SalesDataMart
synchronized with activity in the Sales.OrderDetails table in the InsideTSQL2008 database. Three
triggers are expected: insert, update and delete. (24 points)
3. Write queries to perform the following actions. Include the queries in your solution script. (6
point)
a. Duplicate the order and orderdetail information for orderid 10248 (it will need a new
orderid, use 999 as the new order id)**
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
4. Write a query returning the rows for the products included in the orders 10248, 10249, 10250
from SalesSummary in the SalesDataMart. The products have the following ids
(11,42,72,14,51,41,65) Sort the results in descending order by the year, quarter and productid.
The results match expected outcomes (see next page). (6.7 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. Proper formatting of your .sql
file is required.
**Inserting a row with a specific id value into a table that has an IDENITY column requires the following
statement preceding the insert:
SET IDENTITY_INSERT <table_name> ON;
Where <table_name> is the name of the target table.
After completing the desired insert(s) with non-identity generated values you should then issue the
following statement:
SET IDENTITY_INSERT <table_name> OFF;
Where <table_name> is the name of the target table.
Explanation / Answer
Answers:
Screenshot:
--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