ss2k ...

i need to implement a "1-stage undo" for various stored-procedures (sps that
typically manipulate multiple relational tables) ... so, basically, revert
the various records associated with a given unique-key of the
ultimate-parent-table to the state they were in immediately prior to the
last execution of a given sp ...

does anyone have any ideas?

The stored procedure can be called inside a transaction, that can be rolled
back on meeting certain conditions. For example:

BEGIN TRAN
EXEC YourSP
IF (Check some condition here)
BEGIN
COMMIT
ELSE
ROLLBACK
END

If you are talking about rolling back this stored procedure's action, at a
later date, then you will have to implement some kind of auditing mechanism,
where in, you store the previous image of the data.

--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
What hardware is your SQL Server running on?
http://vyaskn.tripod.com/poll.htm




[quoted text, click to view]
ss2k ...

i need to implement a "1-stage undo" for various stored-procedures (sps that
typically manipulate multiple relational tables) ... so, basically, revert
the various records associated with a given unique-key of the
ultimate-parent-table to the state they were in immediately prior to the
last execution of a given sp ...

does anyone have any ideas?

Here is a repost of a message that might make some sense for what you are
doing:

Use save transaction (savepoints.) They give you full control over what
is going on in the procedure. The first example does not isolate the
subordinate procedure's activities. For example, say you have the following
pseudoprocedures:
--------------------------------------------------------------------
create proc childProc
as
begin transaction
save transaction childProc

do something
if errors
begin
rollback transaction childProc
commit transaction
raiserror to warn the client (not the calling procedure, we do
that later)
return -100 --use negative numbers for errors, less than -99
other negs are system reserved
end

commit transaction
go
---------------------------------------------------------------------
create proc parentProc
as
begin transaction
save transaction parentProc

do something else
if errors
begin
rollback transaction childProc
commit transaction
raiserror to warn the client (not the calling procedure, we do
that later)
return -100 --use negative numbers for errors, less than -99
other negs are system reserved
end

execute @retval = childProc
if @retval not in (0,-100)
begin
rollback transaction childProc
commit transaction
raiserror to warn the client (not the calling procedure, we do
that later)
return -100 --use negative numbers for errors, less than -99
other negs are system reserved
end

commit transaction
go

---------------------------------------------------------------------

Now, we have given the parentProc the right to say that it is happy with
return values of 0, or -100, but not any other value. In more real terms,
the return value or reason for the rollback of the savepoint may be
anything. This method gives you relatively complete control (until we can
trap errors, especially in triggers, you do not get complete control) over
what happens.


--
----------------------------------------------------------------------------
-----------
Louis Davidson (drsql@hotmail.com)
Compass Technology Management

Pro SQL Server 2000 Database Design
http://www.apress.com/book/bookDisplay.html?bID=266

Note: Please reply to the newsgroups only unless you are
interested in consulting services. All other replies will be ignored :)

[quoted text, click to view]