8hCB2qX7

1
2-- Vorarbeit 1
3use Projekt
4GO
5ALTER VIEW vwProjektmitarbeiterstunden
6AS
7SELECT P.ProjBezeichnung AS 'Name', P.ProjNr AS 'Projektnummer',
8Paz.PazPersonal AS 'Personalnummer', PN.PersVorname AS 'Vorname',
9PN.PersNachname AS 'Nachname', Paz.PazDatum AS 'Datum',
10Paz.PazStunden AS 'Stunden'
11FROM tblProjektarbeitszeit Paz INNER JOIN tblProjekte P ON P.ProjNr = Paz.PazProjekt INNER JOIN tblPersonal PN
12ON PN.PersNr = Paz.PazPersonal
13
14GO
15
16SELECT * FROM vwProjektmitarbeiterstunden
17
18-- Vorarbeit 2, Hilfe von Nader
19SELECT PazProjekt, PazPersonal, COUNT([PazDatum]) FROM tblProjektarbeitszeit
20WHERE PazPersonal is not NULL
21GROUP BY PazPersonal, PazProjekt
22HAVING COUNT(PazDatum) != COUNT(DISTINCT PazDatum)
23
24
25SELECT * FROM tblProjektarbeitszeit
26WHERE PazProjekt = 106 AND PazPersonal = 115
27ORDER BY PazDatum
28
29UPDATE tblProjektarbeitszeit
30SET PazDatum = DATEADD(day, 1, PazDatum)
31WHERE PazID = 472
32GO
33
34-- Bsp 1
35use Projekt
36GO
37ALTER TRIGGER dbo.trPW
38ON vwProjektmitarbeiterstunden
39INSTEAD OF INSERT
40AS 
41    IF @@ROWCOUNT = 0 RETURN;
42    IF @@ROWCOUNT > 1
43    BEGIN
44        THROW 51000, 'Es kann nur 1 Eintrag eingefuegt werden!', 1;
45        RETURN;
46    END
47
48    SET NOCOUNT ON;
49
50    DECLARE 
51    @projNr INT = (SELECT Projektnummer FROM inserted), 
52    @persNr INT = (SELECT Personalnummer FROM inserted), 
53    @datum DATE = (SELECT Datum FROM inserted), 
54    @stunden DECIMAL(3,1) = (SELECT Stunden FROM inserted)
55
56	IF NOT EXISTS (SELECT * FROM tblProjekte WHERE @projNr = ProjNr)
57	BEGIN 
58	        THROW 51000, 'Das angegebene Projekt existiert nicht!', 1
59		    RETURN;
60	END
61
62	IF NOT EXISTS (SELECT * FROM tblPersonal WHERE @persNr = PersNr)
63	BEGIN 
64        THROW 51000, 'Der Mitarbeiter ist nicht vorhanden!', 1;
65		RETURN;
66	END
67
68    IF EXISTS (SELECT * FROM vwProjektmitarbeiterstunden WHERE @projNr = Projektnummer 
69    AND @persNr = Personalnummer AND @datum = Datum)
70    BEGIN
71        UPDATE tblProjektarbeitszeit SET PazStunden = PazStunden + @stunden
72		WHERE @projNr = PazProjekt AND @persNr = PazPersonal AND @datum = PazDatum
73            
74    END
75        ELSE
76            INSERT INTO tblProjektarbeitszeit (PazProjekt, PazPersonal, PazDatum, PazStunden)
77            SELECT Projektnummer, Personalnummer, Datum, Stunden FROM inserted
78RETURN;
79
80GO
81
82-- Bsp 2
83
84
85--DROP TABLE ChangeLogProjekte
86
87SELECT * FROM tblProjekte WHERE ProjStatus = 2
88
89GO
90
91ALTER TRIGGER dbo.ProjUpdate
92ON tblProjekte
93INSTEAD OF UPDATE
94AS BEGIN
95	IF @@ROWCOUNT = 0 RETURN
96	SET NOCOUNT ON;
97    IF OBJECT_ID('ChangeLogProjekte', 'U') IS NULL
98        CREATE TABLE ChangeLogProjekte (
99            ID INT IDENTITY CONSTRAINT PK_Artikel PRIMARY KEY NOT NULL,
100            Username VARCHAR(100) NOT NULL,
101            Datum DATETIME NOT NULL,
102            Error INT NOT NULL
103        );
104    DECLARE @status INT, @nr INT, @error INT = 0
105    DECLARE MY_CURSOR CURSOR 
106    LOCAL STATIC READ_ONLY FORWARD_ONLY
107    FOR
108    SELECT ProjStatus, ProjNr FROM inserted
109    OPEN MY_CURSOR
110    FETCH NEXT FROM MY_CURSOR INTO @status, @nr
111    WHILE @@FETCH_STATUS = 0
112    BEGIN 
113        IF(@status = 2)
114        BEGIN
115            THROW 51000, 'Aenderung nicht moeglich, Projekt bereits abgeschlossen!', 1
116                INSERT INTO ChangeLogProjekte VALUES
117                (SUSER_NAME(),GETDATE(),1)
118            RETURN;
119        END
120        ELSE
121        BEGIN 
122            DECLARE
123            @bezeichnung VARCHAR(30) = (SELECT ProjBezeichnung FROM inserted WHERE ProjNr = @nr),
124            @kosten MONEY = (SELECT ProjKalkKosten FROM inserted WHERE ProjNr = @nr),
125            @stunden decimal(7,1) = (SELECT ProjStundenSoll FROM inserted WHERE ProjNr = @nr), 
126            @projEnde SMALLDATETIME = (SELECT ProjEndeSoll FROM inserted WHERE ProjNr = @nr)
127            IF UPDATE(ProjBezeichnung)
128                UPDATE tblProjekte SET ProjBezeichnung = @bezeichnung WHERE ProjNr = @nr
129            IF UPDATE(ProjKalkKosten)
130                UPDATE tblProjekte SET ProjKalkKosten = @kosten WHERE ProjNr = @nr
131            IF UPDATE(ProjStundenSoll)
132                UPDATE tblProjekte SET ProjStundenSoll = @stunden WHERE ProjNr = @nr
133            IF UPDATE(ProjEndeSoll)
134                UPDATE tblProjekte SET ProjBezeichnung = @bezeichnung WHERE ProjNr = @nr
135            IF UPDATE(ProjBeginn)
136                UPDATE tblProjekte SET ProjBeginn = (SELECT ProjBeginn FROM inserted) WHERE ProjNr = @nr
137            IF UPDATE(ProjEndeIst)
138                UPDATE tblProjekte SET ProjEndeIst = (SELECT ProjEndeIst FROM inserted) WHERE ProjNr = @nr
139            IF UPDATE(ProjStatus)
140                UPDATE tblProjekte SET ProjStatus = (SELECT ProjStatus FROM inserted) WHERE ProjNr = @nr   
141            IF UPDATE(ProjStatus)
142                UPDATE tblProjekte SET ProjStundenIst = (SELECT ProjStundenIst FROM inserted) WHERE ProjNr = @nr 
143        FETCH NEXT FROM MY_CURSOR INTO @status, @nr
144        END
145    END
146    CLOSE MY_CURSOR
147    DEALLOCATE MY_CURSOR
148    INSERT INTO ChangeLogProjekte VALUES
149    (SUSER_NAME(),GETDATE(),@error)
150    SELECT * FROM ChangeLogProjekte
151END
152RETURN;
153
154GO
155
156BEGIN TRANSACTION
157UPDATE tblProjekte
158SET ProjBezeichnung = 'test' WHERE ProjStatus = 1
159SELECT * FROM tblProjekte WHERE ProjStatus = 1
160ROLLBACK
161
162GO
163-- Bsp 3
164
165-- 101
166SELECT * FROM tblPersonal
167BEGIN TRANSACTION
168DECLARE @id INT = 101
169UPDATE TABLE tblAbteilungen SET AbtLeiter = NULL WHERE AbtLeiter = @id
170DELETE FROM tblPersonal WHERE PersNr = @id
171ROLLBACK
172
173SELECT * FROM tblPersonal
174BEGIN TRANSACTION
175DECLARE @id INT = 452
176DELETE FROM tblTeammitglieder WHERE PersNr = @id
177DELETE FROM tblPersonal WHERE PersNr = @id
178SELECT * FROM tblPersonal WHERE PersNr = @id
179ROLLBACK
180
181GO
182use Projekt
183GO
184CREATE TRIGGER deleteAll
185ON tblPersonal
186INSTEAD OF DELETE
187AS
188    IF @@ROWCOUNT = 0 RETURN;
189    SET NOCOUNT ON;
190    
191    DECLARE @id INT
192    DECLARE MY_CURSOR CURSOR
193    LOCAL STATIC READ_ONLY FORWARD_ONLY
194    FOR
195    SELECT PersNr FROM deleted
196    OPEN MY_CURSOR
197    FETCH NEXT FROM MY_CURSOR INTO @id
198    WHILE @@FETCH_STATUS = 0
199    BEGIN 
200        IF EXISTS (SELECT * FROM tblTeammitglieder WHERE PersNr = @id)
201            DELETE FROM tblTeammitglieder WHERE PersNr = @id
202        IF EXISTS (SELECT * FROM tblAbteilungen WHERE AbtLeiter = @id)
203            UPDATE tblAbteilungen SET AbtLeiter = NULL WHERE AbtLeiter = @id
204        DELETE FROM tblPersonal WHERE PersNr = @id
205        FETCH NEXT FROM MY_CURSOR INTO @id
206    END
207    CLOSE MY_CURSOR
208    DEALLOCATE MY_CURSOR
209RETURN ;
210
211DROP TRIGGER deleteAll
212
213BEGIN TRANSACTION
214DELETE FROM tblPersonal WHERE PersNr = 101
215ROLLBACK
216
217
218-- Bsp 4
219
220-- Man könnte eine Stored Procedure für diesen Fall erstellen