Laboratorium5

USE TSQL2012
GO

DROP TABLE dbo.Logs

CREATE TABLE Logs(
    logid INT IDENTITY (1,1),
    date DATE,
    tablename VARCHAR(128),
    operationdetails VARCHAR(128)
);

DROP TRIGGER Production.trigger_insert;
GO

CREATE TRIGGER trigger_insert ON Production.Products INSTEAD OF INSERT
AS
    BEGIN
    SET NOCOUNT ON;
    IF NOT EXISTS(SELECT * FROM Production.Products pp, inserted WHERE inserted.productname = pp.productname)
        BEGIN
        SET NOCOUNT ON;
        INSERT INTO Production.Products(productname, supplierid, categoryid, unitprice, discontinued)
        SELECT productname, supplierid, categoryid, unitprice, discontinued FROM inserted
        INSERT INTO Logs(date, tablename, operationdetails) VALUES (GETDATE(), 'Production.Products', 'I')
        END;
    ELSE
        BEGIN
        INSERT INTO Logs(date, tablename, operationdetails) VALUES (GETDATE(), 'Production.Products', 'Error on I')
        RAISERROR('This product is already exists', 1, 16)
        END;
END;

DROP TRIGGER Production.trigger_update;
GO

CREATE TRIGGER trigger_update ON Production.Products INSTEAD OF UPDATE
AS
    BEGIN
    SET NOCOUNT ON;

    IF EXISTS(SELECT * FROM inserted i, deleted d WHERE i.productname <> d.productname OR i.supplierid <> d.supplierid
        OR i.unitprice <> d.unitprice OR i.discontinued <> d.discontinued)
        BEGIN
            UPDATE Production.Products
            SET productname = i.productname, supplierid = i.supplierid, categoryid = i.categoryid, unitprice = i.unitprice,
                discontinued = i.discontinued
            FROM Production.Products pp
            INNER JOIN inserted i ON pp.productid = i.productid
            INSERT INTO Logs(date, tablename, operationdetails) VALUES (GETDATE(), 'Production.Products', 'U')
        END;
    ELSE
        BEGIN
            INSERT INTO Logs(date, tablename, operationdetails) VALUES (GETDATE(), 'Production.Products', 'Error on U')
            RAISERROR('Provided data are already in use. Please, provide new data.', 1, 16);
        END;
END;

DROP TRIGGER Production.trigger_delete
GO

CREATE TRIGGER trigger_delete ON Production.Products INSTEAD OF DELETE
AS
    BEGIN
    SET NOCOUNT ON;
    DECLARE @id INT;
    DECLARE @count INT;
    SELECT @id = productid FROM deleted;
    SELECT @count = COUNT(*) FROM Sales.OrderDetails WHERE productid = @id;
        IF @count = 0
            BEGIN
            DELETE FROM Production.Products WHERE productid = @id
            INSERT INTO Logs(date, tablename, operationdetails) VALUES (GETDATE(), 'Production.Products', 'D')
            END;
        ELSE
            BEGIN
            INSERT INTO Logs(date, tablename, operationdetails) VALUES (GETDATE(), 'Production.Products', 'Error on D')
            RAISERROR('Cannot delete - Production.Products is referenced in other tables', 1, 16);
            END;
END;

SELECT * FROM Logs
SELECT * FROM Production.Products

INSERT INTO Production.Products(productname, supplierid, categoryid, unitprice, discontinued)
VALUES('Prokerka2', 3, 2, 99.88, 0)

INSERT INTO Production.Products(productname, supplierid, categoryid, unitprice, discontinued)
VALUES('Prokerka2', 2, 1, 99.88, 0)

DELETE FROM Production.Products WHERE productid = 78
DELETE FROM Production.Products WHERE productname = 'Product HHYDP'

UPDATE Production.Products SET productname = 'Update Check' WHERE productid = 85
UPDATE Production.Products SET categoryid = '1' WHERE productid = '85'