do a sql query that i can undo

add a command to a sql query so i can undo query
LVL 1
rgb192Asked:
Who is Participating?
 
leakim971Connect With a Mentor PluritechnicianCommented:
You can use :

BEGIN TRAN : http://msdn.microsoft.com/en-us/library/ms188929.aspx
&
ROLLBACK TRAN : http://msdn.microsoft.com/en-us/library/ms181299.aspx

BEGIN TRAN @TransactionName
  INSERT INTO ValueTable VALUES(1)
  INSERT INTO ValueTable VALUES(2)
ROLLBACK TRAN @TransactionName

Open in new window

0
 
rgb192Author Commented:
so
BEGIN TRAN @TransactionName
  INSERT INTO ValueTable VALUES(1)
  INSERT INTO ValueTable VALUES(2)
ROLLBACK TRAN @TransactionName

does nothing



and
BEGIN TRAN @TransactionName
  INSERT INTO ValueTable VALUES(1)
  INSERT INTO ValueTable VALUES(2)
ROLLBACK TRAN @TransactionName
COMMIT TRAN @TransactionName

would execute transaction






do i have to set up a name for TransactionName

0
 
leakim971PluritechnicianCommented:
To execute transaction don't use/remove : ROLLBACK TRAN @TransactionName

@TransactionName is the name of your transaction, you can use another name of course.
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
Kumaraswamy RCommented:
transactions in T-SQL let see the following example:

CREATE PROCEDURE spAddBookmark
 @UserID AS INT,
 @Comment AS VARCHAR (100) = NULL,
 @GroupID AS INT = NULL,
 @ID AS INT OUTPUT
AS

DECLARE @Bookmarks INT
DECLARE @err INT

SET NOCOUNT ON
SET ANSI_WARNINGS OFF

BEGIN TRANSACTION
begin
INSERT INTO Books (UserID, Comment) VALUES (@UserID, @Comment)
-- If any error Roll back

SET @err = @@ERROR
IF @err <> 0
 BEGIN
  ROLLBACK TRANSACTION
  RETURN @err
 END
ELSE
 BEGIN
  SELECT @ID = @@IDENTITY
 END
else
COMMIT TRANSACTION

end

 END
-- If noerror Insert

SET NOCOUNT OFF
SET ANSI_WARNINGS ON
GO
0
 
Kumaraswamy RCommented:
0
 
leakim971PluritechnicianCommented:
Thanks!
0
All Courses

From novice to tech pro — start learning today.