in which rows updated in sql server 2005

Question

0.00/5 (No votes)

See more:

Dear Friend
I have some problem when implementing program.The actual problem is as follows :-

Consider my table User:-
The following attributes:-

userid    name    email           address    phone    company    designation
1          a       a@gmail.com    nagpur      1           x        developer   
2          b       b@gmail.com    pune        2           y          dba             
3          c       c@gmail.com    mumbai      3           z        engineers
4          d       d@gmail.com    nagpur      4           y          developer

if you have updated any information in the user id 1 and 3 just like :-
butes:-

userid    name    email           address    phone    company    designation
1          a       a@gmail.com    pune        2           y       manager   
2          b       b@gmail.com    pune        2           y          dba             
3          c       c@gmail.com    nagpur      5           x       developer
4          d       d@gmail.com    nagpur      4           y       developer

This two userid information updated so my question is "How to understand user this two rows updated or not" ? is any query please give me information as soon as possible .

Thanks in advanced !!

Posted 29-Jul-13 2:31am

Member 8089110

Updated 29-Jul-13 2:50am

Maciej Los

v2

Add a Solution

Comments

ZurdoDev 29-Jul-13 8:37am

What's your question? It is not clear.

Member 8089110 29-Jul-13 8:46am

Sir,if you have updated record in your table for example :- in this table first and third rows updated information so how to understand or in which query i retrieved information this query rows or this id is updated or deleted or inserted ?

Maciej Los 29-Jul-13 8:53am

Still not clear ;(
What to understand? If you write software to manage above data, you need to know who and when made changes.

Member 8089110 29-Jul-13 9:05am

if you have changes in user table so how to understand this table or rows changes. is any query to retrieve information or rows this rows changes ?

Sudhakar Shinde 29-Jul-13 9:25am

You can very well check how many rows are affected by the UPDATE statement. Is this what you are looking for?

Member 8089110 30-Jul-13 0:58am

how to check how many rows are affected by the UPDATE statement ?

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Raja Sekhar S · Answer 1 · 2013-07-29T20:06:00

If You want to know what are the rows updated then u can use do it in two ways..
1. Write a select Statement before Update Statement selecting rows which will be updated Like...

SQL

Select Columnname from TableName Where Condition
Update TableName set column= something Where Condition

2. U can use a TRIGGER for update of that Particular Table and save those records in to some new table...
Check this link for Triggers[^]

SQL

CREATE TRIGGER TriggerName
ON TableName
AFTER UPDATE 
AS 
IF ( UPDATE (ColumnName))
BEGIN
--Do Something
END;

If you just want to Know the no of Rows affected by a statement u can use @@RowCount...

SQL

Create Table #Test(id Int, value Nvarchar(40))

Insert into #Test 
Select 1,'Value1' Union All
Select 2,'Value2' Union All
Select 3,'Value3' Union All
Select 4,'Value4' 

Select @@ROWCOUNT [RowCount After Insert] -- Returns 4

Select * From #Test
Select @@RowCount [RowCount After Select] -- Returns 4

Update #Test Set id=1 where id<4
Select @@RowCount [RowCount After Update] -- Returns 3

Update #Test Set id=1 where id=0
Select @@ROWCOUNT			  -- Returns 0

Drop Table #Test

you can fetch column update's date if column is primary key/foreign key like this

SQL

Select * From sys.objects where type_desc in('PRIMARY_KEY_CONSTRAINT','FOREIGN_KEY_CONSTRAINT')
Order by modify_date Desc

Hope this Helps...

Rasik Banta · Answer 2 · 2013-07-29T03:44:00

I think your question is - how to understand which rows have been updated by your query (and not the number of rows). I haven't come across any such query which would provide the list of rows which have been updated.

Can you try adding a new column to the table which would display the Updated timestamp and make out which rows have been updated by looking at the time. Obviously this will work only in a dev kind of environment but would not in an environment with multiple users. You can still give it a try by adding 2 columns which contain the "LastUpdatedBy" and "LastUpdatedDate" columns.

in which rows updated in sql server 2005

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0