You have three tables that contain data for vendors, customers, and agents. You create a
view that is used to look up telephone numbers for these companies.
The view has the following definition:
You need to ensure that users can update only the phone numbers by using this view.
What should you do?
A.
Alter the view. Use the EXPAND VIEWS query hint along with each SELECT statement.
B.
Drop the view. Re-create the view by using the SCHEMABINDING clause, and then
create an index on the view.
C.
Create an AFTER UPDATE trigger on the view.
D.
Create an INSTEAD OF UPDATE trigger on the view.
Explanation:
Reference:
http://msdn.microsoft.com/en-us/library/ms187956.aspx
When I create a trigger and try to update phone via view, I get the error:
Msg 4436, Level 16, State 13, Procedure trg_IOU_PhoneList, Line 91
UNION ALL view ‘Ispit.apt.vwCompanyPhoneList’ is not updatable because a partitioning column was not found.
my trigger looks like:
CREATE TRIGGER [apt].[trg_IOU_PhoneList]
ON [apt].[vwCompanyPhoneList]
INSTEAD OF UPDATE AS
BEGIN
UPDATE apt.vwCompanyPhoneList
SET Phone = inserted.Phone
FROM inserted
END;
update apt.vwCompanyPhoneList
set Phone = 67890
where LastName = ‘CFName1’;
What I’m doing wrong?
create trigger apt.tr
on apt.vwCompanyPhoneList
instead of update
as
update c
set Phone = i.Phone
from inserted as i
inner join apt.Customer as c
on i.CompanyID = c.CustomerID
and i.Source = ‘Customer’;
update a
set Phone = i.Phone
from inserted as i
inner join apt.Agent as a
on a.AgentID = i.CompanyID
and i.Source = ‘Agent’;
update v
set Phone = i.Phone
from inserted as i
inner join apt.Vendor as v
on v.VendorID = i.CompanyID
and i.Source = ‘Agent’;
GO