MVtt6dHF

1create table WorkshopParticipants2
2(
3	WorkshopID int not null
4		constraint WorkshopParticipants_Workshops__fk
5			references Workshops,
6	ConferenceParticipantID int not null
7		constraint WorkshopParticipants_ConferenceParticipants__fk
8			references ConferenceParticipants
9)
10go
11
12CREATE TRIGGER dbo.TR_WorkshopParticipants_CancelledReservation
13  ON dbo.WorkshopParticipants
14  AFTER INSERT, UPDATE
15AS
16BEGIN
17
18  IF EXISTS(
19    SELECT *
20    FROM inserted
21    JOIN ConferenceParticipants AS cp
22      ON inserted.ConferenceParticipantID = cp.ConferenceParticipantID
23    JOIN ConfDayReservations AS cdr
24      ON cp.ConfDayReservationID = cdr.ConfDayReservationID
25    JOIN WorkshopReservations AS wr
26      ON cdr.ConfDayReservationID = wr.ConfDayReservationID
27    WHERE wr.IsCancelled = 1
28  )
29  BEGIN
30
31    ROLLBACK
32    ;THROW 60000, 'Cannot add new participant to cancelled WorkshopReservation', 1
33
34  END
35
36END
37go
38
39CREATE TRIGGER TR_WorkshopParticipants_CollidingWorkshops
40ON dbo.WorkshopParticipants
41  AFTER INSERT, UPDATE
42AS
43BEGIN
44
45  IF EXISTS(
46    SELECT * FROM inserted
47    JOIN ConferenceParticipants AS cp1
48      ON inserted.ConferenceParticipantID = cp1.ConferenceParticipantID
49    JOIN ConferenceParticipants AS cp2
50      ON cp1.ConferenceParticipantID <> cp2.ConferenceParticipantID
51        AND cp1.ParticipantID = cp2.ParticipantID
52    JOIN WorkshopParticipants AS wp2
53      ON cp2.ConferenceParticipantID = wp2.ConferenceParticipantID
54    WHERE dbo.FN_WorkshopsColliding (inserted.WorkshopID, wp2.WorkshopID) = 1
55  )
56  BEGIN
57
58    ROLLBACK
59    ;THROW 60000, 'Cannot add workshopParticipant because of colliding workshops', 1
60
61  END
62
63END
64go
65
66CREATE TRIGGER TR_WorkshopParticipants_NoMoreFreePlaces
67ON dbo.WorkshopParticipants
68  AFTER INSERT, UPDATE
69AS
70BEGIN
71
72  IF EXISTS(
73    SELECT *
74    FROM WorkshopReservations
75    WHERE ParticipantsNumber < dbo.FN_GetWorkshopReservationSignedParticipantsNumber (WorkshopReservationID)
76  )
77  BEGIN
78
79    ROLLBACK
80    ;THROW 60000, 'No free places left for given WorkshopReservationID', 1
81
82  END
83
84END
85go
86
87
88
89-- PROCEDURY
90
91CREATE PROCEDURE dbo.PR_RemoveWorkshopParticipant
92  @ConferenceParticipantID INT,
93  @WorkshopID INT
94AS
95BEGIN
96    BEGIN TRY
97      IF NOT EXISTS(
98        SELECT *
99        FROM WorkshopParticipants
100        WHERE ConferenceParticipantID=@ConferenceParticipantID OR WorkshopID=@WorkshopID
101      )
102      BEGIN
103        ;THROW 61001, 'Given ConferenceParticipantID and WorkshopID does not exist', 1
104      END
105      IF @ConferenceParticipantID IS NOT NULL
106        BEGIN
107          DELETE  FROM WorkshopParticipants
108                  WHERE ConferenceParticipantID=@ConferenceParticipantID
109        END
110      IF @WorkshopID IS NOT NULL
111        BEGIN
112          DELETE  FROM WorkshopParticipants
113                  WHERE WorkshopID=@WorkshopID
114        END
115    END TRY
116    BEGIN CATCH
117      DECLARE @errorMsg nvarchar (2048) = 'An error occurred while deleting workshopParticipant: ' + ERROR_MESSAGE ();
118      THROW 61000, @errorMsg, 1
119    END CATCH
120END
121GO
122
123
124
125CREATE PROCEDURE dbo.PR_AddWorkshopParticipant
126  @WorkshopID INT,
127  @ConferenceParticipantID INT
128AS
129BEGIN
130    SET NOCOUNT ON
131    BEGIN TRY
132
133      IF NOT EXISTS(
134        SELECT *
135        FROM Workshops
136        WHERE @WorkshopID = WorkshopID
137      )
138        BEGIN
139        ;THROW 60000, 'Given WorkshopID does not exist', 1
140      END
141
142      IF NOT EXISTS(
143        SELECT *
144        FROM ConferenceParticipants
145        WHERE @ConferenceParticipantID = ConferenceParticipantID
146      )
147        BEGIN
148        ;THROW 60000, 'Given ConferenceParticipantID does not exist', 1
149      END
150
151      INSERT INTO dbo.WorkshopParticipants(WorkshopID, ConferenceParticipantID)
152        VALUES (@WorkshopID, @ConferenceParticipantID)
153    END TRY
154    BEGIN CATCH
155      DECLARE @errorMsg nvarchar (2048) = 'An error occurred while adding dayPrice: ' + ERROR_MESSAGE ();
156      THROW 60000, @errorMsg, 1
157    END CATCH
158END
159GO
160
161
162
163
164
165
166-- FUNKCJE
167
168CREATE FUNCTION dbo.FN_GetWorkshopReservationSignedParticipantsNumber(@WorkshopReservationID INT)
169  RETURNS INT
170AS BEGIN
171  RETURN (
172    SELECT count(*)
173    FROM dbo.WorkshopReservations AS WR
174    JOIN ConfDayReservations AS CDR
175      ON WR.ConfDayReservationID = CDR.ConfDayReservationID AND WR.WorkshopReservationID = @WorkshopReservationID
176    JOIN ConferenceParticipants AS CP
177      ON CDR.ConfDayReservationID = CP.ConfDayReservationID
178    JOIN WorkshopParticipants AS WP
179      ON CP.ConferenceParticipantID = WP.ConferenceParticipantID AND WP.WorkshopID = WR.WorkshopID
180  )
181END
182GO