SQL Server View – Create a View to Edit ID Mapping Table Without Seeing IDs

sql serverssmst-sqlview

I have 2 tables, A and B with an int primary key and a string name.

I have a 3rd table, C, with 2 columns that map the IDs of A and B together. I want to create a view to edit the mappings in table C, but I want the view to show the Name value rather than the IDs. The view would be edited via SQL Server Management Studio.

For example, the view should allow the user to type in "Item4" in the item column, and "Accessory1" in the accessory column, and table C gets updated with a new row mapping "4" to "1".

enter image description here

I am wondering if I can make this an editable view so I don't have to go to the trouble of writing a whole application to manage this.

If I go to the view in Microsoft SQL Server Management Studio, and right click, I can edit the top 200 rows. I know the constraint on this feature is that I can only edit if the change is to a single table. I would only be editing a single table, so there might be a way to get this to work. Is there a way? (My users are people (traders) who are familiar with SQL Server and prefer to have direct access to their data).

Best Answer

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;
Related Question