LnR4L7XU

1USE TSQL2012
2GO
3
4DROP TABLE dbo.Logs
5
6CREATE TABLE Logs(
7    logid INT IDENTITY (1,1),
8    date DATE,
9    tablename VARCHAR(128),
10    operationdetails VARCHAR(128)
11);
12
13DROP TRIGGER Production.trigger_insert;
14GO
15
16CREATE TRIGGER trigger_insert ON Production.Products INSTEAD OF INSERT
17AS
18    BEGIN
19	SET NOCOUNT ON;
20	IF NOT EXISTS(SELECT * FROM Production.Products pp, inserted WHERE inserted.productname = pp.productname)
21		BEGIN
22		SET NOCOUNT ON;
23		INSERT INTO Production.Products(productname, supplierid, categoryid, unitprice, discontinued)
24		SELECT productname, supplierid, categoryid, unitprice, discontinued FROM inserted
25		INSERT INTO Logs(date, tablename, operationdetails) VALUES (GETDATE(), 'Production.Products', 'I')
26		END;
27	ELSE
28		BEGIN
29		INSERT INTO Logs(date, tablename, operationdetails) VALUES (GETDATE(), 'Production.Products', 'Error on I')
30		RAISERROR('This product is already exists', 1, 16)
31		END;
32END;
33
34DROP TRIGGER Production.trigger_update;
35GO
36
37CREATE TRIGGER trigger_update ON Production.Products INSTEAD OF UPDATE
38AS
39    BEGIN
40    SET NOCOUNT ON;
41
42    IF EXISTS(SELECT * FROM inserted i, deleted d WHERE i.productname <> d.productname OR i.supplierid <> d.supplierid
43        OR i.unitprice <> d.unitprice OR i.discontinued <> d.discontinued)
44        BEGIN
45            UPDATE Production.Products
46            SET productname = i.productname, supplierid = i.supplierid, categoryid = i.categoryid, unitprice = i.unitprice,
47                discontinued = i.discontinued
48            FROM Production.Products pp
49            INNER JOIN inserted i ON pp.productid = i.productid
50            INSERT INTO Logs(date, tablename, operationdetails) VALUES (GETDATE(), 'Production.Products', 'U')
51        END;
52    ELSE
53        BEGIN
54            INSERT INTO Logs(date, tablename, operationdetails) VALUES (GETDATE(), 'Production.Products', 'Error on U')
55            RAISERROR('Provided data are already in use. Please, provide new data.', 1, 16);
56        END;
57END;
58
59DROP TRIGGER Production.trigger_delete
60GO
61
62CREATE TRIGGER trigger_delete ON Production.Products INSTEAD OF DELETE
63AS
64    BEGIN
65    SET NOCOUNT ON;
66	DECLARE	@id INT;
67	DECLARE @count INT;
68	SELECT @id = productid FROM deleted;
69	SELECT @count = COUNT(*) FROM Sales.OrderDetails WHERE productid = @id;
70		IF @count = 0
71			BEGIN
72			DELETE FROM Production.Products WHERE productid = @id
73			INSERT INTO Logs(date, tablename, operationdetails) VALUES (GETDATE(), 'Production.Products', 'D')
74			END;
75		ELSE
76			BEGIN
77			INSERT INTO Logs(date, tablename, operationdetails) VALUES (GETDATE(), 'Production.Products', 'Error on D')
78			RAISERROR('Cannot delete - Production.Products is referenced in other tables', 1, 16);
79			END;
80END;
81
82SELECT * FROM Logs
83SELECT * FROM Production.Products
84
85INSERT INTO Production.Products(productname, supplierid, categoryid, unitprice, discontinued)
86VALUES('Prokerka2', 3, 2, 99.88, 0)
87
88INSERT INTO Production.Products(productname, supplierid, categoryid, unitprice, discontinued)
89VALUES('Prokerka2', 2, 1, 99.88, 0)
90
91DELETE FROM Production.Products WHERE productid = 78
92DELETE FROM Production.Products WHERE productname = 'Product HHYDP'
93
94UPDATE Production.Products SET productname = 'Update Check' WHERE productid = 85
95UPDATE Production.Products SET categoryid = '1' WHERE productid = '85'