Блог

Главная - Вопросы по программированию - Реализация отношения 0 ..1 в tsql

Реализация отношения 0 ..1 в tsql

#sql #sql-server #tsql #upsert

#sql #sql-сервер #tsql #upsert

Вопрос:

У меня есть таблица, в которой я хотел бы удалить много нулевых столбцов, но я не уверен, что я иду в правильном направлении.

Вот пример SQL, чтобы показать, что я хочу (удаление количества из продуктов):

 CREATE TABLE [Products]([Id] [int] NOT NULL,[Name] [varchar](50))
CREATE TABLE [StockProducts]([Id] [int] NOT NULL,[Quantity] [int])
go
insert into Products (Id, [Name]) values (1,'Socks')
insert into Products (Id, [Name]) values (2,'Gloves')
CREATE VIEW ProductStockView as select P.Id, SP.Quantity from Products P left join StockProducts SP on P.Id = SP.Id
go
select * from ProductStockView
go
update ProductStockView set Quantity = 2 where Id = 1

Мне бы очень хотелось, чтобы обновление добавляло вставку в мою таблицу StockProducts, потому что у меня есть все идентификаторы продуктов, и мой существующий код будет продолжать работать.

Существует ли шаблон для этого, или предпочтительным способом было бы просто добавить триггер обновления в представление? Прямо сейчас оператор update выдает: 0 затронутых строк.

Редактировать на основе двух комментариев: Идентификатор двух таблиц следует рассматривать как первичные ключи (для использования в таких средах, как Entity Framework). И мой реальный пример, конечно, заключается в том, что таблица с именем Products в моем примере — это старая таблица, которая у нас есть с 75 столбцами, а ProductStockView — это то, чем я хотел бы заменить ее в частях моего кода.

Комментарии:

1. Я чувствую, что реальный вопрос здесь заключается в том, почему вы обновляете представление ProductStockView , а не выполняете обновление (или, скорее, Upsert/ MERGE ) против StockProducts ?

2. Как минимум, ваш DDL должен содержать правильные ограничения первичного ключа и внешнего ключа, чтобы никто не догадался о ваших намерениях. И трудно предлагать полезные предложения без гораздо лучшего понимания вашей модели. Редко у кого есть одна таблица, которая представляет запасы (или текущий уровень запасов или любой другой подходящий термин). Возможно, ваша модель намного более упрощена и не соответствует реальной ситуации. Если это так, то это важная информация, хотя бы для того, чтобы не вдаваться в подробности, которые не нужны.

3. Вы могли бы использовать merge для обработки upsert , т. Е. update Или insert по мере необходимости. Его можно даже обучить delete , если значение Quantity равно нулю.

4. К сожалению, ваши варианты — либо заменить ваши инструкции UPDATE на MERGE, как описано выше, либо добавить триггер для ОБНОВЛЕНИЯ таблицы. Если вы предпочитаете вообще не изменять свои инструкции UPDATE, то триггер — ваша единственная надежда.

Ответ №1:

Спасибо за комментарии. Это было именно то, что мне было нужно. Я оставлю ответ здесь:

 create trigger ProductStockViewUpdateTrigger
on ProductStockView instead of update
as
MERGE StockProducts as target
USING (SELECT Id, Quantity from inserted) as source
    ON target.Id = source.Id
WHEN MATCHED
    AND target.Quantity != source.Quantity
        THEN UPDATE 
            SET target.Quantity = source.Quantity
WHEN NOT MATCHED by target
THEN 
    INSERT ([Id], [Quantity])
    values (source.id, source.quantity);