BD3_K2_Z2_g1

--Zadanie 2
--i ogólnie te CREATE, ON, AS, BEGIN i inne można z dużych napisać wtedy będzie inaczej wyglądać
create trigger t_prod_update
on Production.Products
after update
as
begin
    if @@rowcount=0 return
    set nocount on

    declare @productid int;
    set @productid = (select productid from deleted);

    if ((select productname from inserted) != (select productname from deleted))
    begin
    insert into Historia(tabela, operacja, recordid, oldvalue, newvalue, dataoperacji)
        values ('Products', 'update', @productid, (select productname from deleted), (select productname from inserted), getdate());
    end

    if ((select supplierid from inserted) != (select supplierid from deleted))
    begin
    insert into Historia(tabela, operacja, recordid, oldvalue, newvalue, dataoperacji)
        values ('Products', 'update', @productid, (select supplierid from deleted), (select supplierid from inserted), getdate());
    end

    if ((select categoryid from inserted) != (select categoryid from deleted))
    begin
    insert into Historia(tabela, operacja, recordid, oldvalue, newvalue, dataoperacji)
        values ('Products', 'update', @productid, (select categoryid from deleted), (select categoryid from inserted), getdate());
    end

    if ((select unitprice from inserted) != (select unitprice from deleted))
    begin
    insert into Historia(tabela, operacja, recordid, oldvalue, newvalue, dataoperacji)
        values ('Products', 'update', @productid, (select unitprice from deleted), (select unitprice from inserted), getdate());
    end

    if ((select discontinued from inserted) != (select discontinued from deleted))
    begin
    insert into Historia(tabela, operacja, recordid, oldvalue, newvalue, dataoperacji)
        values ('Products', 'update', @productid, (select discontinued from deleted), (select discontinued from inserted), getdate());
    end
end
go


update Production.Products set productname = 'test PRODUKT', unitprice = 21.09 where productid = 3 -- tu inne wartości na testa i inne produkt id
select * from Historia
select * from Production.Products where productid = 3 -- tu inne produkt id (takie jak wyzej)