Bitcoin Forum
April 23, 2018, 07:52:25 AM *
News: Latest stable version of Bitcoin Core: 0.16.0  [Torrent]. (New!)
 
   Home   Help Search Donate Login Register  
Pages: [1]
  Print  
Author Topic: [Need] SQL Expert (Triggers)  (Read 245 times)
OmegaStarScream
Staff
Legendary
*
Offline Offline

Activity: 1260
Merit: 1065


Hire BOUNTYPORTALS>Bounty management goo.gl/XKv9TK


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

1524469945
Hero Member
*
Offline Offline

Posts: 1524469945

View Profile Personal Message (Offline)

Ignore
1524469945
Reply with quote  #2

1524469945
Report to moderator
1524469945
Hero Member
*
Offline Offline

Posts: 1524469945

View Profile Personal Message (Offline)

Ignore
1524469945
Reply with quote  #2

1524469945
Report to moderator
Advertised sites are not endorsed by the Bitcoin Forum. They may be unsafe, untrustworthy, or illegal in your jurisdiction. Advertise here.
1524469945
Hero Member
*
Offline Offline

Posts: 1524469945

View Profile Personal Message (Offline)

Ignore
1524469945
Reply with quote  #2

1524469945
Report to moderator
1524469945
Hero Member
*
Offline Offline

Posts: 1524469945

View Profile Personal Message (Offline)

Ignore
1524469945
Reply with quote  #2

1524469945
Report to moderator
Kencha77
Sr. Member
****
Offline Offline

Activity: 364
Merit: 251


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

       


████
████
████
████
████
████
████
████
████
████
████
████
████
████
████
               ▄▄▄▄▄▄
          ▄▄███████████▄████▄
       ▄████████████████ ▄▄ █
     ▄███████▀▀▀     ▀▀█ ▀▀ █
   ▄██████▀            ▀████▀          ██
  ▐█████▀                            █████
 ▐█████                             ███████
 █████          ▄███▄                ██████▄
▐████          ███████         ██     ██████
█████          ███████       █████    ██████
█████           ▀███▀      ███████    ██████
█████            ███         █████    ██████
▐████           █████          ██     ██████
 █████         ███████               ██████▀
 ▐█████                             ███████
  ▐█████▄                            █████
   ▀██████▄            ▄████▄          ██
     ▀███████▄▄▄     ▄▄█ ▄▄ █
       ▀████████████████ ▀▀ █
          ▀▀███████████▀████▀
               ▀▀▀▀▀▀





                ▄▄▄▄
        ▄▄▄▄████████
▄▄▄▄████████████████
████████████████████
████████████████████
████████████████████
██████████████▀  ▀██
██████████████    ██
██████████████▄  ▄██
████████████████████
████████████████████
████████████████████
████████████████████
████████████████████
████████████████▀▀▀▀
████████▀▀▀▀
▀▀▀▀
   
WHITE PAPER
HOME PAGE
THE ICO
       


████
████
████
████
████
████
████
████
████
████
████
████
████
████
████
Sr.
OmegaStarScream
Staff
Legendary
*
Offline Offline

Activity: 1260
Merit: 1065


Hire BOUNTYPORTALS>Bounty management goo.gl/XKv9TK


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
Merit: 1000


「きみはこれ&#


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
Sr. Member
****
Offline Offline

Activity: 364
Merit: 251


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)

       


████
████
████
████
████
████
████
████
████
████
████
████
████
████
████
               ▄▄▄▄▄▄
          ▄▄███████████▄████▄
       ▄████████████████ ▄▄ █
     ▄███████▀▀▀     ▀▀█ ▀▀ █
   ▄██████▀            ▀████▀          ██
  ▐█████▀                            █████
 ▐█████                             ███████
 █████          ▄███▄                ██████▄
▐████          ███████         ██     ██████
█████          ███████       █████    ██████
█████           ▀███▀      ███████    ██████
█████            ███         █████    ██████
▐████           █████          ██     ██████
 █████         ███████               ██████▀
 ▐█████                             ███████
  ▐█████▄                            █████
   ▀██████▄            ▄████▄          ██
     ▀███████▄▄▄     ▄▄█ ▄▄ █
       ▀████████████████ ▀▀ █
          ▀▀███████████▀████▀
               ▀▀▀▀▀▀





                ▄▄▄▄
        ▄▄▄▄████████
▄▄▄▄████████████████
████████████████████
████████████████████
████████████████████
██████████████▀  ▀██
██████████████    ██
██████████████▄  ▄██
████████████████████
████████████████████
████████████████████
████████████████████
████████████████████
████████████████▀▀▀▀
████████▀▀▀▀
▀▀▀▀
   
WHITE PAPER
HOME PAGE
THE ICO
       


████
████
████
████
████
████
████
████
████
████
████
████
████
████
████
Sr.
OmegaStarScream
Staff
Legendary
*
Offline Offline

Activity: 1260
Merit: 1065


Hire BOUNTYPORTALS>Bounty management goo.gl/XKv9TK


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!