Base tables
CREATE TABLE dbo.Item
(
ItemID integer PRIMARY KEY,
ItemName varchar(50) NOT NULL UNIQUE
);
CREATE TABLE dbo.Accessory
(
AccessoryID integer PRIMARY KEY,
AccessoryName varchar(50) NOT NULL UNIQUE
);
CREATE TABLE dbo.ItemAccessory
(
ItemID integer REFERENCES dbo.Item (ItemID),
AccessoryID integer REFERENCES dbo.Accessory (AccessoryID),
PRIMARY KEY (ItemID, AccessoryID)
);
Sample data
INSERT dbo.Item
(ItemID, ItemName)
VALUES
(1, 'Item 1'),
(2, 'Item 2'),
(3, 'Item 3'),
(4, 'Item 4');
INSERT dbo.Accessory
(AccessoryID, AccessoryName)
VALUES
(1, 'Accessory 1'),
(2, 'Accessory 2'),
(3, 'Accessory 3'),
(4, 'Accessory 4');
INSERT dbo.ItemAccessory
(ItemID, AccessoryID)
VALUES
(1, 1),
(1, 2),
(2, 3),
(3, 4);
View
CREATE VIEW dbo.ItemAccessoryName
WITH SCHEMABINDING
AS
SELECT
I.ItemName,
A.AccessoryName
FROM dbo.ItemAccessory AS IA
JOIN dbo.Accessory AS A
ON A.AccessoryID = IA.AccessoryID
JOIN dbo.Item AS I
ON I.ItemID = IA.ItemID;
Problem
The view is not directly updatable:
INSERT dbo.ItemAccessoryName
(ItemName, AccessoryName)
VALUES
('Item 4', 'Accessory 4');
Msg 4405, Level 16, State 1, Line 61
View or function 'dbo.ItemAccessoryName' is not updatable because the modification affects multiple base tables.
Solution
We can make any view updatable using an instead of trigger:
CREATE TRIGGER dbo_ItemAccessoryName_IOI
ON dbo.ItemAccessoryName
INSTEAD OF INSERT
AS
SET ROWCOUNT 0;
SET NOCOUNT ON;
INSERT dbo.ItemAccessory
(
ItemID,
AccessoryID
)
SELECT
I.ItemID,
A.AccessoryID
FROM Inserted AS INS
JOIN dbo.Item AS I
ON I.ItemName = INS.ItemName
JOIN dbo.Accessory AS A
ON A.AccessoryName = INS.AccessoryName;
The trigger essentially converts the supplied names to IDs before the insert is attempted. As long as you have good referential integrity, the need for additional checks in the trigger code should be minimal. The details depend on exactly what you want to happen if someone pastes multiple entries, only some of which are valid, for example.
Inserts, updates, and deletes
You would need additional trigger logic if you want to allow (general) updates and deletes through the view. For example:
DROP TRIGGER dbo.dbo_ItemAccessoryName_IOI;
GO
CREATE TRIGGER dbo_ItemAccessoryName_IOIUD
ON dbo.ItemAccessoryName
INSTEAD OF INSERT, UPDATE, DELETE
AS
SET ROWCOUNT 0;
SET NOCOUNT ON;
-- Handle deletions (including the delete part of an update)
DELETE IA
FROM dbo.ItemAccessory AS IA
JOIN dbo.Item AS I
ON I.ItemID = IA.ItemID
JOIN dbo.Accessory AS A
ON A.AccessoryID = IA.AccessoryID
JOIN Deleted AS DEL
ON DEL.ItemName = I.ItemName
AND DEL.AccessoryName = A.AccessoryName;
-- Handle inserts (including the insert part of an update)
INSERT dbo.ItemAccessory
(
ItemID,
AccessoryID
)
SELECT
I.ItemID,
A.AccessoryID
FROM Inserted AS INS
JOIN dbo.Item AS I
ON I.ItemName = INS.ItemName
JOIN dbo.Accessory AS A
ON A.AccessoryName = INS.AccessoryName;
UPDATE
statement? If they want / have direct access, why not just wrap theUPDATE
in a Stored Procedure that accepts both strings values and does the update appropriately?