0

I have a database with 3 tables and I am updating a row in the first, and then trying to update the second and third tables based on the change to a field in the first.

Here is what I have at present:

--Session--
User   Hotspot   DownloadCounter
bob    123       0              

--UserDownload--
User     Download
bob      100   

--HotspotDownload--
Hotspot  Download 
123      300

Currently when I get an update from the hotspot I update the session table with the latest traffic counter value, however I would now like to update the UserDownload & HotspotDownload tables with the value of the new DownloadCounter - the old DownloadCounter.

I thought this would be easy with the following procedure:

DECLARE increment INT;

UPDATE Session
    SET @increment  = (200 - Counter),
    Counter = 200
    WHERE (User = 'bob' AND Circuit = '123');

UPDATE UserDownload
    SET Total = (Total + @increment)
    WHERE  (User = 'bob');

UPDATE HotspotDownload
    SET Total = (Total + @increment)
    WHERE  (Circuit = '123');

The idea being I update the Session table and assign the counter change value to a variable which is then used in the update of the Download tables. But the '@' causes a syntax error in the procedure, and without the '@' it thinks increment is a column so fails.

If there are not entries for the user/hotspot in the total tables I still need to update the sessions table.

I tried doing this with a single update and a left join, but that resulted in the Sessions table column being updated first and so then the Download tables would not be updated.

Is it possible to assign the result of a calculation on columns to a variable within an update, and then use that update within another update in the procedure?

Thanks.

Dorris Norris
  • 1
  • 1

1 Answers1

0

OK, looks like the way to do this is triggers.

Have the procedure just update the Session table, and then have a trigger against the table which runs before the actual update and updates the 2nd and 3rd tables.

Dorris Norris
  • 1
  • 1