Exam 70-461 topic 1 question 60 discussion

Actual exam question from Microsoft's 70-461

Question #: 60
Topic #: 1

You develop a Microsoft SQL Server database that has two tables named SavingAccounts and LoanAccounts. Both tables have a column named AccountNumber of the nvarchar data type.
You use a third table named Transactions that has columns named TransactionId AccountNumber, Amount, and TransactionDate.
You need to ensure that when multiple records are inserted in the Transactions table, only the records that have a valid AccountNumber in the SavingAccounts or
LoanAccounts are inserted.
Which Transact-SQL statement should you use?

A. CREATE TRIGGER TrgValidateAccountNumber ON Transactions INSTEAD OF INSERT AS BEGIN INSERT INTO Transactions SELECT TransactionID,AccountNumber,Amount,TransactionDate FROM inserted WHERE AccountNumber IN (SELECT AccountNumber FROM LoanAccounts UNION SELECT AccountNumber FROM SavingAccounts) END
B. CREATE TRIGGER TrgValidateAccountNumber ON Transactions FOR INSERT AS BEGIN INSERT INTO Transactions SELECT TransactionID,AccountNumber,Amount,TransactionDate FROM inserted WHERE AccountNumber IN (SELECT AccountNumber FROM LoanAccounts UNION SELECT AccountNumber FROM SavingAccounts) END
C. CREATE TRIGGER TrgValidateAccountNumber ON Transactions INSTEAD OF INSERT AS BEGIN IF EXISTS ( SELECT AccountNumber FROM inserted EXCEPT (SELECT AccountNumber FROM LoanAccounts UNION SELECT AccountNumber FROM SavingAccounts)) BEGIN ROLLBACK TRAN END END
D. CREATE TRIGGER TrgValidateAccountNumber ON Transactions FOR INSERT AS BEGIN IF EXISTS ( SELECT AccountNumber FROM inserted EXCEPT (SELECT AccountNumber FROM LoanAccounts UNION SELECT AccountNumber FROM SavingAccounts)) BEGIN ROLLBACK TRAN END END

Show Suggested Answer

Suggested Answer: A 🗳️

by Fadletime at Dec. 10, 2020, 10:16 p.m.

Comments

Submit Cancel

SugarVixen

4 years, 4 months ago

When only the 'FOR' keyword is used, the trigger type defaults to 'AFTER'. For this question, the insert should only occur IF the accounts exist in either of the two tables. If the 'FOR'/'AFTER' trigger type was used, the insert would occur and THEN the trigger would be called. The account verification has to happen first, so the 'INSTEAD OF' trigger type is used, preventing the insert of the new records if the account numbers do not exist in the other tables. i.e. 'FOR' is the same as 'AFTER'. clearest source: https://www.dotnettricks.com/learn/sqlserver/different-types-of-sql-server-triggers

upvoted 1 times

...

Fadletime

4 years, 5 months ago

Can anyone explain with some clarity why this is A the INSTEAD OF trigger, as opposed to B the FOR INSERT trigger?

upvoted 1 times

...