simulates three libraries (in the same instance), namely DB1, DB2, DB3The business of the whole transaction is as follows: 1, DB1 write table Data 2, DB2 write table Data 3, DB3 Call stored procedure write table Data 4, each write table data interval simulate throw exception flow, see if data rollback
The code is as follows:BEGIN TRY BEGIN TRAN INSERT into DB1. dbo. Log ( Type, Message, Stack, Created ) VALUES ( 0, --Type-int N ' Message ', --Message-nvarchar (+) N ' Stack ', --Stack-nvarchar (max) ' 2010-03-12 05:43:17 ' --Created-datetime ) -- RAISERROR (' failed after inserting the first library ',--Message text. --16,--Severity. --1--state. -- ); INSERT into DB2. dbo. Agent ( Name , Mobile , Password , LocationID , Address , Status , createtime ) VALUES ( N ' Name ' , --Name-nvarchar () ' Mobile ' , --Mobile-varchar (+) ' Password ' , --Password-varchar (+) 0 , --Locationid-int N ' Address ' , --Address-nvarchar 0 , --Status-int ' 2015-05-22 05:43:48 ' --Createtime-datetime ) --raiserror (' failed after inserting a second library ',--Message text. ----- Severity. --1--state. -- ); EXEC TestDB. dbo. SP_T1--db3 stored Procedures RAISERROR (' failed after inserting stored procedure ', --Message text. --Severity. 1 --state. ); COMMIT TRAN END TRYBEGIN CATCH if @ @TRANCOUNT > 0---------------determine if there is a transaction BEGIN PRINT error_message() ROLLBACK TRAN----------ROLLBACK TRANSACTION END END CATCH
From for notes (Wiz)
SQL Server cross-database transactions
