Создать триггер на 2 таблицы

create trigger Ins_Detzakaz
on Detzakaz for insert
as
update Products set Pr_Kol = Pr_Kol + i.dz_kol
from inserted i
inner join Products p on p.PID = i.PID
go
 
create trigger Del_Detzakaz
on Detzakaz for delete
as
update Products set Pr_Kol = Pr_Kol - 
(select sum(d.dz_kol) from deleted d left join Products p on p.PID = d.PID group by d.PID)
from deleted d
where d.PID = Products.PID
go
 
create trigger Upd_Detzakaz
on Detzakaz for update
as
update Products set Pr_Kol = Pr_Kol
- (select sum(d.dz_kol) from deleted d left join Products p on p.PID = d.PID group by d.PID)
+ (select sum(i.dz_kol) from inserted i left join Products p on p.PID = i.PID group by i.PID) 
from deleted d 
inner join inserted i on i.PID = d.PID
where Products.PID = d.PID and Products.PID = i.PID
go

create trigger Ins_Detzakaz
on Detzakaz for insert
as
update Products set Pr_Kol = Pr_Kol + i.dz_kol
from inserted i
inner join Products p on p.PID = i.PID
go
 
create trigger Del_Detzakaz
on Detzakaz for delete
as
update Products set Pr_Kol = Pr_Kol - 
isnull((select sum(d.dz_kol) from deleted d where d.PID = Products.PID group by d.PID),0)
go
 
create trigger Upd_Detzakaz
on Detzakaz for update
as
update Products set Pr_Kol = Pr_Kol
- isnull((select sum(d.dz_kol) from deleted d where d.PID = Products.PID group by d.PID),0)
+ isnull((select sum(i.dz_kol) from inserted i where i.PID = Products.PID group by i.PID),0) 
go

-- Добавте в существующий триггер конструкцией Alter или создайте заново(предварительно удалив существующие)...
CREATE TRIGGER Ins_Detzakaz
ON Detzakaz FOR INSERT
AS
UPDATE Products SET Pr_Kol = Pr_Kol + i.dz_kol
FROM inserted i
INNER join Products p ON p.PID = i.PID
EXEC(dz_total_sum) -- вызываем процедуру для подсчёта суммы по составляющим заказа
UPDATE Zakaz SET dz_itog_sum = dz_itog_sum + i.dz_total_sum
FROM inserted i
INNER join Zakaz z on z.ZID = i.ZID
go
 
CREATE TRIGGER Del_Detzakaz
ON Detzakaz FOR DELETE
AS
UPDATE Products SET Pr_Kol = Pr_Kol - 
isnull((SELECT SUM(d.dz_kol) FROM deleted d WHERE d.PID = Products.PID GROUP BY d.PID),0)
EXEC(dz_total_sum) -- вызываем процедуру для подсчёта суммы по составляющим заказа
UPDATE Zakaz SET dz_itog_sum = dz_itog_sum - 
isnull((SELECT SUM(d.dz_total_sum) FROM deleted d WHERE d.ZID = Zakaz.ZID GROUP BY d.ZID),0)
go
 
CREATE TRIGGER Upd_Detzakaz
ON Detzakaz FOR UPDATE
AS
UPDATE Products SET Pr_Kol = Pr_Kol
- isnull((SELECT SUM(d.dz_kol) FROM deleted d WHERE d.PID = Products.PID GROUP BY d.PID),0)
+ isnull((SELECT SUM(i.dz_kol) FROM inserted i WHERE i.PID = Products.PID GROUP BY i.PID),0) 
EXEC(dz_total_sum) -- вызываем процедуру для подсчёта суммы по составляющим заказа
UPDATE Zakaz SET dz_itog_sum = dz_itog_sum
- isnull((SELECT SUM(d.dz_total_sum) FROM deleted d WHERE d.ZID = Zakaz.ZID GROUP BY d.ZID),0)
+ isnull((SELECT SUM(i.dz_total_sum) FROM inserted i WHERE d.ZID = Zakaz.ZID GROUP BY i.ZID),0)
go

alter table Zakaz add dz_total_sum as dz_kol * dz_cost_per_unit

create trigger Instead_Ins_Detzayavka on Detzayavka
instead of insert
as
begin
if exists(select 1 from inserted i inner join Products p on p.PID = i.PID 
           where p.Pr_kol < (select sum(dza_kol) from Detzayavka where PID = i.PID) + i.dza_kol)
    raiserror('Запись невозможна. На складе нет такого количества',10,10)
-- Сообщение выведится в закладке сообщений managemt studio, запись не вставится в Detzayavka
end
go

CREATE TRIGGER Instead_Ins_Detzayavka ON Detzayavka
instead OF INSERT
AS
BEGIN
IF exists(SELECT 1 FROM inserted i INNER join Products p ON p.PID = i.PID 
           WHERE p.Pr_kol < (SELECT SUM(dza_kol) FROM Detzayavka WHERE PID = i.PID) + i.dza_kol)
        RAISERROR('Запись невозможна. На складе нет такого количества',10,10)
ELSE
INSERT INTO Detzayavka SELECT * FROM inserted
-- Сообщение выведится в закладке сообщений managemt studio, запись не вставится в Detzayavka
END
go