smaA6RQ4

1create database pc2
2go
3
4use pc2
5go
6
7CREATE TABLE laptop(
8 code int NOT NULL, 
9 model varchar(32) NOT NULL, 
10 speed decimal(4, 0) NOT NULL,
11 ram decimal(4, 0) NOT NULL, 
12 hd decimal(3, 0) NOT NULL, 
13 price float NOT NULL,
14 screen int NOT NULL);
15 
16 CREATE TABLE pc(
17  code int NOT NULL ,
18  model varchar(32) NOT NULL ,
19  speed decimal(4, 0) NOT NULL ,
20  ram decimal(4, 0) NOT NULL ,
21  hd decimal(3, 0) NOT NULL ,
22  cd varchar(3) NOT NULL ,
23  price float NOT NULL 
24);
25
26CREATE TABLE product(
27  maker char(32) NOT NULL ,
28  model varchar(32) NOT NULL ,
29  type varchar(7) NOT NULL 
30);
31
32CREATE TABLE printer(
33  code int NOT NULL ,
34  model varchar(32) NOT NULL ,
35  color char(1) NOT NULL ,
36  type varchar(6) NOT NULL ,
37  price float NOT NULL 
38);
39
40SELECT * FROM product
41
42INSERT INTO product VALUES ('DELL', 'Optiplex', 'PC')
43INSERT INTO product VALUES ('DELL', 'Precision', 'PC')
44INSERT INTO product VALUES ('DELL', 'Latitude', 'Laptop')
45INSERT INTO product VALUES ('DELL', 'Inspiron', 'Laptop')
46
47INSERT INTO product VALUES ('Hewlett-Packard', 'Vectra', 'PC')
48INSERT INTO product VALUES ('Hewlett-Packard', 'Compaq', 'PC')
49INSERT INTO product VALUES ('Hewlett-Packard', 'ProBook', 'Laptop')
50INSERT INTO product VALUES ('Hewlett-Packard', 'EliteBook', 'Laptop')
51INSERT INTO product VALUES ('Hewlett-Packard', 'Envy', 'Laptop')
52INSERT INTO product VALUES ('Hewlett-Packard', 'DeskJet', 'Printer')
53INSERT INTO product VALUES ('Hewlett-Packard', 'LaserJet', 'Printer')
54
55select * from pc
56select * from laptop
57
58INSERT INTO pc VALUES (1, 'Vectra', 4000, 64, 768, '32x', 2938)
59INSERT INTO pc VALUES (2, 'Vectra', 3600, 32, 512, '32x', 1938)
60
61INSERT INTO laptop VALUES (1, 'EliteBook', 4100, 128, 512, 5000, 17)
62INSERT INTO laptop VALUES (2, 'EliteBook', 2600, 128, 512, 3000, 17)
63INSERT INTO laptop VALUES (3, 'Latitude', 3000, 128, 512, 3300, 17)
64
65SELECT * FROM product
66SELECT * FROM pc
67SELECT * FROM laptop
68SELECT * FROM printer
69
70GO
71
72-- Trigger 1: pc
73
74ALTER TRIGGER PcCheck ON pc AFTER INSERT, UPDATE
75AS
76	DECLARE @foundCount int = (SELECT COUNT(DISTINCT i.code)
77				               FROM inserted i
78						           JOIN product p ON i.model = p.model
79						           JOIN product p2 ON p.maker = p2.maker
80						           JOIN laptop l ON p2.model = l.model
81				               WHERE l.speed >= i.speed)
82
83    DECLARE @changeCount int = (SELECT COUNT(*) FROM inserted)
84
85	IF @foundCount <> @changeCount
86	BEGIN
87		RAISERROR('PcCheck failure', 11, 1)
88		ROLLBACK
89	END
90GO
91
92-- Trigger 2: laptop
93
94
95CREATE TRIGGER LaptopCheck ON laptop AFTER UPDATE, DELETE
96AS
97    -- Случай 1: обновена е колоната speed, без да се променя code
98    -- и model
99    -- Това е обновлението, което би следвало да се случва най-често
100 
101    IF UPDATE(speed) AND NOT UPDATE(code) AND NOT UPDATE(model)
102        BEGIN
103            -- В този случай може да свържеме inserted и deleted по
104            -- code и да разбереме кои точно са лаптопите и съответно
105            -- техните модели за които има намаление на честотата. За
106            -- тях трябва да провериме дали има проблем
107            IF EXISTS ( SELECT *
108                        FROM deleted d
109                            JOIN inserted i ON i.code = d.code AND i.speed < d.speed
110                            JOIN product p1 ON d.model = p1.model
111                            JOIN (SELECT px.maker, MAX(speed) AS maxspeed
112                                  FROM product px
113                                  JOIN laptop lx ON px.model = lx.model
114                                  GROUP BY px.maker) t ON t.maker = p1.maker
115                            JOIN product p2 ON p1.maker = p2.maker
116                            JOIN pc ON pc.model = p2.model
117                            WHERE pc.speed > t.maxspeed)
118            BEGIN
119                RAISERROR('Unacceptable laptop update', 11, 1)
120                ROLLBACK
121            END
122        END
123    ELSE
124        BEGIN
125            -- Ако за някой лаптоп е сменен модела - в контекста на новия
126            -- модел (ако е от друг производител) няма опасност да се появи проблем,
127            -- дори и честотата да е намалена (след като досега не е имало проблем).
128            -- Затова гледаме само моделите в deleted и правиме проверка за всички.
129            -- Тук попадаме и в случая на DELETE тригер, в който случай
130            -- пак трябва да провериме моделите (водещи към производител)
131            -- за които е имало триене дали не е възникнал проблем
132 
133            IF EXISTS ( SELECT *
134                        FROM deleted d
135                            JOIN product p1 ON d.model = p1.model
136                            LEFT OUTER JOIN (SELECT px.maker, MAX(speed) AS maxspeed
137                                             FROM product px
138                                                JOIN laptop lx ON px.model = lx.model
139                                             GROUP BY px.maker) t ON t.maker = p1.maker
140                            JOIN product p2 ON p1.maker = p2.maker
141                            JOIN pc ON pc.model = p2.model
142                            WHERE pc.speed > t.maxspeed OR t.maxspeed IS NULL)
143            BEGIN
144                RAISERROR('Unacceptable laptop update/delete', 11, 1)
145                ROLLBACK
146            END
147        END
148GO
149
150
151
152CREATE TRIGGER ProductTrigger ON product AFTER UPDATE
153AS
154    -- Правиме проверка за всички производители от deleted
155 
156    IF EXISTS ( SELECT *
157                FROM deleted d
158                    LEFT OUTER JOIN (SELECT px.maker, MAX(speed) AS maxspeed
159                                     FROM product px
160                                     JOIN laptop lx ON px.model = lx.model
161                                     GROUP BY px.maker) t ON t.maker = d.maker
162                    JOIN product p2 ON d.maker = p2.maker
163                    JOIN pc ON pc.model = p2.model
164                WHERE pc.speed > t.maxspeed OR t.maxspeed IS NULL)
165    BEGIN
166        RAISERROR('Unacceptable product model update', 11, 1)
167        ROLLBACK
168    END
169GO
170
171SELECT * FROM pc
172SELECT * FROM laptop
173
174BEGIN TRAN 
175
176INSERT INTO pc VALUES (3, 'Optiplex', 3100, 128, 256, '32x', 15), (4, 'Optiplex', 2900, 128, 256, '32x', 15)
177
178ROLLBACK
179
180
181
182
183
184
185
186CREATE TRIGGER ProductTrigger ON product AFTER UPDATE
187AS
188
189    -- ако моделът не се обновява
190
191    IF EXISTS ( SELECT *
192                FROM deleted d
193                    JOIN inserted i ON d.model = i.model
194                    JOIN pc ON pc.model = d.model
195                WHERE d.type <> i.type AND d.type = 'PC') OR
196       EXISTS ( SELECT *
197                FROM deleted d
198                    JOIN inserted i ON d.model = i.model
199                    JOIN laptop l ON l.model = d.model
200                WHERE d.type <> i.type AND d.type = 'Laptop') OR
201       EXISTS ( SELECT *
202                FROM deleted d
203                    JOIN inserted i ON d.model = i.model
204                    JOIN printer p ON p.model = d.model
205                WHERE d.type <> i.type AND d.type = 'Printer')
206    BEGIN
207        RAISERROR('Unacceptable model type update', 11, 1)
208        ROLLBACK
209    END
210
211    IF UPDATE(model)
212    BEGIN
213        IF EXISTS (SELECT *
214                   FROM deleted d
215                        JOIN pc ON pc.model = d.model
216                   WHERE d.model NOT IN (SELECT model FROM inserted) AND d.type = 'PC') OR
217           EXISTS (SELECT *
218                   FROM deleted d
219                        JOIN laptop ON laptop.model = d.model
220                   WHERE d.model NOT IN (SELECT model FROM inserted) AND d.type = 'Laptop') OR
221           EXISTS (SELECT *
222                   FROM deleted d
223                        JOIN printer ON printer.model = d.model
224                   WHERE d.model NOT IN (SELECT model FROM inserted) AND d.type = 'Printer')
225        BEGIN
226            RAISERROR('Unacceptable model type update', 11, 1)
227            ROLLBACK
228        END
229    END
230
231    -- ако моделът се обновява
232    -- - може да видиме, кои са моделите, които изчезват от играта, да им вземеме типа и да провериме дали има изостанали
233    -- - може да видиме, кои са моделите, които остават в играта с нов тип и за тях да видиме дали има проблемни изостанали със стария тип
234GO