How to know if row was updated or deleted?

Question

Post reply

How to know if row was updated or deleted?

Shurgenz

SSC Eights!

Points: 802
More actions
April 13, 2005 at 7:45 am

#109468

I wish to use timestamp field for this task

create table table1 (id int identity(1,1), skey varchar(30) not null primary key clustered, rowid rowversion)

insert table1 (skey)

select 'aaa'

insert table1 (skey)

select 'aa'

insert table1 (skey)

select 'ccc'

insert table1 (skey)

select 'ddd'

insert table1 (skey)

select 'eee'

insert table1 (skey)

select 'fff'

In case when I have only one rowversion field within database, Have I sure, the rowversion field grows ascedingly? by +1 step?

the next query will return 1 if there was no updates

select count(*)-sum(case when b.id is not null then 1 else 0 end) from table1 a left join table1 b on a.rowid=b.rowid+1 and a.id=b.id+1

and >1 if there was... but I supposed, that rowversion growth was +1 Is it true?

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Johan Bijnens SSC Guru Points: 134873 More actions · Answer 1

Rowversion is a sysnonym for timestamp datatype.

This timestamp-column is (automaticaly) altered every time the row is updated.

What can it do ? Just indicate if the row has been changed since your last query when you perform the next query using that timestamp-column as a predicate !

We use triggers to keep track of rowactions.

create trigger mytable_tri

for insert

as

insert into logging_mytable

select 'I', --keyvalues-- ,whodoneitinfo from inserted

create trigger mytable_tru

for update

as

insert into logging_mytable

select 'U', --keyvalues-- ,whodoneitinfo from inserted --Keyvalues are not to be changed !!

create trigger mytable_tru

for delete

as

insert into logging_mytable

select 'D', --keyvalues-- ,whodoneitinfo from deleted

Check out CREATE TRIGGER in books online

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Shurgenz SSC Eights! Points: 802 More actions · Answer 2

Using trigger is very simple decision.

I want to use somewhat hard to understand by hackers.

tracking timestamp field I think is non standard step.

by other hand - it is very simple to code.

timestamp tracking is one of many other bricks in my project

I developed an ADE + MSDE2000a. And want to protect it from illegal use