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
?
0
0
C
0
0
why C and not B
0
1
B is correct.
C is similar to B and will work although @ID and @PersonID are declare using the wrong data type.
The above exhibit shows both ID columns set to bigint whilst the variables are set to int.
0
1
B
1
1
shouldn’t it be D?
mainly because ‘FROM VwEmployee’ and not ‘FROM inserted’. Where does the ‘inserted’ come from?
0
1
“inserted” is a T-SQL table created whenever an INSERT or an UPDATE occur.
an INSERT creates a record into the “inserted” table.
a DELETE creates a record into the “deleted” table.
an UPDATE creates a record into the “deleted” table and also the “inserted” table.
0
1
“inserted” is a T-SQL table created whenever an INSERT or an UPDATE occur.
an INSERT creates a record into the “inserted” table.
a DELETE creates a record into the “deleted” table.
an UPDATE creates a record into the “deleted” table and also the “inserted” table.
1
0