You develop a Microsoft SQL Server 2012 database that contains tables named Employee and Person.

The tables have the following definitions:

Users are able to use single INSERT statements or INSERT…SELECT statements into this view.

You need to ensure that users are able to use a single statement to insert records into both Employee and Person tables by using the VwEmployee view.

Which Transact-SQL statement should you use?

A. CREATE TRIGGER TrgVwEmployee
ON VwEmployee
FOR INSERT
AS
BEGIN
INSERT INTO Person(Id, FirstName, LastName)
SELECT Id, FirstName, LastName, FROM inserted
INSERT INTO Employee(PersonId, EmployeeNumber)
SELECT Id, EmployeeNumber FROM inserted
END

B. CREATE TRIGGER TrgVwEmployee
ON VwEmployee
INSTEAD OF INSERT
AS
BEGIN
INSERT INTO Person(Id, FirstName, LastName)
SELECT Id, FirstName, LastName, FROM inserted
INSERT INTO Employee(PersonId, EmployeeNumber)
SELECT Id, EmployeeNumber FROM inserted
END

C. CREATE TRIGGER TrgVwEmployee
ON VwEmployee
INSTEAD OF INSERT

AS

BEGIN

DECLARE @ID INT, @FirstName NVARCHAR(25), @LastName NVARCHAR(25), @PersonID
INT, @EmployeeNumber NVARCHAR(15)

SELECT @ID = ID, @FirstName = FirstName, @LastName = LastName,

@EmployeeNumber = EmployeeNumber

FROM inserted

INSERT INTO Person(Id, FirstName, LastName)

VALUES(@ID, @FirstName, @LastName)

INSERT INTO Employee(PersonID, EmployeeNumber)

VALUES(@PersonID, @EmployeeNumber

END

D.

CREATE TRIGGER TrgVwEmployee ON VwEmployee

INSTEAD OF INSERT

AS

BEGIN

INSERT INTO

Person(Id, FirstName, LastName)

SELECT Id, FirstName, LastName FROM VwEmployee

INSERT INTO Employee(PersonID, EmployeeNumber)

SELECT Id, EmployeeNumber FROM VwEmployee

END

Explanation: