INSTEAD OF Trigger is required when the view have more than one base table

Bandi
Posted by Bandi under Sql Server category on | Points: 40 | Views : 292

CREATE TABLE ViewTableNames
(
PersonalID INT,
FirstName VARCHAR(32),
LastName VARCHAR(32)
)
GO

CREATE TABLE ViewTableAddresses
(
PersonalID INT,
Address VARCHAR(64),
City VARCHAR(32),
State CHAR(2),
Zip CHAR(5)
)
GO

CREATE TABLE ViewTablePhone
(
PersonalID INT,
PhoneType VARCHAR(32),
PhoneNumber VARCHAR(13)
)
GO
INSERT INTO ViewTableNames VALUES (101, 'Misty', 'Pfeifer')
INSERT INTO ViewTableAddresses VALUES (101, '123 4th St.',
'Anycity', 'KS', '66777')
INSERT INTO ViewTablePhone VALUES (101, 'Home', '123-456-7890')
GO

CREATE VIEW NamesView
AS
SELECT VTN.PersonalID, LastName, FirstName, Address, City, State, Zip, PhoneType, PhoneNumber
FROM ViewTableNames VTN INNER JOIN ViewTableAddresses VTA ON (VTN.PersonalID = VTA.PersonalID)
INNER JOIN ViewTablePhone VTP ON (VTN.PersonalID = VTP.PersonalID)
GO

INSERT INTO NamesView (PersonalID, LastName, FirstName)
VALUES (102, 'Jones', 'Bill')
GO
INSERT INTO NamesView (PersonalID, Address, City, State, Zip)
VALUES (102, '234 5th St.', 'Anycity', 'KS', '66777') -- ERROR due to more than one base table
GO
INSERT INTO NamesView (PersonalID, PhoneType, PhoneNumber)
VALUES (102, 'Cellular', '234-567-8901') -- ERROR (View or function 'NamesView' is not updatable because the modification affects multiple base tables.)
GO

The above error can be resloved by using INSTEAD OF TRIGGERs

Comments or Responses

Login to post response