2

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).

4
  • @Franchesca Regarding "(My users are people who are familiar with MS SQL and prefer to have direct access to their data)": are your users going to be editing this data the same way you are, editing rows directly in SSMS (something that is rarely ever a good option)? Or will they be wanting to issue an UPDATE statement? If they want / have direct access, why not just wrap the UPDATE in a Stored Procedure that accepts both strings values and does the update appropriately? Oct 11, 2016 at 14:05
  • @srutzky because then you can't copy paste your rows from excel. :D
    – Franchesca
    Oct 11, 2016 at 14:10
  • 1
    @Franchesca How is Excel getting the values for those rows in the first place? Are people hand-entering them into Excel, or is it pulling directly from SQL Server? If hand-entered, what about typos? Oct 11, 2016 at 14:21
  • @srutzky With great power comes great responsibility. As for where the data comes from, it depends on the user and what they are doing, and what they are doing can change at short notice.
    – Franchesca
    Oct 12, 2016 at 11:14

1 Answer 1

6

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.