Bitcoin Forum
November 20, 2017, 06:35:53 AM *
News: Latest stable version of Bitcoin Core: 0.15.1  [Torrent].
 
   Home   Help Search Donate Login Register  
Pages: [1]
  Print  
Author Topic: [Need] SQL Expert (Triggers)  (Read 242 times)
OmegaStarScream
Staff
Legendary
*
Offline Offline

Activity: 1106



View Profile
May 13, 2017, 08:25:58 AM
 #1

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

Join ICO Now Coinlancer is Disrupting the Freelance marketplace!
Advertised sites are not endorsed by the Bitcoin Forum. They may be unsafe, untrustworthy, or illegal in your jurisdiction. Advertise here.
1511159753
Hero Member
*
Offline Offline

Posts: 1511159753

View Profile Personal Message (Offline)

Ignore
1511159753
Reply with quote  #2

1511159753
Report to moderator
Kencha77
Full Member
***
Offline Offline

Activity: 238

Mada Mada Da Ne


View Profile
May 13, 2017, 09:36:38 AM
 #2

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

BRIDGING THE GAP BETWEEN CRYPTOCURRENCY AND FIAT   P A Y C E N T     WHITEPAPER | LINK TO DISCUSSION   ICO: 2-30 Nov ’17
OmegaStarScream
Staff
Legendary
*
Offline Offline

Activity: 1106



View Profile
May 13, 2017, 10:05:09 AM
 #3

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
Hero Member
*****
Offline Offline

Activity: 910


「きみはこれ&#


View Profile
May 13, 2017, 11:21:11 AM
 #4

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 ?

  ████
█ ████
█ ████
█ ████
█ ████ █
█ ████ █
█ ████ █
█ ████ █
█ ████ █
  ████ █
  ████ █
  ████ █
  ████
  ████
█ ████
█ ████
█ ████
█ ████ █
█ ████ █
█ ████ █
█ ████ █
█ ████ █
  ████ █
  ████ █
  ████ █
  ████
  .WEBSITE.
▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄
  .ANN THREAD.
.
▄▄▄▄▄▄▄▄
  ████
█ ████
█ ████
█ ████
█ ████ █
█ ████ █
█ ████ █
█ ████ █
█ ████ █
  ████ █
  ████ █
  ████ █
  ████
Kencha77
Full Member
***
Offline Offline

Activity: 238

Mada Mada Da Ne


View Profile
May 13, 2017, 12:44:59 PM
 #5

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)

BRIDGING THE GAP BETWEEN CRYPTOCURRENCY AND FIAT   P A Y C E N T     WHITEPAPER | LINK TO DISCUSSION   ICO: 2-30 Nov ’17
OmegaStarScream
Staff
Legendary
*
Offline Offline

Activity: 1106



View Profile
May 13, 2017, 01:25:45 PM
 #6

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.

Pages: [1]
  Print  
 
Jump to:  

Sponsored by , a Bitcoin-accepting VPN.
Powered by MySQL Powered by PHP Powered by SMF 1.1.19 | SMF © 2006-2009, Simple Machines Valid XHTML 1.0! Valid CSS!