能否在SSMS中配置特定场景的用户输入视图？

Absolutely! You can set up this view to support user input (inserting/modifying configuration records) in SSMS. Here's a step-by-step breakdown tailored to your table setup:

1. Create the Base View #

First, let's build the view that displays the friendly names (DomainName, EntityName) alongside the underlying IDs (we'll need these for later edits):

CREATE VIEW ConfigurationView
AS
SELECT 
    dt.DomainName,
    et.EntityName,
    ct.DomainId,
    ct.EntityId
FROM ConfigurationTable ct
INNER JOIN DomainTable dt ON ct.DomainId = dt.DomainId
INNER JOIN EntityTable et ON ct.EntityId = et.EntityId

This view will show exactly what you need: the paired domain and entity names from your configuration table.

2. Make the View Editable with Triggers #

By default, you can't directly edit DomainName or EntityName in this view (they come from separate base tables). To let users input names to modify or add configurations, we'll use INSTEAD OF triggers to map those names back to their corresponding IDs in ConfigurationTable.

Trigger for Inserting New Configurations #

This trigger takes the DomainName and EntityName a user enters, finds their matching IDs, and inserts the record into ConfigurationTable:

CREATE TRIGGER trg_ConfigurationView_Insert
ON ConfigurationView
INSTEAD OF INSERT
AS
BEGIN
    SET NOCOUNT ON;

    -- Insert into the configuration table using the IDs matched from the input names
    INSERT INTO ConfigurationTable (DomainId, EntityId)
    SELECT 
        dt.DomainId,
        et.EntityId
    FROM inserted i
    INNER JOIN DomainTable dt ON i.DomainName = dt.DomainName
    INNER JOIN EntityTable et ON i.EntityName = et.EntityName
END

Trigger for Updating Existing Configurations #

This trigger updates the existing configuration record when a user changes the domain or entity name in the view:

CREATE TRIGGER trg_ConfigurationView_Update
ON ConfigurationView
INSTEAD OF UPDATE
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE ct
    SET 
        ct.DomainId = dt.DomainId,
        ct.EntityId = et.EntityId
    FROM ConfigurationTable ct
    INNER JOIN inserted i ON ct.DomainId = i.DomainId AND ct.EntityId = i.EntityId
    INNER JOIN DomainTable dt ON i.DomainName = dt.DomainName
    INNER JOIN EntityTable et ON i.EntityName = et.EntityName
END

Key Notes to Keep in Mind #

• Unique Names: Ensure DomainName in DomainTable and EntityName in EntityTable are unique. If there are duplicate names, the trigger will insert/update multiple records (which you probably don't want).
• Handle Missing Names: Right now, if a user enters a domain/entity name that doesn't exist, the trigger will skip that row. You can add error handling (like THROW statements) to notify users of invalid inputs.
• Limitations: This setup lets users manage the configuration relationships (which domain is paired with which entity). If you need to let users edit the actual DomainName or EntityName values themselves, you'd need to adjust the view and triggers to target the base tables directly—though that's less common for configuration scenarios.

Once you've set this up, users can interact with ConfigurationView in SSMS just like a regular table: entering or editing DomainName and EntityName values, and the triggers will handle the rest under the hood.

内容的提问来源于stack exchange，提问作者user9393635