[Need] SQL Expert (Triggers) | Bitcointalksearch.org

OmegaStarScream

staff

Activity: 3472

Merit: 6129

Quote from: KenR on May 13, 2017, 07:21:11 AM

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.

Quote from: Kencha77 on May 13, 2017, 08:44:59 AM

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.

Kencha77

sr. member

Activity: 574

Merit: 251

Quote from: OmegaStarScream on May 13, 2017, 06:05:09 AM

Quote from: Kencha77 on May 13, 2017, 05:36:38 AM

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)

KenR

hero member

Activity: 910

Merit: 1000

「きみはこれ&#

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 ?

OmegaStarScream

staff

Activity: 3472

Merit: 6129

Quote from: Kencha77 on May 13, 2017, 05:36:38 AM

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.

Kencha77

sr. member

Activity: 574

Merit: 251

Quote from: OmegaStarScream on May 13, 2017, 04:25:58 AM

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

staff

Activity: 3472

Merit: 6129

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

Topic: [Need] SQL Expert (Triggers) (Read 320 times)