Exam 70-461 topic 1 question 132 discussion

DRAG DROP -
You administer a Microsoft SQL Server database that contains a table named Customer defined by the following Transact-SQL statement:

The SalesRep column contains the SQL Login name of the user designated as the customer's sales rep.
You need to create at trigger that meets the following requirements:
✑ A customer's CreditLimit can only be changed by the customer's SalesRep.
✑ CreditLimit cannot be increased by more than 50 percent in any single update.
If an UPDATE statement causes either of these business rules to be violated, the entire UPDATE statement should be rolled back.
In addition, the trigger must handle single-row and multi-row update statements and should execute in the most efficient manner possible.
How should you complete the trigger? To answer, drag the appropriate Transact-SQL segments to the correct locations. 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.
Select and Place:

Suggested Answer:
Box 1:
The COLUMNS_UPDATED function returns a varbinary bit pattern indicating the inserted or updated columns of a table or view. Use COLUMNS_UPDATED anywhere inside the body of a Transact-SQL INSERT or UPDATE trigger to test whether the trigger should execute certain actions.
Box 2, Box 3:
An Example of Using the inserted Table in a Trigger to Enforce Business Rules
The following example creates a DML trigger. This trigger checks to make sure the credit rating for the vendor is good when an attempt is made to insert a new purchase order into the PurchaseOrderHeader table. To obtain the credit rating of the vendor corresponding to the purchase order that was just inserted, the
Vendor table must be referenced and joined with the inserted table. If the credit rating is too low, a message is displayed and the insertion does not execute.
CREATE TRIGGER Purchasing.LowCredit ON Purchasing.PurchaseOrderHeader

AFTER INSERT -

AS -
IF EXISTS (SELECT *
FROM Purchasing.PurchaseOrderHeader p

JOIN inserted AS i -
ON p.PurchaseOrderID = i.PurchaseOrderID

JOIN Purchasing.Vendor AS v -
ON v.BusinessEntityID = p.VendorID

WHERE v.CreditRating = 5 -
)

BEGIN -
RAISERROR ('A vendor''s credit rating is too low to accept new purchase orders.', 16, 1);
ROLLBACK TRANSACTION;

RETURN -
END;

GO -
References:
https://docs.microsoft.com/en-us/sql/t-sql/functions/columns-updated-transact-sql?view=sql-server-2017 https://docs.microsoft.com/en-us/sql/relational-databases/triggers/use-the-inserted-and-deleted-tables?view=sql-server-2017