enter image description here The problem is that, in the table, it is not possible to group the same rows, i.e. judging by the picture I need to get the material-1, the number is 1000, here is the trigger code:

USE [ERP] GO /****** Object: Trigger [dbo].[Заявка_ЖурналВедомость] Script Date: 10.05.2016 20:13:00 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER Trigger [dbo].[Заявка_ЖурналВедомость] ON [dbo].[Журнал_прихода] FOR insert AS if @@ROWCOUNT=0 SET nocount on begin INSERT INTO Ведомость_материалов(Материал,Колличество_на_складе ) SELECT Наименование_детали,sum(Количество) FROM Журнал_прихода where [ID] in(select [ID] from inserted) group by Наименование_детали
  • Strange trigger looks. You always add new lines with the number in the list. that is, if one part arrives in two wards, then in both cases you will add new entries to the list, which is most likely not true. Probably it is worth adding the count to the existing record of the statement if there is a record for this part there or to add a new record - if it is not there. It makes sense to use merge. - Mike

1 answer 1

You'd rather put the table creation DDL script instead of the screen.

Suppose the tables are created as follows:

 create table Ведомость_материалов ( ID int primary key, Материал nvarchar(max), Количество_на_складе int ) create table Журнал_прихода ( ID int primary key, Наименование_детали nvarchar(max), Количество int )

I assume that the Материал column in one table will have the same values ​​as the Наименование_детали column in another.

Then the trigger can be:

 create trigger Заявка_ЖурналВедомость on Журнал_прихода for insert AS if @@ROWCOUNT = 1 begin update Ведомость_материалов set Количество_на_складе = Количество_на_складе + (select Количество from inserted) where Материал = (select Наименование_детали from inserted) end

This trigger is triggered when inserting into one table, updating data in another table.

Fill the table of Material Sheets with initial values:

 insert Ведомость_материалов values (1, N'болт', 0); insert Ведомость_материалов values (2, N'шуруп', 0);

Now, when inserting entries into the Incoming Log:

 insert Журнал_прихода values (1, N'болт', 100); insert Журнал_прихода values (2, N'болт', 200); insert Журнал_прихода values (3, N'шуруп', 1000); insert Журнал_прихода values (4, N'шуруп', 3000);

trigger will trigger and update the amount of material in stock.

 select * from Ведомость_материалов; ID | Материал | Количество_на_складе 1 | болт | 300 2 | шуруп | 4000

Look at this material about triggers, there just examples are similar to what you are doing.