· 4 years ago · Dec 20, 2020, 01:56 PM
1use TSQL2012;
2
3/*Tworzy tabele Logs*/
4CREATE TABLE Logs(
5 logid INT IDENTITY(1,1),
6 date DATETIME,
7 tablename NVARCHAR(30),
8 operationdetails NVARCHAR(50)
9);
10GO
11
12/*Trigger na dodawanie w Production.Products*/
13CREATE TRIGGER trigInsProductionProducts
14ON Production.Products
15INSTEAD OF INSERT
16AS
17 IF @@ROWCOUNT = 0 RETURN
18 SET NOCOUNT ON -- zwiększa wydajność, zmniejsza traffic, nie wypisuje obrabiane linie w messages
19
20 BEGIN
21 IF NOT EXISTS ((SELECT*FROM Production.Products a, inserted i WHERE a.productname = i.productname))
22 BEGIN
23 INSERT INTO Logs (date,tablename,operationdetails) VALUES(GETDATE(), 'Production.Products', 'Added successfully')
24 INSERT INTO Production.Products(productname,supplierid,categoryid,unitprice,discontinued)
25 SELECT productname, supplierid, categoryid, unitprice, discontinued FROM inserted
26 PRINT 'Added successfully'
27 END
28 ELSE
29 BEGIN
30 PRINT 'Juz istnieje produkt o takiej nazwie'
31 INSERT INTO Logs (date,tablename,operationdetails) VALUES(GETDATE(), 'Production.Products', 'Adding crashed')
32 END
33 END
34
35/*Można tu sprawdzić, pierwszy produkt istnieje, drugi - nie*/
36INSERT INTO Production.Products(productname,supplierid,categoryid,unitprice,discontinued) VALUES ('Product LUNZZ',2,3,1000,1)
37INSERT INTO Production.Products(productname,supplierid,categoryid,unitprice,discontinued) VALUES ('Product ALEXA',2,3,1000,1)
38GO
39
40/*Trigger na edytowanie w Production.Products*/
41CREATE TRIGGER trigDelProductionProducts
42ON Production.Products
43INSTEAD OF DELETE
44AS
45 IF @@ROWCOUNT = 0 RETURN
46 SET NOCOUNT ON -- zwiększa wydajność, zmniejsza traffic, nie wypisuje obrabiane linie w messages
47
48 BEGIN
49 DECLARE @id INT
50 SELECT @id=productid FROM deleted
51 IF NOT EXISTS ((SELECT*FROM deleted i, Sales.OrderDetails b
52 WHERE i.productid = b.productid))
53 BEGIN
54 DELETE FROM Production.Products WHERE productid = @id
55 PRINT 'Deleted successfully'
56 INSERT INTO Logs (date,tablename,operationdetails) VALUES(GETDATE(), 'Production.Products', 'Deleted successfully')
57 END
58 ELSE
59 BEGIN
60 PRINT 'Deleting crashed. This product has a reference.'
61 INSERT INTO Logs (date,tablename,operationdetails) VALUES(GETDATE(), 'Production.Products', 'Deleting crashed')
62 END
63 END
64
65/*Pierwszy dodany przez INSERT(brak referencji), drugi jest powiązany z inną tablicą*/
66DELETE FROM Production.Products WHERE productname = 'ALEXA UPDATED'
67DELETE FROM Production.Products WHERE productname = 'Product LUNZZ'
68GO
69
70/*trigger UPDATE*/
71CREATE TRIGGER trigUpdProductionProducts
72ON Production.Products
73INSTEAD OF UPDATE
74AS
75 IF @@ROWCOUNT = 0 RETURN
76 SET NOCOUNT ON -- zwiększa wydajność, zmniejsza traffic, nie wypisuje obrabiane linie w messages
77
78 BEGIN
79 IF EXISTS (SELECT * FROM inserted a, deleted b WHERE a.productname <> b.productname OR a.supplierid <> b.supplierid OR a.categoryid <> b.categoryid OR a.unitprice <> b.unitprice OR a.discontinued <> b.discontinued)
80 BEGIN
81 UPDATE Production.Products
82 SET productname = i.productname, supplierid = i.supplierid, categoryid = i.categoryid, unitprice = i.unitprice, discontinued = i.discontinued
83 FROM Production.Products a
84 INNER JOIN inserted i ON a.productid = i.productid
85 PRINT 'Dane zostały zaktualizowane pomyślnie'
86 INSERT INTO Logs (date,tablename,operationdetails) VALUES(GETDATE(), 'Production.Products', 'Updated successfully')
87 END
88 ELSE
89 BEGIN
90 PRINT 'Błąd zaktualizowania danych. Nowe i stare dane są identyczne. Prośba wpisać inne dane'
91 INSERT INTO Logs (date,tablename,operationdetails) VALUES(GETDATE(), 'Production.Products', 'Updating crashed')
92 END
93 END
94
95/*Pierwsze odnowianie wykona się na nowym produkcie pomyślnie, drugie - musi spróbować zmienić dane na takie same w 'starym' produkcie,ale wyskoczy błąd*/
96UPDATE Production.Products SET productname = 'ALEXA UPDATED' WHERE productid = 78
97UPDATE Production.Products SET supplierid = 12 WHERE productid = 77