You have tables named Products and OrderDetails. The Products table has a foreign key relationship with the OrderDetails table on the ProductID column. You have the following Transact-SQL batch…

Question

Options

• A--The product will be deleted from the Products table.
• B--The product will be deleted from the Products table.
• C--The product will not be deleted from the Products table.
• D--The product will not be deleted from the Products table.

• A
  15% (4)
• B
  4% (1)
• C
  8% (2)
• D
  73% (19)

Explanation

ROLLBACK { TRAN | TRANSACTION } [ transaction_name | @tran_name_variable | savepoint_name | @savepoint_variable ] transaction_name Is the name assigned to the transaction on BEGIN TRANSACTION. When nesting transactions, transaction_name must be the name from the outermost BEGIN TRANSACTION statement. Is savepoint_name from a SAVE TRANSACTION statement. Use savepoint_name when a conditional rollback should affect only part of the transaction. ROLLBACK TRANSACTION without a savepoint_name or transaction_name rolls back to the beginning of the transaction. When nesting transactions, this same statement rolls back all inner transactions to the outermost BEGIN TRANSACTION statement. In both cases, ROLLBACK TRANSACTION decrements the @@TRANCOUNT system function to 0. ROLLBACK TRANSACTION savepoint_name does not decrement @@TRANCOUNT. A transaction cannot be rolled back after a COMMIT TRANSACTION statement is executed, except when the COMMIT TRANSACTION is associated with a nested transaction that is contained within the transaction being rolled back. In this instance, the nested transaction will also be rolled back, even if you have issued a COMMIT TRANSACTION for it. SQL Server 2008 error handling best practice CREATE PROCEDURE SaveTranExample @InputCandidateID INT -- Detect whether the procedure was called from an active transaction and save that for later use. -- In the procedure, @hasOuterTransaction = 0 means there was no active transaction -- and the procedure started one. -- @hasOuterTransaction > 0 means an active transaction was started before the -- procedure was called. DECLARE @hasOuterTransaction BIT = CASE WHEN @@TRANCOUNT > 0 THEN 1 ELSE 0 -- Save points need unique names if modules can nest otherwise you can rollback -- to the wrong save point. The solution is to use a GUID to name the save points. DECLARE @rollbackPoint nchar(32) = REPLACE(CONVERT(NCHAR(36), NEWID()), N'-', N''); IF @hasOuterTransaction > 0 -- Procedure called when there is an active transaction. -- Create a savepoint to be able to roll back only the work done in the procedure if there is an SAVE TRANSACTION @rollbackPoint; -- Procedure must start its own transaction. BEGIN TRANSACTION @rollbackPoint; -- Modify database. DELETE HumanResources.JobCandidate WHERE JobCandidateID = @InputCandidateID; -- Get here if no errors; must commit -- any transaction started in the -- procedure, but not commit a transaction -- started before the transaction was called. IF @hasOuterTransaction = 0 -- @hasOuterTransaction = 0 means no transaction was started before the procedure was called. -- The procedure must commit the transaction it started. COMMIT TRANSACTION; -- An error occurred; -- If the transaction is still valid IF XACT_STATE() = 1 -- The XACT_STATE function can return the following values: -- 1 An open transaction exists that can be either committed or rolled back. -- 0 There is no open transaction. -- -1 An open transaction exists, but it is in a doomed state. Due to the type of error that was raised, the transaction can only be rolled back. -- Because the syntax for ROLLBACK TRANSACTION is the same for the transaction and for a -- (ROLLBACK TRANSACTION [ transaction_name | @tran_name_variable | savepoint_name | @savepoint_variable ]) -- we can write the following: ROLLBACK TRANSACTION @rollbackPoint; -- In case @rollbackPoint has the name of a transaction, roll back to the beginning of the -- In case @rollbackPoint has the name of a savepoint, roll back to the savepoint. ELSE IF XACT_STATE() = -1 IF @hasOuterTransaction = 0 -- Transaction started in procedure. -- Roll back complete transaction. ROLLBACK TRANSACTION; -- If the transaction is uncommitable, a rollback to the savepoint is not allowed -- because the savepoint rollback writes to the log. Just return to the caller, which -- should roll back the outer transaction. -- Execute Standard module error handler; -- After the appropriate rollback, echo error information to the caller. DECLARE @ErrorMessage NVARCHAR(4000); DECLARE @ErrorSeverity INT; DECLARE @ErrorState INT; SELECT @ErrorMessage = ERROR_MESSAGE(); SELECT @ErrorSeverity = ERROR_SEVERITY(); SELECT @ErrorState = ERROR_STATE(); RAISERROR (@ErrorMessage, -- Message text. @ErrorSeverity, -- Severity. @ErrorState -- State.

No community discussion yet for this question.