[Need] SQL Expert (Triggers)

[OmegaStarScream]

Let's assume that I have the following code:

Code:

-- create the two tables
CREATE TABLE TABLE_2 (ID INT NOT NULL IDENTITY(1,1), ProdName VARCHAR(50))
CREATE TABLE TABLE_1 (ProdName VARCHAR(50), Bought INT)
GO

-- create trigger on "TABLE_2" to update "TABLE_1"    
CREATE TRIGGER T2Insert
ON TABLE_2
AFTER INSERT
AS
    UPDATE T1
    SET Bought = Bought + 1
    FROM TABLE_1 T1
    INNER JOIN Inserted i ON T1.ProdName = i.ProdName
    WHERE T1.Bought < 100
GO

-- initialize TABLE_1 with some seed data
INSERT INTO dbo.TABLE_1 (ProdName, Bought)
VALUES ( 'Prod1', 0), ('Prod2', 20), ('Prod3', 40), ('Prod4', 40), ('Prod100', 100)

-- insert new values into TABLE_2
INSERT INTO dbo.TABLE_2 (ProdName)
VALUES  ('Prod1'), ('Prod100'), ('Prod2'), ('Prod4')

-- get data to check
SELECT * FROM dbo.TABLE_1

The trigger works fine. However, If I insert something like this on TABLE_2 :

Code:

INSERT INTO dbo.TABLE_2 (ProdName)
VALUES  ('Prod1'), ('Prod1')

It only gets updated once. basically the SET Bought = Bought + 1 updates once instead of twice/or whatever the number of values I try to insert.

If you can solve this issue, let me know In the thread and how much $ you are looking for

[Kencha77]

Let's assume that I have the following code:

Code:

-- create the two tables
CREATE TABLE TABLE_2 (ID INT NOT NULL IDENTITY(1,1), ProdName VARCHAR(50))
CREATE TABLE TABLE_1 (ProdName VARCHAR(50), Bought INT)
GO

-- create trigger on "TABLE_2" to update "TABLE_1"    
CREATE TRIGGER T2Insert
ON TABLE_2
AFTER INSERT
AS
    UPDATE T1
    SET Bought = Bought + 1
    FROM TABLE_1 T1
    INNER JOIN Inserted i ON T1.ProdName = i.ProdName
    WHERE T1.Bought < 100
GO

-- initialize TABLE_1 with some seed data
INSERT INTO dbo.TABLE_1 (ProdName, Bought)
VALUES ( 'Prod1', 0), ('Prod2', 20), ('Prod3', 40), ('Prod4', 40), ('Prod100', 100)

-- insert new values into TABLE_2
INSERT INTO dbo.TABLE_2 (ProdName)
VALUES  ('Prod1'), ('Prod100'), ('Prod2'), ('Prod4')

-- get data to check
SELECT * FROM dbo.TABLE_1

The trigger works fine. However, If I insert something like this on TABLE_2 :

Code:

INSERT INTO dbo.TABLE_2 (ProdName)
VALUES  ('Prod1'), ('Prod1')

It only gets updated once. basically the SET Bought = Bought + 1 updates once instead of twice/or whatever the number of values I try to insert.

If you can solve this issue, let me know In the thread and how much $ you are looking for

I think problem is because if the same product is bought/inserted more than 1 time at once, the value of bought also increases only by 1. The easiest solution would be only allowing one insert into table 2 at a time

[OmegaStarScream]

I think problem is because if the same product is bought/inserted more than 1 time at once, the value of bought also increases only by 1. The easiest solution would be only allowing one insert into table 2 at a time

That's not an option I'm willing to take unfortunately, A table should have the ability to get multiple INSERT STATEMENTS. I want to edit the trigger to achieve what I'm looking for.

[KenR]

Are there any specific errors thrown during running the trigger ?
I'm not that experienced with triggers but don't you have to use the

Code:

FOR EACH ROW
       BEGIN

loop to have it impacted on all the rows ?

[Kencha77]

I think problem is because if the same product is bought/inserted more than 1 time at once, the value of bought also increases only by 1. The easiest solution would be only allowing one insert into table 2 at a time

That's not an option I'm willing to take unfortunately, A table should have the ability to get multiple INSERT STATEMENTS. I want to edit the trigger to achieve what I'm looking for.

SET Bought = Bought + COUNT(Inserted.ProdName)

[OmegaStarScream]

Are there any specific errors thrown during running the trigger ?
I'm not that experienced with triggers but don't you have to use the

Code:

FOR EACH ROW
BEGIN

loop to have it impacted on all the rows ?

I said that the trigger above works fine, there are no errors. Its just not doing what I was hoping for. FOR EACH ROW doesn't exist on SQL Server as far as I know and using a while loop won't work either because the insert can be done using one statement but with multiple values.

SET Bought = Bought + COUNT(Inserted.ProdName)

It won't work because values could vary and be different, It could be two of the same ProdName, 3 from another.

EDIT: I managed to handle this situation by creating a temporary table and storing data there (taking it from inserted) using GROUP BY. I no longer need this.