· 6 years ago · Jun 17, 2019, 09:34 AM
1--############################# Zadanie 0 ##############################
2--napisać kod ktory sprawdzi jakie sa w danej bazie widoki i skasuje wszystkie
3SELECT
4 OBJECT_SCHEMA_NAME(v.object_id) schema_name,
5 v.name
6FROM
7 sys.views as v;
8
9
10--############################# ZADANIE 1 ##############################
11
12Create trigger
13GO
14
15--DROP TABLE Test;
16--GO
17
18CREATE TABLE Test (ID int PRIMARY KEY, Ostatnia_aktualizacja datetime);
19GO
20
21INSERT INTO Test(ID) VALUES(1);
22INSERT INTO Test(ID) VALUES(2);
23GO
24
25--DROP TRIGGER trigger1;
26--GO
27CREATE TRIGGER trigger1
28ON Test
29AFTER UPDATE
30AS
31 BEGIN
32 UPDATE Test SET Ostatnia_aktualizacja = GETDATE() WHERE ID = (SELECT ID FROM inserted);
33 END
34GO
35
36UPDATE Test SET ID = 3 WHERE ID = 2;
37GO
38
39SELECT * FROM Test;
40
41
42
43--############################# ZADANIE 2 ##############################
44
45
46
47--DROP TABLE Test;
48GO
49
50CREATE TABLE Test (ID int, Ostatnia_aktualizacja date);
51GO
52
53INSERT INTO Test(ID) VALUES(1);
54INSERT INTO Test(ID) VALUES(2);
55GO
56
57--DROP TABLE Test_Historia;
58SELECT * INTO Test_Historia FROM Test WHERE 1 = 0;
59ALTER TABLE Test_Historia
60 ADD Kto varchar(30),
61 Kiedy datetime,
62 Operacja varchar(30);
63GO
64
65--DROP TRIGGER trigger2;
66CREATE TRIGGER trigger2
67ON Test
68AFTER DELETE
69AS
70 BEGIN
71 INSERT INTO Test_Historia VALUES(
72 (SELECT ID FROM deleted),
73 (SELECT Ostatnia_aktualizacja FROM deleted),
74 CURRENT_USER,
75 GETDATE(),
76 'DELETE');
77 END
78GO
79
80DELETE FROM Test WHERE ID = 1;
81SELECT * FROM Test_Historia;
82
83--############################# ZADANIE 3 ##############################
84
85--DROP TABLE Test;
86GO
87
88CREATE TABLE Test (ID int, Ostatnia_aktualizacja date);
89GO
90
91INSERT INTO Test(ID) VALUES(1);
92INSERT INTO Test(ID) VALUES(2);
93GO
94
95--DROP TABLE Test_Historia;
96SELECT * INTO Test_Historia FROM Test WHERE 1 = 0;
97ALTER TABLE Test_Historia
98 ADD Kto varchar(30),
99 Kiedy datetime,
100 Operacja varchar(30);
101GO
102
103--DROP TRIGGER trigger3;
104CREATE TRIGGER trigger3
105ON Test
106AFTER UPDATE
107AS
108 BEGIN
109 INSERT INTO Test_Historia VALUES(
110 (SELECT ID FROM deleted),
111 (SELECT Ostatnia_aktualizacja FROM deleted),
112 CURRENT_USER,
113 GETDATE(),
114 'UPDATE');
115 END
116GO
117
118UPDATE Test SET ID = 3 WHERE ID = 2;
119SELECT * FROM Test_Historia;
120
121--############################# ZADANIE 4 ##############################
122
123
124
125--DROP TABLE Test;
126GO
127
128CREATE TABLE Test (ID int, Kategoria varchar(30));
129GO
130
131INSERT INTO Test(ID, Kategoria) VALUES(1, 'Kat 1');
132INSERT INTO Test(ID, Kategoria) VALUES(2, 'Kat 2');
133GO
134
135--DROP TRIGGER trigger4;
136CREATE TRIGGER trigger4
137ON Test
138AFTER INSERT
139AS
140 BEGIN
141 IF ((SELECT COUNT(*) FROM Test WHERE Kategoria = (SELECT Kategoria FROM inserted)) >= 2)
142 BEGIN
143 PRINT 'ABC'
144 ROLLBACK TRANSACTION
145 END
146 ELSE
147 BEGIN
148 PRINT '123'
149 END
150 END
151GO
152
153DELETE FROM Test WHERE ID = 3;
154INSERT INTO Test(ID, Kategoria) VALUES(3, 'Kat 3');
155SELECT * FROM Test;
156
157
158
159--############################# ZADANIE 5 ##############################
160--DROP TABLE Test;
161GO
162
163CREATE TABLE Test (ID int, Kategoria varchar(30));
164GO
165
166INSERT INTO Test(ID, Kategoria) VALUES(1, 'Kat 1');
167INSERT INTO Test(ID, Kategoria) VALUES(2, 'Kat 2');
168GO
169
170DROP TRIGGER trigger5;
171GO
172
173CREATE TRIGGER trigger5
174ON Test
175INSTEAD OF INSERT
176AS
177 BEGIN
178 INSERT INTO test SELECT inserted.ID, UPPER(inserted.Kategoria) FROM inserted;
179 END
180GO
181
182DELETE FROM Test WHERE ID = 3;
183INSERT INTO Test(ID, Kategoria) VALUES(3, 'Kat 3');
184SELECT * FROM Test;
185
186
187--############################# ZADANIE 5.1 ##############################
188
189
190
191--DROP TABLE Test;
192GO
193
194CREATE TABLE Test (ID int, Cena int);
195GO
196
197INSERT INTO Test(ID, Cena) VALUES(1, 10);
198INSERT INTO Test(ID, Cena) VALUES(2, 20);
199GO
200
201--DROP TRIGGER trigger6;
202CREATE TRIGGER trigger6
203ON Test
204INSTEAD OF INSERT
205AS
206 BEGIN
207 IF ((SELECT Cena FROM inserted) < 0)
208 BEGIN
209 INSERT INTO test SELECT inserted.ID, 0 FROM inserted;
210 END
211 ELSE
212 BEGIN
213 INSERT INTO test SELECT * FROM inserted;
214 END
215 END
216GO
217
218DELETE FROM Test WHERE ID = 3;
219INSERT INTO Test(ID, Cena) VALUES(4, 15);
220SELECT * FROM Test;
221
222
223
224--############################# ZADANIE 5.2 ##############################
225
226
227
228
229--DROP TABLE Test;
230GO
231
232CREATE TABLE Test (ID int, NumerKategorii int);
233GO
234
235INSERT INTO Test(ID, NumerKategorii) VALUES(1, 1);
236INSERT INTO Test(ID, NumerKategorii) VALUES(2, 3);
237GO
238
239--DROP TRIGGER trigger7;
240CREATE TRIGGER trigger7
241ON Test
242INSTEAD OF INSERT
243AS
244 BEGIN
245 IF NOT EXISTS (SELECT CategoryID FROM Categories WHERE CategoryID = (SELECT NumerKategorii FROM inserted))
246 BEGIN
247 PRINT 'Kategoria nie istnieje';
248 END
249 ELSE
250 BEGIN
251 INSERT INTO test SELECT * FROM inserted;
252 END
253 END
254GO
255
256DELETE FROM Test WHERE ID = 3;
257INSERT INTO Test(ID, NumerKategorii) VALUES(2, 2);
258SELECT * FROM Test;
259
260
261
262--############################# ZADANIE 5.3 ##############################
263
264
265--DROP TABLE Test;
266GO
267
268CREATE TABLE Test (ID int, NazwaKategorii varchar(30));
269GO
270
271INSERT INTO Test(ID, NazwaKategorii) VALUES(1, 'Kat 1');
272INSERT INTO Test(ID, NazwaKategorii) VALUES(2, 'Kat 2');
273GO
274
275--DROP TRIGGER trigger8;
276CREATE TRIGGER trigger8
277ON Test
278AFTER UPDATE
279AS
280 BEGIN
281 IF (COLUMNS_UPDATED() = 2)
282 BEGIN
283 PRINT 'Nie można zmieniać nazwy kategorii';
284 ROLLBACK TRANSACTION;
285 END
286 END
287GO
288
289DELETE FROM Test WHERE ID = 2;
290UPDATE Test SET ID = 8 WHERE ID = 2;
291SELECT * FROM Test;
292
293
294--############################# ZADANIE 5.4 ##############################
295
296--DROP TABLE Test;
297GO
298
299CREATE TABLE Test (ID int, NazwaKategorii varchar(30));
300GO
301
302INSERT INTO Test(ID, NazwaKategorii) VALUES(1, 'Kat 1');
303INSERT INTO Test(ID, NazwaKategorii) VALUES(2, 'Kat 2');
304GO
305
306--DROP TRIGGER trigger9;
307CREATE TRIGGER trigger9
308ON Test
309AFTER UPDATE
310AS
311 BEGIN
312 IF UPDATE(NazwaKategorii)
313 BEGIN
314 PRINT 'Nie można zmieniać nazwy kategorii';
315 ROLLBACK TRANSACTION;
316 END
317 END
318GO
319
320DELETE FROM Test WHERE ID = 8;
321UPDATE Test SET NazwaKategorii = 'Kat 2137' WHERE ID = 2;
322SELECT * FROM Test;
323
324
325
326--############################# ZADANIE 5.5 ##############################
327
328DROP TRIGGER trigger1;