---insert after - после того как добавили материал\фиттинг в поставку, изменять количество товаров на складе
---insert instead of - вставка нового материала или фиттинга
--ALTER TRIGGER TR_pr_in_supply_insert
--ON Product_in_supply AFTER INSERT

--SELECT * FROM Material
--SELECT * FROM Fitting

--drop trigger TR_material_insert

CREATE TRIGGER TR_material_insert
ON Material INSTEAD OF INSERT
AS
IF @@ROWCOUNT = 1
BEGIN
    DECLARE @x INT, @am FLOAT, @pr FLOAT, @n NVARCHAR(30), @M1 INT, @F1 INT, @AUX FLOAT
    SELECT @x = ID, @am = Amount, @pr = Selling_price,
        @n = Name from inserted
    IF NOT EXISTS (SELECT * FROM Material WHERE @n = Name)
    BEGIN
        SET @M1 = 0
        SET @F1 = 0
        SELECT @M1 = MAX(ID) FROM Material
        SELECT @F1 = MAX(ID) FROM Fitting
        IF(@F1 > @M1)
            SET @M1 = @F1
        SET @M1 = @M1 + 1
        INSERT  INTO [dbo].[Material] VALUES (@M1, @am, @pr, @n)
    END
    ELSE
    BEGIN
        SELECT @x = ID, @AUX = Selling_price
        FROM Material
        WHERE @n = Name

        IF(@pr > @AUX)
        BEGIN
            UPDATE Material
            SET Selling_price = @pr
            WHERE ID = @x
        END

        UPDATE Material
        SET Amount = Amount + @am
        WHERE ID = @x
    END
END

--insert after - после того как добавили материал\фиттинг в поставку, изменять количество товаров на складе

CREATE TRIGGER TR_product_in_supply_insert
ON Product_in_supply AFTER INSERT
AS
IF @@ROWCOUNT = 1
BEGIN
    DECLARE @x INT, @am FLOAT, @pr FLOAT, @M1 FLOAT, @F1 FLOAT
    SELECT @x = ID_Product, @am = Amount, @pr = Purchase_price from inserted
    IF EXISTS (SELECT * FROM Material WHERE @x = ID)
    BEGIN
        SELECT @M1 = Selling_price FROM Material WHERE @x = ID
        IF @M1 < @pr
                SET @M1 = @pr * 1.1

        UPDATE Material
        SET Amount = Amount + @am, Selling_price = @M1
        WHERE ID = @x
    END
    ELSE IF EXISTS (SELECT * FROM Fitting WHERE @x = ID)
    BEGIN
        SELECT @F1 = Selling_price FROM Fitting WHERE @x = ID
        IF @F1 < @pr
                SET @F1 = @pr * 1.1

        UPDATE Fitting
        SET Amount = Amount + @am, Selling_price = @F1
        WHERE ID = @x
    END
    ELSE
        RAISERROR('The item does not exist in the database', 16, 10)
END