DRAG DROP
Note: This question is part of a series of questions that use the same scenario. For your convenience, the
scenario is repeated in each question. Each question presents a different goal and answer choices, but the text
of the scenario is exactly the same in each question in this series.
You have a database named DB1 that contains the following tables: Customer, CustomerToAccountBridge,
and CustomerDetails. The three tables are part of the Sales schema. The database also contains a schema
named Website. You create the Customer table by running the following Transact-SQL statement:
The value of the CustomerStatus column is equal to one for active customers. The value of the Account1Status
and Account2Status columns are equal to one for active accounts. The following table displays selected
columns and rows from the Customer table.
You plan to create a view named Website.Customer and a view named Sales.FemaleCustomers.
Website.Customer must meet the following requirements:
1. Allow users access to the CustomerName and CustomerNumber columns for active customers.
2. Allow changes to the columns that the view references. Modified data must be visible through the view.
3. Prevent the view from being published as part of Microsoft SQL Server replication.
Sales.Female.Customers must meet the following requirements:
1. Allow users access to the CustomerName, Address, City, State and PostalCode columns.
2. Prevent changes to the columns that the view references.
3. Only allow updates through the views that adhere to the view filter.
You have the following stored procedures: spDeleteCustAcctRelationship and spUpdateCustomerSummary.
The spUpdateCustomerSummary stored procedure was created by running the following Transacr-SQL
statement:
You run the spUpdateCustomerSummary stored procedure to make changes to customer account summaries.
Other stored procedures call the spDeleteCustAcctRelationship to delete records from the
CustomerToAccountBridge table.
When a procedure calls spDeleteCustAcctRelationship, if the calling stored procedures has already started an
active transaction, all the detections made by the spDeleteCustAccRelationship stored procedure must be
committed by the caller; otherwise changes must be committed within the spDeleteCustAcctRelationship stored
procedure.
If any error occurs during the delete operation, only the deletes made by the soDeleteCustACCTRelationships
stored procedure must be rolled back and the status must be updated.
You need to complete the stored procedure to ensure all the requirements are met.
How should you complete the procedure? To answer, drag the Transact-SQL segments to the correct location.
Each transact-SQL segment may be used once, more than once or not at all. You may need to drag the split
bar between panes or scroll to view content.
NOTE: Each correct selection is worth one point.
Select and Place:
Answer: 
Explanation:
Savepoints offer a mechanism to roll back portions of transactions. You create a savepoint using the SAVE
TRANSACTION savepoint_name statement. Later, you execute a ROLLBACK TRANSACTION
savepoint_name statement to roll back to the savepoint instead of rolling back to the start of the transaction.
https://technet.microsoft.com/en-us/library/ms178157(v=sql.105).aspx