Вложенные курсоры — это плохо, какие еще варианты у меня есть?
У меня есть два набора значений:
- Группа триггерных событий (например, статус)
- Куча почтовых адресов
У меня есть две заданные таблицы, которые мне не разрешено изменять, давайте назовем их «триггерами» и «действиями». У них есть связь 1:n от триггеров.номер до действий.номертриггера. Обе таблицы имеют «номер» PK, который не увеличивается автоматически.
Теперь мне нужно добавить новую строку в триггеры для каждого статуса, на который я хочу, чтобы программа реагировала. Сначала это кажется простым, но мне также пришлось увеличить число, потому что оно должно быть уникальным по определению таблицы. Затем мне нужно добавить строку для каждого почтового адреса в таблицу «действия», где «номер триггера» должен быть «номером» ранее добавленной строки в «триггеры» (отношение ForeignKey)
Для первой части я нашел ответ в Google, чтобы создать временный файл. таблицу со статусом, создайте на ней курсор, пройдитесь по результатам, извлеките каждую строку и выполните оператор вставки для триггеров таблицы. Прежде чем я объявлю переменную «LastNumber» и установлю для нее результат «Выбрать ISNull(MAX(Number),0)+1 из триггеров». Эту переменную я затем использую для «номера» при вставке в таблицу «триггеры». А позже я могу использовать ее для «номер триггера» в таблице «действия».
Самая простая идея для меня теперь заключалась бы в том, чтобы использовать второй курсор во временной таблице для почтовых адресов, перебирать их, получать каждое письмо и вставлять их в «действия», а также вычислять приращение для столбца «номер» и использовать переменная как «номер триггера»
Проверяя, можно ли вкладывать курсоры, поскольку @@Fetch_Status не привязан явно к одному курсору, я натыкаюсь на ответ на аналогичный вопрос, что вложенные курсоры - это зло, и почти нет случаев, когда это необходимо.
Итак, мой вопрос: есть ли лучший способ добиться того, что мне нужно сделать?
Тем не менее, я добавлю свой код, который у меня есть на данный момент. Поскольку по какой-то непонятной мне причине создатель базы данных создал все с немецкими именами, это может быть немного сложно понять.
Begin Transaction
Declare @Customernumber int
Declare @MailAddresses nvarchar(max)
Set @Customernumber = 13414
Set @MailAddresses = '[email protected],[email protected]'
Declare @NextLFDNREreignis int
Declare @Emails as Table (Mail nVarchar(320))
--### Fill temp Table with Mailaddresses
Insert into @EMails (Mail)
Select
i.value
From string_split(@MailAddresses,',') as i
Declare @Value1 varchar(50)
Declare @Value2 varchar(50)
Declare db_cursor cursor for Select UnterbearbeitungsStatus,Bezeichnung from BenachrichtigungsEreignisse Where Mandant = 1 And KundenNummer = 1 and Geschaeftsbereich = 'Service'
Open db_cursor
Fetch Next from db_cursor Into @Value1,@Value2
While @@FETCH_STATUS = 0
Begin
Set @NextLFDNREreignis = (Select ISNull(MAX(LfdNrEreignis),0)+1 from BenachrichtigungsEreignisse WITH (UPDLOCK, HOLDLOCK))
--### Einfügen der Trigger ###
Insert Into BenachrichtigungsEreignisse
(Mandant
,LfdNrEreignis
,Bezeichnung
,Versandbedingung
,LfdNrGruppe
,KundenNummer
,AngelegtProgramm
,AngelegtDurch
,AngelegtAm)
Values
(1
,@NextLFDNREreignis
,@Value2
,9999
,1
,@Customernumber
,'Script'
,'MM'
,getdate()
)
--### Einfügen der Ereignisse ###
Insert into BenachrichtigungsReaktionen
(Mandant
,LfdNrEreignis
,LfdNrReaktion
,ReaktionsArt
,EmpfaengerAdresse
,AngelegtProgramm
,AngelegtDurch
,AngelegtAm
)
Values
(1
,@NextLFDNREreignis
,1 --Todo:increment number
,1 --send Mail
,'[email protected]'
,'Script'
,'MM'
,getdate()
)
Fetch Next From db_cursor Into @Value1,@Value2
End
close db_cursor
deallocate db_cursor
commit
«BenachrichtigungsEreignisse» — таблица «триггеров». «BenachrichtigungsReaktionen» — таблица «действий».
РЕДАКТИРОВАТЬ!
Хорошо, следующая попытка :)
Я попытался создать для вас пример здесь
Я получаю переменные @CustomerNumber и @MailAddresses из пользовательского ввода. На основе этих переменных я хотел бы сгенерировать для клиента «15000». сгенерируйте записи в Таблице A с одной записью для каждого почтового адреса из переменной @MailAddresses в Таблице B.
Select * from TableA Where CustomerNumber = 0815
Служит шаблоном, записи которого я хочу создать для клиента 1500.
«Set @NextLFDNREreignis = (Select ISNull(MAX(LfdNrEreignis),0)+1 from BenachrichtigungsEreignisse WITH (UPDLOCK, HOLDLOCK))» вы знаете о свойстве IDENTITY или SEQUENCE?
Классическая проблема XY
Подождите, вы хотите сказать, что этот код запускается внутри триггера? Зачем тогда декларации о сделках? Триггеры уже неявно включены в транзакцию, поскольку являются частью операции DML.
Я не думаю, что вам нужны курсоры, но также сложно понять, что вы пытаетесь сделать. Возможно, лучше просто добавить исходные таблицы и указать, чего вы хотите достичь, когда закончите с этим.
MAX(Number)+1 — очень плохая идея, гарантирующая дубликаты. Все, что нужно, — это удалить некоторые из последних значений, и те же числа будут сгенерированы снова.
@ThomA, может быть, потому что я не знаю как. И в основном я понятия не имею, как автоматически увеличить триггеры.номер и опубликовать его в action.triggernumber. Если я правильно понимаю IDENTETY, это изменит мою таблицу, что мне не разрешено, или, лучше, чего я не хочу, потому что это это не моя программа, и я не могу контролировать, останется ли таблица прежней после обновления. ПОСЛЕДОВАТЕЛЬНОСТЬ звучит интересно, но я понятия не имею, как ее использовать. Может быть, у вас есть для меня пример? Нет, это запускается вручную, а не в триггере.
Документация ПОСЛЕДОВАТЕЛЬНОСТЬ — отличное место для начала. Документация SQL Server полна примеров. Боюсь, мне сложно понять некоторые комментарии; Я, например, не знаю, что вы подразумеваете под «азоматики».
Гораздо лучше объяснить, что вы на самом деле хотите сделать, а не как, по вашему мнению, это следует сделать. Запуск этого кода в триггере будет генерировать одни и те же строки снова и снова, потому что inserted нигде не используется. Если вам нужен увеличивающийся номер строки, вы можете использовать функцию ROW_NUMBER, полностью устраняя необходимость в курсоре. Вы можете использовать один SELECT, чтобы получить все строки с отдельными числами.
@PanagiotisKanavos Да, я знаю, но поскольку мой скрипт будет единственным, который будет добавлять или удалять строки, это очень маловероятно.
@Майкл, опубликуй таблицы, примеры данных и ожидаемый результат, объясни проблему. SQL одинаков для всех. this is very unlikely это означает, что при втором запуске произойдет сбой. Ваш запрос каждый раз читает одни и те же строки. И это HOLDLOCK, UPDLOCK предполагает, что это уже не удалось, поэтому вы попытались усилить блокировку. SQL не нуждается в таких трюках
Хорошо, я думал, что описал то, чего хочу достичь, в первой части моего вопроса, но кажется, что это было недостаточно хорошо, или, может быть, мешает языковой барьер.
Что ж, вы много говорите о триггерах в своем вопросе, @Michael, но в своем комментарии выше вы заявили, что этого нет в триггере, поэтому у нас уже есть противоречивая информация. Код, как упоминает Панайотис, не ссылается на объект(ы) inserted (или deleted), которые также можно было бы ожидать для триггера. Отсутствие использования IDENTITY или SEQUENCE и CURSOR делает все это похожим на огромную проблему XY.
Нет, это не языковой барьер. Вы описываете, в чем, по вашему мнению, заключается решение. Для всех этих таблиц нет операторов CREATE TABLE, операторов INSERT INTO, которые вставляют образцы данных, нет примеров вывода. Даже let's call them triggers вызывает путаницу: ТРИГГЕР — это особый тип объекта, созданный с помощью CREATE TRIGGER.
А мы? В самом начале вопроса у нас есть «две заданные таблицы, которые (так в оригинале) мне не разрешено изменять, давайте назовем их «триггерами» и «действиями». Одна из таблиц называется триггеры (которые, во что бы то ни стало, поругаем их за выбор названия таблицы).
Итак, на самом деле вам нужно сделать шаг назад; не говорите нам, что у вас есть, поскольку, при всем уважении, это очень неправильно. Вместо этого покажите нам, как выглядят ваши данные, в идеале с помощью операторов DDL и DML, а затем объясните, чего вы пытаетесь достичь, и продемонстрируйте ожидаемые результаты.
Ладно, я тебя запутал. Извини. «Триггеры» — это абстрактное название на английском языке для первой таблицы, потому что значения в ней именно такие. Это условия, которые проверяются приложением при изменении записей данных (заказов), и если они верны, вторая таблица используется для определения того, какие действия следует выполнить.
Хорошо, я понимаю, извините за это.
Хорошо, я попробую создать для вас образцы таблиц и образцов данных. Я попытался создать оператор создания с помощью SQL Managementstudio, но он полон сотен ограничений, не имеющих отношения к реальному вопросу. Дай мне немного времени
Хорошо, я исправил свой первоначальный вопрос и попытался добавить запрошенные примеры таблиц и ожидаемые результаты. Возможно, это внесет ясность.
Почему тип триггера не уникален по статусу? например 1,'disponierbar' 1,'geliefert' и на таком англоязычном сайте, как этот, было бы более значимо, если бы статус был на английском языке
@P.Salmon TriggerType — это всего лишь ссылка на другие столбцы в том порядке, на который программа должна реагировать. Однако для примера это не важно. Например, TriggerType 1 означает, что программа сверяет статус со статусом обработки, тогда как TriggerType 2 проверяет статус подобработки. Как я уже сказал, это на самом деле не имеет значения для примера.
И да, строка могла быть на английском, но с одной стороны Аток не смог придумать перевод, а с другой стороны это тоже совершенно не важно, в конце концов программа делает сравнение строк и тоже может сказать ' hfghjkö', и это тоже сработает.
Ответы 1
Я предлагаю сделать следующее: создать временную таблицу, в которую вы заполняете то, что хотите вставить в таблицу «триггеры».
Для идентификатора вы получаете max(id) так, как вы это делаете, а затем просто обновляете исходную таблицу, и все готово.
Вот скелетная реализация того, как я это сделаю:
create table #sources (newID INT, someData NVARCHAR(MAX))
insert into #sources (someData)
values( 'sometrigger'), ('someothertrigger'), ('someNthTrigger')
declare @id int
select @id = max(id)
from YourTriggerTable with (someLocks)
update t
set newID = newIDNew + @id
from (
select *
, row_number() over(order by (select null)) as newIDNew
from #sources
) t
insert into YourTriggerTable (
ID, someData)
select newID, someData
from #sources s
-- Now for emails...
Insert into YourTriggerAction (
TriggerID, ActionID, Email
)
SELECT newID, (SELECT MAX(ActionID) FROM YourTriggerAction WITH (SOMELOCKS)) + ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
, em.value
FROM #sources s
CROSS APPLY STRING_SPLIT('[email protected],[email protected]', ',') em
Должен признать, я действительно думаю, что SEQUENCE здесь было бы гораздо лучшим выбором, если ОП не может ALTER использовать существующую таблицу.
Я не согласен, но основная идея практически такая же, разница лишь в том, как получаются максимальные значения.
Вы не получите максимум для последовательности, вы просто получите NEXT VALUE FORSEQUENCE, который работает как для наборов данных, так и для отдельных строк: db<>fiddle
Если бы это был новый дизайн, SEQUENCE мог бы быть лучшим выбором, но ОП добавляет процесс к существующему приложению (о чем свидетельствуют «две заданные таблицы, которые мне не разрешено изменять»). Где-то в стеке приложения уже может быть логика, которая вставляет строки в эти таблицы с помощью собственной логики назначения идентификаторов. Если бы последовательность была реализована, эту логику также необходимо было бы изменить. Основная идея приведенного выше решения заключается в том, что в операциях над множествами может быть назначен диапазон значений идентификаторов (по одному для каждой целевой таблицы), что устраняет необходимость в каких-либо курсорах.
Это не просто вложенные курсоры, а вообще курсоры плохие. Почему вы вообще здесь используете
CURSOR? Почему бы не проанализировать ваши данные так, как они есть: набор данных.