Monn_9999

Laboratorium5

Jan 3rd, 2021
1,080
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. USE TSQL2012
  2. GO
  3.  
  4. DROP TABLE dbo.Logs
  5.  
  6. CREATE TABLE Logs(
  7.     logid INT IDENTITY (1,1),
  8.     date DATE,
  9.     tablename VARCHAR(128),
  10.     operationdetails VARCHAR(128)
  11. );
  12.  
  13. DROP TRIGGER Production.trigger_insert;
  14. GO
  15.  
  16. CREATE TRIGGER trigger_insert ON Production.Products INSTEAD OF INSERT
  17. AS
  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;
  32. END;
  33.  
  34. DROP TRIGGER Production.trigger_update;
  35. GO
  36.  
  37. CREATE TRIGGER trigger_update ON Production.Products INSTEAD OF UPDATE
  38. AS
  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;
  57. END;
  58.  
  59. DROP TRIGGER Production.trigger_delete
  60. GO
  61.  
  62. CREATE TRIGGER trigger_delete ON Production.Products INSTEAD OF DELETE
  63. AS
  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;
  80. END;
  81.  
  82. SELECT * FROM Logs
  83. SELECT * FROM Production.Products
  84.  
  85. INSERT INTO Production.Products(productname, supplierid, categoryid, unitprice, discontinued)
  86. VALUES('Prokerka2', 3, 2, 99.88, 0)
  87.  
  88. INSERT INTO Production.Products(productname, supplierid, categoryid, unitprice, discontinued)
  89. VALUES('Prokerka2', 2, 1, 99.88, 0)
  90.  
  91. DELETE FROM Production.Products WHERE productid = 78
  92. DELETE FROM Production.Products WHERE productname = 'Product HHYDP'
  93.  
  94. UPDATE Production.Products SET productname = 'Update Check' WHERE productid = 85
  95. UPDATE Production.Products SET categoryid = '1' WHERE productid = '85'
RAW Paste Data