in

SQL Server – Error updating rows in SSMS via a multi-table view


I’m designing a database back-end that currently does not have a UI. For now, users will manage the data through a view layer using the “Edit Top 200 rows” option in SQL Server Management Studio (SSMS).

For simple, single-table views, INSERT, UPDATE, DELETE all work fine.

For complex, multi-table views, however, INSERT and DELETE are ok but UPDATE fails:

If I manually run an UPDATE SQL statement on the complex view, it works fine, which suggests that the view and trigger are working as expected.

Any ideas on why the UPDATE fails with the “Edit Top 200 rows” feature in SSMS? Is there a way to get the generated SQL when editing through this tool?

View

CREATE VIEW dbo.vw_Country AS
SELECT 
  cnt.Country_Id, 
  cnt.Country_Name, 
  cnt.Country_Code, 
  r.Region_Code, 
  cur.Currency_Code
FROM dbo.Country cnt
INNER JOIN dbo.Region r ON cnt.Region_Id = r.Region_Id -- Get region info
INNER JOIN dbo.Currency cur ON cnt.Currency_Id = cur.Currency_Id; -- Get currency info

Trigger

CREATE TRIGGER dbo.tr_Country_update ON dbo.vw_Country INSTEAD OF UPDATE AS
BEGIN

    -- Do some validation first (removed in snippet)

    -- Update base table
    UPDATE dbo.Country
    SET Country_Code = ins.Country_Code,
        Country_Name = ins.Country_Name,
        Currency_Id = cur.Currency_Id,
        Region_Id = r.Region_Id
    FROM inserted ins -- New values
    INNER JOIN deleted del ON ins.Country_ID = del.Country_ID -- Old values
    INNER JOIN dbo.Country cty ON ins.Country_Id = cty.Country_Id -- PK field can't change
    INNER JOIN dbo.Currency cur ON ins.Currency_Code = cur.Currency_Code -- Get Currency Id
    INNER JOIN dbo.Region r ON ins.Region_Code = r.Region_Code -- Get Region Id
    WHERE ins.Country_Code <> del.Country_Code
    OR ins.Country_Name <> del.Country_Name
    OR ins.Currency_Code <> del.Currency_Code -- Currency_Code is alternate key
    OR ins.Region_Code <> del.Region_Code -- Region_Code is alternate key
END
GO

Table

CREATE TABLE dbo.COUNTRY (
    -- General
    COUNTRY_ID INTEGER IDENTITY(1,1) NOT NULL,
    CURRENCY_ID INTEGER NOT NULL,
    REGION_ID INTEGER NOT NULL,
    COUNTRY_NAME NVARCHAR(100),
    COUNTRY_CODE NVARCHAR(5),
    -- Temporal
    LAST_UPDATED_BY_USER AS (SUSER_SNAME()),
    CREATED_BY_USER NVARCHAR(128) NOT NULL DEFAULT (SUSER_SNAME()),
    VALID_FROM DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL 
      DEFAULT SYSUTCDATETIME(),
    VALID_TO DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL 
      DEFAULT CAST('9999-12-31 23:59:59.9999999' AS DATETIME2), 
    PERIOD FOR SYSTEM_TIME (VALID_FROM, VALID_TO),
    -- Constraints
    CONSTRAINT PK_COUNTRY PRIMARY KEY CLUSTERED (COUNTRY_ID),
    CONSTRAINT UC_COUNTRY_CODE UNIQUE NONCLUSTERED (COUNTRY_CODE),
    CONSTRAINT UC_COUNTRY_NAME UNIQUE NONCLUSTERED (COUNTRY_NAME)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.COUNTRY_history))



Source: https://stackoverflow.com/questions/70627155/sql-server-error-updating-rows-in-ssms-via-a-multi-table-view

A react-leaflet & express app for visualizing the latest earthquake data from Kandilli Observatory

AWS Releases Fully Managed Data Lake for CloudTrail Logs