February 9, 2012 at 2:43 am
Hi Experts,
How can i write a simple transaction and then roll back the transaction.
For example:i create a table and then insert few records.
CREATE TABLE dbo.t3(a int) ; and then inserting rows into table.
INSERT INTO dbo.t3 VALUES (1) ;
INSERT INTO dbo.t3 VALUES (2,3) ;<-
INSERT INTO dbo.t3 VALUES (3) ;
now purposely iam inserting 2,3 values so tht it fails.Now i want all statements must roll back.Like the table should also not be created if insert fails.If insert is successful then only table must be created.
How can i write this in a begin transaction and Roll back transaction.
Kindly Help.
February 9, 2012 at 2:49 am
Use try - catch block for error handling.
Syntax:
BEGIN TRY
{ sql_statement |
statement_block }
END TRY
BEGIN CATCH
{ sql_statement |
statement_block }
END CATCH
February 9, 2012 at 2:58 am
sandy_1006 (2/9/2012)
Hi Experts,How can i write a simple transaction and then roll back the transaction.
For example:i create a table and then insert few records.
CREATE TABLE dbo.t3(a int) ; and then inserting rows into table.
INSERT INTO dbo.t3 VALUES (1) ;
INSERT INTO dbo.t3 VALUES (2,3) ;<-
INSERT INTO dbo.t3 VALUES (3) ;
now purposely iam inserting 2,3 values so tht it fails.Now i want all statements must roll back.Like the table should also not be created if insert fails.If insert is successful then only table must be created.
How can i write this in a begin transaction and Roll back transaction.
Kindly Help.
Well in you example, that's a parse-time error so none of that would ever be actually executed regardless.
If you had a run-time error however (INSERT INTO dbo.t3 VALUES ('a')) then you would need a try-catch block and a transaction
BEGIN TRANSACTION
BEGIN TRY
CREATE TABLE dbo.t3(a int) ;
INSERT INTO dbo.t3 VALUES (1) ;
INSERT INTO dbo.t3 VALUES ('a') ;
INSERT INTO dbo.t3 VALUES (3) ;
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
-- whatever reporting/auditing you want
END CATCH
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 9, 2012 at 4:52 am
Thanks a lot .I have understood between parse and run time error.Probably this did not clicked my mind.Once again thanks.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy