CODEWITHSUNDEEP
sql-servertriggersinsert-update
Сергей74rus
Сергей74rus

Reputation: 179

Update trigger, add not working properly

Sorry in advance for my English, here is the problem: as I can not to think about the logic of the trigger, the bottom line is that there are 2 tables:

When adding records to a table in the magazine of the parish shall operate a trigger which checks if the table is empty add a new entry (Material, quantity in stock), if the table has records then update them on the condition that the Material = (select the part Name From inserted), but if the table has records and Material != select the part Name From inserted) then add a new record.

Here is the code of the trigger:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER Trigger [dbo].[Application Log Sheet] 
ON [dbo].[Parish magazine]
FOR INSERT 
AS
     DECLARE @a INT;
     DECLARE @b int;

     SELECT @a = COUNT(*) 
     FROM Bill of materials 

     IF @a > 0
     BEGIN
         UPDATE Vedomostithe
         SET Kolichestvennogo = the Number of stock + (select Quantity from inserted)
         WHERE Material = (select the part Name from inserted)
     END
     ELSE IF @a >= 0 
        INSERT INTO Bill of material(Material, Quantity in stock)
            SELECT the part Name, Number
            FROM the arrival Journal
            WHERE [ID] IN (SELECT [ID] FROM inserted)

Table bill of materials:

create table Bill of materials 
(
     ID int primary key,
     Material nvarchar(max),
     Quantity in stock int
)

Table arrival journal:

create table Journal of the parish
(
     ID int primary key,
     Part name nvarchar(max),
     The number of int
)

Upvotes: 1

Views: 53

Answers (1)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239646

On the one hand, you shouldn't store data that can be calculated, since all that does is give you an opportunity for the stored calculation to be inconsistent with the rest of the data. As such, I'd usually recommend just having a view that shows the totals.

On the other hand, this looks like a straightforward task for MERGE. But we need to bear in mind that inserted can contain multiple rows, and these could be for multiple different parts. So, we want something like:

ALTER Trigger [dbo].[Application Log Sheet] 
ON [dbo].[Parish magazine]
for insert
AS

MERGE INTO [Bill of Materials] t
USING (SELECT PART, SUM(Quantity) as Qty from inserted group by PART) s
ON t.Part = s.Part
WHEN MATCHED THEN UPDATE SET Quantity = t.Quantity + s.Qty
WHEN NOT MATCHED THEN INSERT (Part,Quantity) VALUES (s.Part,s.Qty)

Apologies if some of the names are incorrect above, but hopefully you can see how to adapt it to fit your requirements (you questions seems to flip-flop between various table/column names such that there seem to be about four different table names, whereas you start your question by saying that there are two).

Upvotes: 1

Related Questions