· 8 years ago · Dec 07, 2017, 02:30 AM
1DROP TRIGGER TR_1
2DROP TRIGGER TR_2
3DROP TRIGGER TR_3
4DROP TRIGGER TR_4
5DROP TRIGGER TR_5
6DROP TRIGGER TR_6
7DROP TABLE LowSupplyLog
8DROP TABLE CostPerHourLog
9GO
10
11--1 WRONG
12ALTER TABLE Job
13NOCHECK CONSTRAINT fk_JobToClient
14GO
15
16CREATE TRIGGER TR_1
17ON Job
18FOR INSERT, UPDATE
19AS
20IF NOT EXISTS (SELECT ClientID FROM Job)
21 BEGIN
22 RAISERROR ('Invalid Client ID', 16, 1)
23 ROLLBACK TRANSACTION
24 END
25RETURN
26GO
27
28--2 CORRECT
29CREATE TRIGGER TR_2
30ON Service
31FOR UPDATE
32AS
33IF @@ROWCOUNT > 0 AND UPDATE (CostPerHour)
34 BEGIN
35 IF EXISTS (SELECT Service.CostPerHour FROM Service INNER JOIN INSERTED ON Service.CostPerHour = INSERTED.CostPerHour
36 WHERE Service.CostPerHour > 0.2 * INSERTED.CostPerHour)
37 BEGIN
38 RAISERROR('CostPerHour cannot increase by more than 20%% each update', 16, 1)
39 ROLLBACK TRANSACTION
40 END
41 END
42RETURN
43GO
44
45--3 WRONG
46CREATE TRIGGER TR_3
47ON StaffTraining
48FOR INSERT, UPDATE
49AS
50IF @@ROWCOUNT > 0 OR UPDATE (StaffID)
51 BEGIN
52 IF EXISTS (SELECT StaffID FROM StaffTraining GROUP BY StaffID HAVING COUNT(StaffID) > 3)
53 BEGIN
54 RAISERROR('Each staff memeber can only take a maximum of 3 courses!', 16, 1)
55 ROLLBACK TRANSACTION
56 END
57 END
58RETURN
59GO
60
61--4 CORRECT
62ALTER TABLE Supply
63NOCHECK CONSTRAINT fk_SupplyToSupplyCategory
64GO
65
66CREATE TRIGGER TR_4
67ON SupplyCategory
68FOR DELETE
69AS
70IF @@ROWCOUNT > 0
71 BEGIN
72 IF EXISTS (SELECT SupplyCategory.SupplyCategoryCode FROM SupplyCategory INNER JOIN Supply ON SupplyCategory.SupplyCategoryCode = Supply.SupplyCategoryCode)
73 BEGIN
74 RAISERROR('SupplyCategory has Supplies in it!', 16, 1)
75 ROLLBACK TRANSACTION
76 END
77 END
78RETURN
79GO
80
81--5 WRONG
82CREATE TABLE CostPerHourLog
83(
84LogID int identity (1,1) not null
85constraint pk_LogID primary key clustered,
86ChangeDateTime smalldatetime not null,
87ServiceCode varchar (15) not null,
88Description varchar (100) not null,
89OldCostPerHour smallmoney not null,
90NewCostPerHour smallmoney not null
91)
92GO
93
94CREATE TRIGGER TR_5
95ON Service
96FOR UPDATE
97AS
98DECLARE @OldCost smallmoney
99SET @OldCost = (SELECT Service.CostPerHour FROM Service INNER JOIN INSERTED ON Service.ServiceCode = INSERTED.ServiceCode WHERE Service.ServiceCode = INSERTED.ServiceCode)
100IF @@ROWCOUNT > 0 AND UPDATE (CostPerHour) AND @OldCost != (SELECT CostPerHour FROM INSERTED WHERE ServiceCode = INSERTED.ServiceCode)
101 BEGIN
102 INSERT INTO CostPerHourLog (ChangeDateTime, ServiceCode, Description, OldCostPerHour, NewCostPerHour)
103 VALUES (getdate(), (Select ServiceCode from INSERTED), (Select Description from INSERTED), @OldCost, (Select CostPerHour from INSERTED))
104 RAISERROR('This Worked',16,1)
105 END
106RETURN
107GO
108
109--6 CORRECT
110CREATE TABLE LowSupplyLog
111(
112LogID int identity (1,1) not null
113constraint pk_LowLogID primary key clustered,
114LogDateTime smalldatetime not null,
115SupplyCode varchar (8) not null,
116Description varchar (100) not null,
117QuantityOnHand smallint not null
118)
119GO
120
121CREATE TRIGGER TR_6
122ON Supply
123FOR UPDATE
124AS
125IF @@ROWCOUNT > 0 AND UPDATE (QuantityOnHand) AND (Select QuantityOnHand from INSERTED) < 2
126 BEGIN
127 INSERT INTO LowSupplyLog (LogDateTime, SupplyCode, Description, QuantityOnHand)
128 VALUES (getdate(), (Select SupplyCode from INSERTED), (Select Description from INSERTED), (Select QuantityOnHand from INSERTED))
129 END
130RETURN