Re: DESIGN CLARIFICATION--Need Help

"Scott" <sivaraman55_at_gmail.com> wrote in message news:1130866036.463452.288330_at_f14g2000cwb.googlegroups.com...
> Hi All
> I am involved in designing a application that need to move data from
> one of the tables(Say TableA) to Archive table (ArchiveA).
> The process involves deleting the rows based on a condition and
> inserting the same into ArchiveA.
> (let us say..when the status becomes 'Out of Stock' in TableA.)
>
> I can write a simple Insert into statement to insert all the records
> into ArchiveA, that satisfies
> this condition and write a separate delete statement to delete the
> records in TableA.
> But what happens to Concurrency issues? Assuming that when I move the
> data to Archive Tables,the record count may be just 10 and when I
> delete the rows in TableA, the record count may be
> more than 10.
> How to avoid this situation? Can I use locking to prevent this from
> happening?
>
> Thanks in advance for all pointers,
>
> Scott.
>

We do it with a trigger.
On delete insert into 'archive A' Received on Wed Nov 02 2005 - 19:25:03 CST