You are the database developer for an order-processing application. The database has the following tables:
CREATE TABLE dbo.Product (ProdID INT NOT NULL PRIMARY KEY,
ProdName VARCHAR(100) NOT NULL,
SalePrice MONEY NOT NULL,
ManufacturerName VARCHAR(100) NOT NULL);
CREATE TABLE dbo.Customer (CustID INT NOT NULL PRIMARY KEY,
CustName VARCHAR(100) NOT NULL,
CustAddress VARCHAR(200) NOT NULL,
CustCity VARCHAR(100) NOT NULL,
CustState VARCHAR(50) NOT NULL,
CustPostalCode VARCHAR(5) NOT NULL);
CREATE TABLE dbo.[Order](OrderID INT NOT NULL PRIMARY KEY,
ProdID INT NOT NULL REFERENCES dbo.Product(ProdId),
CustID INT NOT NULL REFERENCES dbo.Customer(CustId),
OrderDate DATETIME NOT NULL);
You need to ensure that the following requirements are met:
•Data is loaded into the tables.
•Data that has been inserted will be removed if any statement fails.
•No open transactions are performed after the batch has executed.
Which Transact-SQL statements should you use?
A. BEGIN TRY
SAVE TRANSACTION DataLoad
INSERT INTO dbo.Product
VALUES (1, ‘Chair’, 146.58,’Contoso’),
(2, ‘Table’, 458.36, ‘Contoso’),
(3, ‘Cabinet’, 398.17, ‘Northwind Traders’),
(4, ‘Desk’,1483.25, ‘Northwind Traders’);
INSERT INTO dbo.Customer
VALUES(1, ‘John Smith’, ‘200 West 2nd St’, ‘Seattle’, ‘WA’, ‘98060’),
(2, ‘Bob Jones’, ‘300 Main St’, ‘Portland’, ‘OR’, ‘97211’),
(3, ‘Fred Thomson’, ‘100 Park Ave’, ‘San Francisco’, ‘CA’, ‘94172’);
INSERT INTO dbo.[Order]
VALUES (1, 1, 2,’09/15/2011′),
(2, 4, 2, ’09/15/2011′),
(3, 2, 1, ’08/17/2011′),
(4, 2, 3, ’07/01/2011′),
(5, 3, 3,’10/02/2011′);
COMMIT TRANSACTION DataLoad;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
BEGIN
ROLLBACK TRANSACTION DataLoad;
END;
END CATCH;
B. BEGIN TRY
BEGIN TRANSACTION
INSERT INTO dbo.Product
VALUES (1, ‘Chair’, 146.58,’Contoso’),
(2, ‘Table’, 458.36, ‘Contoso’),
(3, ‘Cabinet’, 398.17, ‘Northwind Traders’),
(4, ‘Desk’,1483.25, ‘Northwind Traders’);
INSERT INTO dbo.Customer
VALUES (1, ‘John Smith’, ‘200 West 2nd St’, ‘Seattle’, ‘WA’, ‘98060’),
(2, ‘Bob Jones’, ‘300 Main St’, ‘Portland’, ‘OR’, ‘97211’),
(3, ‘Fred Thomson’, ‘100 Park Ave’, ‘San Francisco’, ‘CA’, ‘94172’);
INSERT INTO dbo.[Order]
VALUES (1, 1, 2,’09/15/2011′),
(2, 4, 2, ’09/15/2011′),
(3, 2, 1, ’08/17/2011′),
(4, 2, 3, ’07/01/2011′),
(5, 3, 3,’10/02/2011′);
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
BEGIN
ROLLBACK TRANSACTION;
END;
END CATCH;
C. BEGIN TRY
BEGIN TRANSACTION customers;
INSERT INTO dbo.Product
VALUES(1, ‘Chair’,46.58, ‘Contoso’),
(2, ‘Table’, 458.36, ‘Contoso’),
(3, ‘Cabinet’, 398.17, ‘Northwind Traders’),
(4,’Desk’, 1483.25, ‘Northwind Traders’);
INSERT INTO dbo.Customer
VALUES(1, ‘John Smith’, ‘200 West 2nd St’, ‘Seattle’, ‘WA’, ‘98060’),
(2, ‘Bob Jones’, ‘300 Main St’, ‘Portland’, ‘OR’, ‘97211’),
(3,’Fred Thomson’, ‘100 Park Ave’, ‘San Francisco’, ‘CA’, ‘94172’);
INSERT INTO dbo.[Order]
VALUES(1,1, 2, ’09/15/2011′),
(2, 4, 2, ’09/15/2011′),
(3, 2, 1, ’08/17/2011′),
(4, 2, 3, ’07/01/2011′),
(5, 3, 3,’10/02/2011′);
SAVE TRANSACTION customers;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
BEGIN
ROLLBACK TRANSACTION customers;
END;
END CATCH;
D. BEGIN TRY
BEGIN TRANSACTION
INSERT INTO dbo.Product
VALUES(1, ‘Chair’, 146.58,’Contoso’),
(2, ‘Table’, 458.36, ‘Contoso’),
(3, ‘Cabinet’, 398.17, ‘Northwind Traders’),
(4, ‘Desk’,1483.25, ‘Northwind Traders’);
INSERT INTO dbo.Customer
VALUES (1, ‘John Smith’, ‘200 West 2nd St’, ‘Seattle’, ‘WA’, ‘98060’),
(2, ‘Bob Jones’, ‘300 Main St’, ‘Portland’, ‘OR’, ‘97211’),
(3, ‘Fred Thomson’, ‘100 Park Ave’, ‘San Francisco’, ‘CA’, ‘94172’);
INSERT INTO dbo.[Order]
VALUES (1, 1, 2,’09/15/2011′),
(2, 4, 2, ’09/15/2011′),
(3, 2, 1, ’08/17/2011′),
(4, 2, 3, ’07/01/2011′),
(5, 3, 3,’10/02/2011′);
SAVE TRANSACTION;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
BEGIN
ROLLBACK TRANSACTION;
END;
END CATCH;
E. BEGIN TRY
BEGIN TRANSACTION
INSERT INTO dbo.Product
VALUES(1, ‘Chair’, 146.58,’Contoso’),
(2, ‘Table’, 458.36, ‘Contoso’),
(3, ‘Cabinet’, 398.17, ‘Northwind Traders’),
(4, ‘Desk’,1483.25, ‘Northwind Traders’);
INSERT INTO dbo.Customer
VALUES (1, ‘John Smith’, ‘200 West 2nd St’, ‘Seattle’, ‘WA’, ‘98060’),
(2, ‘Bob Jones’, ‘300 Main St’, ‘Portland’, ‘OR’, ‘97211’),
(3, ‘Fred Thomson’, ‘100 Park Ave’, ‘San Francisco’, ‘CA’, ‘94172’);
INSERT INTO dbo.[Order]
VALUES (1, 1, 2, ’09/15/2011′),
(2, 4, 2, ’09/15/2011′),
(3, 2, 1, ’08/17/2011′),
(4, 2, 3, ’07/01/2011′),
(5, 3, 3,’10/02/2011′);
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
BEGIN
ROLLBACK TRANSACTION;
END;
END CATCH;
F. BEGIN TRANSACTION
INSERT INTO dbo.Product
VALUES (1, ‘Chair’, 146.58, ‘Contoso’),
(2, ‘Table’,458.36, ‘Contoso’),
(3, ‘Cabinet’, 398.17, ‘Northwind Traders’),
(4, ‘Desk’, 1483.25, ‘Northwind Traders’);
IF @@ERROR > 0
ROLLBACK TRANSACTION;
INSERT INTO dbo.Customer
VALUES(1,’John Smith’, ‘200 West 2nd St’, ‘Seattle’, ‘WA’, ‘98060’),
(2, ‘Bob Jones’, ‘300 Main St’, ‘Portland’,’OR’, ‘97211’),
(3, ‘Fred Thomson’, ‘100 Park Ave’, ‘San Francisco’, ‘CA’, ‘94172’);
IF @@ERROR > 0
ROLLBACK TRANSACTION;
INSERT INTO dbo.[Order]
VALUES (1, 1, 2, ’09/15/2011′),
(2, 4, 2,’09/15/2011′),
(3, 2, 1, ’08/17/2011′),
(4, 2, 3, ’07/01/2011′),
(5, 3, 3, ’10/02/2011′);
IF @@ERROR > 0
ROLLBACK TRANSACTION;
COMMIT TRANSACTION;
G. SET XACT_ABORT ON
BEGIN TRANSACTION
INSERT INTO dbo.Product
VALUES (1, ‘Chair’, 146.58,’Contoso’),
(2, ‘Table’, 458.36, ‘Contoso’),
(3, ‘Cabinet’, 398.17, ‘Northwind Traders’),
(4, ‘Desk’,1483.25, ‘Northwind Traders’);
INSERT INTO dbo.Customer
VALUES (1, ‘John Smith’, ‘200 West 2nd St’, ‘Seattle’, ‘WA’, ‘98060’),
(2, ‘Bob Jones’, ‘300 Main St’, ‘Portland’, ‘OR’, ‘97211’),
(3, ‘Fred Thomson’, ‘100 Park Ave’, ‘San Francisco’, ‘CA’, ‘94172’);
INSERT INTO dbo.[Order]
VALUES (1, 1, 2,’09/15/2011′),
(2, 4, 2, ’09/15/2011′),
(3, 2, 1, ’08/17/2011′),
(4, 2, 3, ’07/01/2011′),
(5, 3, 3,’10/02/2011′);
COMMIT TRANSACTION;
H. BEGIN TRANSACTION
INSERT INTO dbo.Product
VALUES (1, ‘Chair’, 146.58, ‘Contoso’),
(2, ‘Table’,458.36, ‘Contoso’),
(3, ‘Cabinet’, 398.17, ‘Northwind Traders’),
(4, ‘Desk’, 1483.25, ‘Northwind Traders’);
INSERT INTO dbo.Customer
VALUES (1, ‘John Smith’, ‘200 West 2nd St’, ‘Seattle’, ‘WA’, ‘98060’),
(2, ‘Bob Jones’, ‘300 Main St’, ‘Portland’, ‘OR’, ‘97211’),
(3, ‘Fred Thomson’, ‘100 Park Ave’,’San Francisco’, ‘CA’, ‘94172’);
INSERT INTO dbo.[Order]
VALUES (1, 1, 2, ’09/15/2011′),
(2, 4, 2,’09/15/2011′),
(3, 2, 1, ’08/17/2011′),
(4, 2, 3, ’07/01/2011′),
(5, 3, 3, ’10/02/2011′);
IF @@ERROR >0
ROLLBACK TRANSACTION
ELSE
COMMIT TRANSACTION;
