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