Microsoft MCSA SQL Server 2016 (70-761)むぼーな挑戦11/RAISERRORわからん。

RAISERRORわからんちん。

大切なのは、『RAISERROR』はエラーなので、『CATCH』されます。

そして、『The first paragraph in General Remarks says,”When nesting transactions, this same statement rolls back all inner transactions to the outermost BEGIN TRANSACTION statement.”』ってあるように、「一般的な解説の最初の段落では、トランザクションをネストすると、この同じステートメントはすべての内部トランザクションを最も外側のBEGIN TRANSACTIONステートメントにロールバックします。」ってかいてある。

https://www.briefmenow.org/microsoft/what-is-the-result-of-each-transact-sql-statement/

You run the following Transact-SQL statements:下記の二つを実行してください。

What is the result of each Transact-SQL statement? To answer, select the appropriate options in the answer
area.どんな結果がえられますか？
Hot Area:

＜答え＞両方とも「All transactions are rolled back.」

【検証と結果】Peterさんのから。以下のステートメントを実行してみたら＜答え＞両方とも「All transactions are rolled back.」だったそうです。

IF EXISTS (SELECT TOP(1) 42 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ‘dbo’ and TABLE_NAME = ‘Log1’)
BEGIN
DROP TABLE dbo.Log1;
END;
IF EXISTS (SELECT TOP(1) 42 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ‘dbo’ and TABLE_NAME = ‘Log2’)
BEGIN
DROP TABLE dbo.Log2;
END;
IF EXISTS (SELECT TOP(1) 42 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ‘dbo’ and TABLE_NAME = ‘Log3’)
BEGIN
DROP TABLE dbo.Log3;
END;
IF EXISTS (SELECT TOP(1) 42 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ‘dbo’ and TABLE_NAME = ‘Log4’)
BEGIN
DROP TABLE dbo.Log4;
END;

CREATE TABLE Log1 (Code CHAR(5), ApplcationId INT, Info VARCHAR(1000));
CREATE TABLE Log2 (Code CHAR(5), ApplcationId INT, Info VARCHAR(1000));
CREATE TABLE Log3 (Code CHAR(5), ApplcationId INT, Info VARCHAR(1000));
CREATE TABLE Log4 (Code CHAR(5), ApplcationId INT, Info VARCHAR(1000));
GO

CREATE PROC dbo.UpdateLogs @Code CHAR(5), @ApplicationId INT, @Info VARCHAR(1000)
AS
BEGIN
BEGIN TRY
BEGIN TRAN
INSERT INTO dbo.Log1 VALUES (@Code, @ApplicationId, @Info)

IF (@Code = ‘C2323’ AND @ApplicationId = 1)
RAISERROR(‘C2323 code from HR application!’, 16, 1)
ELSE
INSERT INTO dbo.Log2 VALUES (@Code, @ApplicationId, @Info)
INSERT INTO dbo.Log3 VALUES (@Code, @ApplicationId, @Info)

BEGIN TRAN
IF @Code = ‘C2323’
BEGIN
SELECT ‘Rolling back’;
ROLLBACK TRAN
END
ELSE
INSERT INTO dbo.Log4 VALUES (@Code, @ApplicationId, @Info)

IF @@TRANCOUNT > 0
COMMIT TRAN
END TRY
BEGIN CATCH
IF XACT_STATE() != 0
ROLLBACK TRAN
END CATCH
END
GO

EXEC dbo.UpdateLogs ‘C2323’, 1, ‘Employee records are updated.’

SELECT ‘First Execution’;
SELECT * FROM dbo.Log1;
SELECT * FROM dbo.Log2;
SELECT * FROM dbo.Log3;
SELECT * FROM dbo.Log4;

DELETE FROM dbo.Log1;
DELETE FROM dbo.Log2;
DELETE FROM dbo.Log3;
DELETE FROM dbo.Log4;

EXEC dbo.UpdateLogs ‘C2323’, 10, ‘Sales process started.’

SELECT ‘Second Execution’;
SELECT * FROM dbo.Log1;
SELECT * FROM dbo.Log2;
SELECT * FROM dbo.Log3;
SELECT * FROM dbo.Log4;

DROP PROCEDURE dbo.UpdateLogs;