use TSQL2012

create table Historia(
    id int identity(1,1) not null,
    tabela varchar(20) not null,
    operacja varchar(20) not null,
    recordid int not null,
    oldvalue varchar(100) not null,
    newvalue varchar(100) not null,
    dataoperacji date not null
    )
go

--1--
--tu powinny być transakcje i wyjątki ale nie umiem

select * from Production.Products
select * from Sales.OrderDetails where productid = 1
go

create trigger t_prod_delete
on Production.Products
instead of delete
as
begin
    declare @productid int;
    set @productid = (select productid from deleted);

    delete from Sales.Orders where orderid in (select orderid from Sales.OrderDetails where productid = @productid);
    delete from Sales.OrderDetails where productid = @productid;
    delete from Production.Products where productid = @productid;

    insert into Historia(tabela, operacja, recordid, oldvalue, newvalue, dataoperacji)
        values ('Products', 'delete', @productid, null, null, getdate());
end
go