CODEWITHSUNDEEP
asp.netsql
Jeff
Jeff

Reputation: 449

Pull a row from SQL database based on if the value of a column is changed

I need to pull a row in a select statement from a SQL database if a certain value in a table is changed.

For example, I have a column called price in a Price table. If the user changes the value for price (through an asp.net app), I want to select that entire row. This is going to be done in a workflow and an email is sent to the user that the row that was changed AFTER it was changed.

Does this make sense? Can someone point me in the right direction of a procedure or function to use? Thanks.

Upvotes: 0

Views: 206

Answers (3)

Conrad Frix
Conrad Frix

Reputation: 52675

Without knowing what your data looks like (or what database this is, it's a little difficult) but assuming you have a history table with a date and an ID that stays the same like this...

+----+-------+------------+
| ID | PRICE | CHNG_DATE  |
+----+-------+------------+
|  1 | 2.5   | 2001-01-01 |
|  1 | 42    | 2001-01-01 |
|  2 | 4     | 2001-01-01 |
|  2 | 4     | 2001-01-01 |
|  3 | 4     | 2001-01-01 |
|  3 | 3     | 2001-01-01 |
|  3 | 2     | 2001-01-01 |
+----+-------+------------+

and your database supports With and Row_number You could write the following 

WITH data 
     AS (SELECT id, 
                price, 
                chng_date, 
                Row_number() 
                  OVER ( 
                    partition BY id 
                    ORDER BY chng_date) rn 
         FROM   price) 
SELECT data.id, 
       data.price     new, 
       data_prv.price old, 
       data.chng_date 
FROM   data 
       INNER JOIN data data_prv 
               ON data.id = data_prv.id 
                  AND data.rn = data_prv.rn + 1 
WHERE  data.price <> data_prv.price

That would produce this

+----+-----+-----+------------+
| ID | NEW | OLD | CHNG_DATE  |
+----+-----+-----+------------+
|  1 |  42 | 2.5 | 2001-01-01 |
|  3 |   3 | 4   | 2001-01-01 |
|  3 |   2 | 3   | 2001-01-01 |
+----+-----+-----+------------+

Demo

If your Database supports LAG() its even eaiser

WITH data 
     AS (SELECT id, 
                price                   new, 
                chng_date, 
                Lag(price) 
                  OVER ( 
                    partition BY id 
                    ORDER BY chng_date) old 
         FROM   price) 
SELECT id, 
       new, 
       old, 
       chng_date 
FROM   data 
WHERE  new <> old

Demo

Upvotes: 0

Kyle B.
Kyle B.

Reputation: 5787

You could use an SQL trigger to accomplish this.

There is a tutorial (using Price as you described) that shows how to accomplish this here: http://benreichelt.net/blog/2005/12/13/making-a-trigger-fire-on-column-change/

Upvotes: 1

Losbear
Losbear

Reputation: 3315

well, in order to update a row, you'll have to update that row "WHERE uniqueID = [someid]". Can't you simply run a select immediately after that? (SELECT * FROM [table] WHERE uniquueID = [someid])

Upvotes: 0

Related Questions