Results 1 to 7 of 7
  1. #1
    Join Date
    May 2004
    Posts
    4

    Unanswered: SQL Trigger: check if record exists before update

    I have a stored procedure that writes transactions (points) every time a user has a point addition or subtraction.

    After a row is written to the Transaction table, I have a User_Balance table that gets updated with the user balance.

    As a check, I want to be sure the userID exists in the user_balance table before the update. Most of my inserts to the User_Transaction table are in a batch from a join, so it can be many 100s of records at a time.

    I made some attempts at adding a userID to the user_Balance table but I am not getting it. You can see my attempts by the commenting

    I need to make sure the UserID exists in the User_Balance table before updating.

    Code:
    ALter TRIGGER tr_UpdateUserBalance
       ON   User_Transaction
       AFTER INSERT	
    AS 
    BEGIN
    	-- SET NOCOUNT ON added to prevent extra result sets from
    	-- interfering with SELECT statements.
    	SET NOCOUNT ON;
    
        -- Insert statements for trigger here
    	--get the inserted userid
    	--DECLARE @TransReason int
    	--DECLARE @TransAmount int
    	--DECLARE @WinAmount int
    	--Select @UserID = UserID, @TransAmount = TransAmount, @WinAmount = WinAmount FROM inserted	
    	--	IF NOT EXISTS(Select a.UserID 
    	--					from User_Balance a INNER JOIN 
    	  --                  Inserted b ON b.UserID = a.userid
    	--					Where a.userid = b.UserID)
    
    		--	BEGIN	
    		--		INSERT INTO User_Balance
    		--		(Userid)
    		--		(Select L.UserID 
    		--				from Inserted L LEFT OUTER JOIN 
    	    --              User_Balance R ON L.UserID = R.userid)
    		--				--Where L.userid = R.UserID)
    
    		--	END	
    
    
    		UPDATE User_Balance
    			SET Balance = CASE i.TransReason							
    							WHEN '0' THEN ([Balance] + ((i.TransAmount ) * i.WinAmount)) 
    							WHEN '5' THEN  ([Balance] + ((i.TransAmount ) - i.WinAmount))
    							WHEN '9' THEN  ([Balance] + i.TransAmount )  
    						End
    			FROM Inserted i
    			WHERE	i.userid = User_Balance.UserID
    		
    END
    GO

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    You don't need to do this - you need to normalise the design.

    Insert a row for the user each time, and then you can use aggregate queries to calculate the balances at run time!
    George
    Home | Blog

  3. #3
    Join Date
    May 2004
    Posts
    4
    Quote Originally Posted by georgev
    You don't need to do this - you need to normalise the design.

    Insert a row for the user each time, and then you can use aggregate queries to calculate the balances at run time!
    That was my original intent, even after I was told I should never store an aggregate table - i did it anyway

    Each transaction has a transactionReason which says what type of transaction it is and I can perform calculations based on that.... I dont think I have enough faith in the power of sql server that it can grab a sum from a table with 1000s of rows.

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    You gotta have faith!

    1000's of rows are nothing. I trust it even when the record count is in the billions and it's not failed me yet
    George
    Home | Blog

  5. #5
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Why trigger to begin with? Why can't you do it in the same stored procedure that issued the INSERT in the first place?..Don't tell me, the insert is fired directly from the front-end, not through a stored procedure...Sad
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Oh and as an example to the OP - I had a very similar problem about a year ago and implemented the normalised solution - the table has now exceeded 250K rows and is doing fine and dandy thanks. Statistics in an instant and no need to worry about locking of phantom reads at all!
    George
    Home | Blog

  7. #7
    Join Date
    May 2004
    Posts
    4
    Quote Originally Posted by rdjabarov
    Why trigger to begin with? Why can't you do it in the same stored procedure that issued the INSERT in the first place?..Don't tell me, the insert is fired directly from the front-end, not through a stored procedure...Sad
    Thanks for the thoughts, but it is fired from a stored procedure.

    Quote Originally Posted by georgev
    Oh and as an example to the OP - I had a very similar problem about a year ago and implemented the normalised solution - the table has now exceeded 250K rows and is doing fine and dandy thanks. Statistics in an instant and no need to worry about locking of phantom reads at all!
    Thanks for the thoughts george. I dumped the aggregate table and spent some of today cleaning up the changes. I will perform all of the calculations on the transaction table.

    I am sure I will have more issues. I will be back soon

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •