how to track old vaues - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

how to track old vaues

Author

Topic

Ranjit.ileni
Posting Yak Master

183 Posts

Posted - 2011-05-21 : 05:26:48

create table dbo.example
(
num int
,name varchar(10)
,age int
,sal float
)
insert into dbo.example
select 1,'fff',24,10000 union all
select 2,'ggg',25,20000 union all
select 3,'hhh',26,30000 union all
select 4,'jjj',27,40000
go

create view dbo.vw_example with schemabinding
as
select
num
,name
,age
,sal
from dbo.example
go

-- updating one field
update dbo.example
set name='zzz'
where num=1

requirement:

when any field from table gets update
I need to select
with entire row with old value and
with entire row with new value
as below.....
status num name sal
old 1 fff 24 10000
new 1 zzz 24 10000

can i guide me how can i track old value...

actual requirement is one view depends on 10+ tables.
any filed from any table gets update from front end application .

when i run that view I need show with entire row old value and with entire row with new value

can I go for triggers on views.

Please give your suggestions..

--Ranjit

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-05-21 : 05:48:03

The only way to get the old and the new values is at runtime of a trigger.
So in a trigger you can save these values into another table.

No, you're never too old to Yak'n'Roll if you're too young to die.

Ranjit.ileni
Posting Yak Master

183 Posts

Posted - 2011-05-21 : 05:55:05

Thanks for Quick reply webfred,

I am unable create a trigger on a view.
It showing The object 'dbo.vw_example ' does not exist or is invalid for this operation.

could you give example for trigger on a view for my scenario

--Ranjit

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-05-21 : 05:58:49

I thought you would like to create the trigger on the table.
Maybe it is not clear to me what you really need.

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources