Использование значений по умолчанию в триггере INSTEAD OF INSERT

Мы выполняем миграцию базы данных на SQL Server, и для поддержки унаследованного приложения мы определили представления в таблице SQL Server, которые представляют данные в соответствии с ожиданиями унаследованного приложения.

Однако теперь у нас возникают проблемы с триггерами INSTEAD OF INSERT, определенными для этих представлений, когда поля могут иметь значения по умолчанию.

Я постараюсь привести пример.

Таблица в базе данных имеет 3 поля, a, b и c. c является совершенно новым, унаследованное приложение не знает об этом, поэтому у нас также есть представление с 2 полями, a и b.

Когда устаревшее приложение пытается вставить значение в его представление, мы используем триггер INSTEAD OF INSERT, чтобы найти значение, которое должно идти в поле c, что-то вроде этого:

INSERT INTO realTable(a, b, c) SELECT Inserted.a, Inserted.b, Calculated.C FROM...

(Детали поиска не имеют отношения.)

Этот триггер работает хорошо, если поле b не имеет значения по умолчанию. Это потому, что если запрос

INSERT INTO legacyView(a) VALUES (123)

выполняется, затем в триггере Inserted.b имеет значение NULL, а не значение по умолчанию для b. Теперь у меня есть проблема, потому что я не могу отличить вышеупомянутый запрос, который поместил бы значение по умолчанию в b, и это:

INSERT INTO legacyView(a,b) VALUES (123, NULL)

Даже если b не NULLABLE, я не знаю, как написать запрос INSERT в триггере так, чтобы, если значение было предоставлено для b, оно использовалось в триггере, но вместо этого использовалось значение по умолчанию.

РЕДАКТИРОВАТЬ: добавлено, что я бы предпочел не дублировать значения по умолчанию в триггере. Значения по умолчанию уже есть в схеме базы данных, я надеюсь, что смогу просто использовать их напрямую.

Источник

3 ответа

Решение

Пол: Я решил это; в конце концов. Немного грязное решение, и оно может быть не всем по вкусу, но я довольно новичок в SQL Server и таких как:

В триггере Why_of_INSERT:

  1. Скопируйте структуру данных вставленной виртуальной таблицы во временную таблицу:

    SELECT * INTO aTempInserted FROM Inserted WHERE 1=2

  2. Создайте представление, чтобы определить ограничения по умолчанию для базовой таблицы представления (из системных таблиц) и использовать их для построения операторов, которые будут дублировать ограничения во временной таблице:

    SELECT  'ALTER TABLE dbo.aTempInserted
               ADD CONSTRAINT ' + dc.name + 'Temp' +
               ' DEFAULT(' + dc.definition + ') 
               FOR ' + c.name AS Cmd, OBJECT_NAME(c.object_id) AS Name
  FROM  sys.default_constraints AS dc
 INNER  JOIN sys.columns AS c
          ON dc.parent_object_id = c.object_id 
         AND dc.parent_column_id = c.column_id

  3. Используйте курсор для перебора полученного набора и выполнения каждого оператора. Это оставляет вам временную таблицу с теми же значениями по умолчанию, что и для таблицы, в которую вы хотите вставить.

  4. Вставьте запись по умолчанию во временную таблицу (все поля обнуляются, как созданные из вставленной виртуальной таблицы):

    INSERT INTO aTempInserted DEFAULT VALUES

  5. Скопируйте записи из виртуальной таблицы Inserted в базовую таблицу представления (куда они были бы вставлены изначально, если бы триггер не предотвратил это), присоединившись к временной таблице для предоставления значений по умолчанию. Для этого необходимо использовать функцию COALESCE, чтобы по умолчанию использовались только неподдерживаемые значения:

    INSERT INTO realTable([a], [b], 
            SELECT COALESCE(I.[a], T.[a]),
                   COALESCE(I.[a], T.[b])
            FROM   Inserted      AS I,
                   aTempInserted AS T

  6. Удалите временную таблицу

Источник

Некоторые идеи:

  • Если унаследованное приложение задает списки столбцов для INSERT и присваивает имена столбцам вместо использования SELECT *, то нельзя ли просто привязать значение по умолчанию к столбцу c и позволить приложению использовать вашу исходную (измененную) таблицу?

  • Если бы был какой-либо способ заставить устаревшее приложение использовать для своих INSERTs представление или таблицу, отличное от SELECT или DELETE, вы могли бы установить необходимые значения по умолчанию для этой таблицы и использовать обычный после-триггер для перемещения новых столбцов в настоящий стол.

  • Как насчет того, чтобы оставить исходную таблицу в одиночестве и добавить дополнительные столбцы в отдельную таблицу, которая имеет отношение 1-1 к оригиналу? Затем создайте представление, которое объединяет эти две таблицы и поместите соответствующие триггеры вместо этого нового представления для обработки всех операций с данными, разбитых по двум таблицам. Я понимаю, что это влияет на производительность, но это может быть единственным способом решения проблемы. Это было бы идеальным случаем для материализованного представления, которое замедляло бы обновления, но заставляло результат работать точно так же, как таблица для чтения. (Материализованные представления лучше всего подходят для внутренних объединений и не требуют агрегирования. Они также устанавливают блокировки схемы для исходных таблиц.)

  • Я столкнулся с подобной проблемой, когда не смог определить разницу между намеренно NULL-значениями и пропущенными столбцами в триггере вместо UPDATE в представлении. В конце концов я сделал вместо представления INSERT триггер для представления вставок в обновления (если ключ уже существовал, это было обновление, в противном случае это была вставка). Хотя это не поможет вам напрямую, это может стимулировать некоторые идеи для вас или других.

Источник

Как насчет использования что-то вроде этого:

insert into realtable
values inserted.a, isnull(inserted.b, DEFAULT), computedC
from inserted
Источник
Другие вопросы по тегам