You develop a Microsoft SQL Server 2012 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
Explanation:
Verified answer as correct.
why A (instead of) instead of B
0
0
The question is to proceed with the insert of multiple records, but only for which with matching AccountNum.
In B the trigger only repeat the insertion of all matching records: the final result is to
leave the non-matching records in target table and twice the matching one.
Remeber that in after trigger the target table is already popoulated: all you can do si to rollback the entire triggering statement.
0
0
answer is A
0
0
AFTER trigger fires after a DML operation. INSTEAD OF trigger fires INSTEAD OF a DML operation.
0
0
the correct answer is C using the If Exists
0
0
YOU r wrong bro.. correct answer in A
0
0
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
0
0
More, part of that new 200Q 70-461 dumps FYI:
https://drive.google.com/open?id=0B-ob6L_QjGLpfnJldlZxTklTaHM0akpJUzhja2pETHJOS0owMzd4eVk1UTVNQUpvdlVxVWM
Best Regards!
1
0