How to know if row was updated or deleted?
-
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?
-
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
-
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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply