· 4 years ago · May 17, 2021, 04:10 PM
1--RESTORE DATABASE AdventureWorks2017 FROM DISK = 'D:\Viewless Account\Desktop\SQL Homework'
2--1--
3IF EXISTS
4(
5 SELECT name FROM master.dbo.sysdatabases
6 WHERE name = 'School2021'
7)
8 BEGIN
9 PRINT('School2021 database already exists.')
10 END
11ELSE
12 BEGIN
13 CREATE DATABASE [School2021]
14 PRINT('School2021 database has been Created.')
15 END
16
17
18
19--2--
20USE School2021
21IF OBJECT_ID('dbo.Sales', 'U') IS NULL
22
23CREATE TABLE School2021.dbo.Sales(
24 SalesOrderID INT NOT NULL,
25 SalesOrderDetailID INT IDENTITY(1,1) NOT NULL,
26 CarrierTrackingNumber NVARCHAR(25) NULL,
27 OrderQty SMALLINT NOT NULL,
28 ProductID INT NOT NULL,
29 SpecialOfferID INT NOT NULL,
30 UnitPrice MONEY NOT NULL,
31 UnitPriceDiscount MONEY NOT NULL,
32 LineTotal AS (isnull(([UnitPrice]*((1.0)-[UnitPriceDiscount]))*[OrderQty],(0.0))),
33 rowguid [uniqueidentifier] ROWGUIDCOL NOT NULL,
34 ModifiedDate DATETIME NOT NULL,
35 CONSTRAINT [PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID] PRIMARY KEY CLUSTERED
36(
37 SalesOrderID ASC,
38 SalesOrderDetailID ASC
39)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
40) ON [PRIMARY]
41GO
42
43
44
45--3--
46IF OBJECT_ID('dbo.SalesSummary', 'U') IS NULL
47CREATE TABLE dbo.SalesSummary(
48 ProductId INT PRIMARY KEY,
49 Quantity INT NOT NULL,
50 FirstOrder DATETIME NOT NULL,
51 LastOrder DATETIME NOT NULL
52)
53
54
55
56--4A--
57USE AdventureWorks2017
58GO
59IF EXISTS (SELECT * FROM sys.objects WHERE [name] = N'ai_SalesOrderDetail' AND [type] = 'TR')
60BEGIN
61 DROP TRIGGER Sales.ai_SalesOrderDetail;
62END;
63GO
64CREATE TRIGGER Sales.ai_SalesOrderDetail ON AdventureWorks2017.Sales.SalesOrderDetail
65AFTER INSERT AS
66BEGIN
67 INSERT INTO School2021.dbo.Sales
68 (SalesOrderID
69 ,SalesOrderDetailID
70 ,CarrierTrackingNumber
71 ,OrderQty
72 ,ProductID
73 ,SpecialOfferID
74 ,UnitPrice
75 ,UnitPriceDiscount
76 ,rowguid
77 ,ModifiedDate)
78 SELECT
79 SalesOrderID
80 ,SalesOrderDetailID
81 ,CarrierTrackingNumber
82 ,OrderQty
83 ,ProductID
84 ,SpecialOfferID
85 ,UnitPrice
86 ,UnitPriceDiscount
87 ,rowguid
88 ,ModifiedDate
89 FROM inserted
90END
91GO
92
93
94--4B--
95USE AdventureWorks2017
96GO
97IF EXISTS (SELECT * FROM sys.objects WHERE [name] = N'au_SalesOrderDetail' AND [type] = 'TR')
98BEGIN
99 DROP TRIGGER Sales.au_SalesOrderDetail;
100END;
101GO
102CREATE TRIGGER Sales.au_SalesOrderDetail ON AdventureWorks2017.Sales.SalesOrderDetail
103AFTER UPDATE AS
104BEGIN
105
106 UPDATE t
107 SET t.SalesOrderID = i.SalesOrderId
108 ,t.CarrierTrackingNumber = i.CarrierTrackingNumber
109 ,t.OrderQty = i.OrderQty
110 ,t.ProductID = i.ProductID
111 ,t.SpecialOfferID = i.SpecialOfferID
112 ,t.UnitPrice = i.UnitPrice
113 ,t.UnitPriceDiscount = i.UnitPriceDiscount
114 ,t.rowguid = i.rowguid
115 ,t.ModifiedDate = i.ModifiedDate
116 FROM School2021.dbo.Sales t
117 INNER JOIN inserted i ON t.SalesOrderID = i.SalesOrderID
118
119 INSERT INTO School2021.dbo.Sales
120 (SalesOrderID
121 ,SalesOrderDetailID
122 ,CarrierTrackingNumber
123 ,OrderQty
124 ,ProductID
125 ,SpecialOfferID
126 ,UnitPrice
127 ,UnitPriceDiscount
128 ,rowguid
129 ,ModifiedDate)
130 SELECT
131 SalesOrderID
132 ,SalesOrderDetailID
133 ,CarrierTrackingNumber
134 ,OrderQty
135 ,ProductID
136 ,SpecialOfferID
137 ,UnitPrice
138 ,UnitPriceDiscount
139 ,rowguid
140 ,ModifiedDate
141 FROM inserted
142 WHERE SalesOrderID NOT IN (SELECT SalesOrderID
143 FROM School2021.dbo.Sales)
144END
145
146GO
147
148
149--4C--
150USE AdventureWorks2017
151GO
152IF EXISTS (SELECT * FROM sys.objects WHERE [name] = N'ad_SalesOrderDetail' AND [type] = 'TR')
153BEGIN
154 DROP TRIGGER Sales.ad_SalesOrderDetail;
155END;
156GO
157CREATE TRIGGER Sales.ad_SalesOrderDetail ON AdventureWorks2017.Sales.SalesOrderDetail
158AFTER DELETE AS
159BEGIN
160 DELETE FROM School2021.dbo.Sales
161 WHERE SalesOrderID IN (SELECT SalesOrderID FROM deleted)
162END
163GO
164
165
166
167--5--
168USE School2021
169GO
170CREATE PROCEDURE dbo.sp_SalesSummarization
171AS
172BEGIN
173SET NOCOUNT ON;
174UPDATE school
175SET school.Quantity = i.SumQuantity FROM School2021.dbo.SalesSummary AS school
176INNER JOIN(SELECT ProductId, SUM(OrderQty) AS SumQuantity FROM Sales
177GROUP BY ProductID) AS i ON school.ProductID = i.ProductID
178END
179GO
180
181
182--6a--
183USE School2021
184GO
185IF EXISTS (SELECT * FROM sys.objects WHERE name = N'ai_SalesSummary' AND TYPE = 'TR')
186BEGIN
187DROP TRIGGER dbo.ai_SalesSummary;
188END;
189GO
190CREATE TRIGGER dbo.ai_SalesSummary ON School2021.dbo.SalesSummary
191AFTER INSERT AS
192BEGIN
193INSERT INTO School2021.dbo.SalesSummary
194 (ProductId
195 ,Quantity
196 ,FirstOrder
197 ,LastOrder)
198SELECT
199 ProductID
200 ,0
201 ,CURRENT_TIMESTAMP
202 ,CURRENT_TIMESTAMP
203FROM inserted
204EXEC sp_SalesSummarization;
205END
206GO
207
208
209--6b--
210USE School2021
211GO
212IF EXISTS (SELECT * FROM sys.objects WHERE name = N'au_SalesSummary' AND TYPE = 'TR')
213BEGIN
214DROP TRIGGER dbo.au_SalesSummary;
215END;
216GO
217CREATE TRIGGER dbo.au_SalesSummary ON School2021.dbo.SalesSummary
218AFTER UPDATE AS
219BEGIN
220UPDATE SalesSummary
221SET LastOrder = CURRENT_TIMESTAMP;
222EXEC sp_SalesSummarization;
223END
224GO
225
226
227--6c--
228USE School2021
229GO
230IF EXISTS (SELECT * FROM sys.objects WHERE name = N'ad_SalesSummary' AND TYPE = 'TR')
231BEGIN
232DROP TRIGGER dbo.ad_SalesSummary;
233END;
234GO
235CREATE TRIGGER dbo.ad_SalesSummary ON School2021.dbo.SalesSummary
236AFTER DELETE AS
237BEGIN
238EXEC sp_SalesSummarization;
239END
240GO