Триггеры базы данных
Решение MySQL 8 с JSON и CTE
Предположим, это ваша таблица:
create table old_table(
data text
);
insert into old_table(data)values
("['ffffffff-11111-1111-baaa-xxxx']"),
("['zxyvvv-1234567-abcdefghijk', '1234567-abcdefg-hijklmn']");
И вы хотите «перенести» данные в две новые таблицы:
create table parent_table(
parent_id int primary key
);
create table child_table(
child_id int auto_increment primary key,
parent_id int not null,
data varchar(100),
foreign key (parent_id) references parent_table(parent_id)
);
Сначала создайте (временную) копию вашей старой таблицы со столбцом AUTO_INCREMENT id:
create table tmp_table(
id int auto_increment primary key,
data json
);
При копировании из старой таблицы преобразуйте данные в JSON:
[ 113]
Заполните parent_table идентификаторами из tmp_table:
insert into parent_table(parent_id)
select id from tmp_table;
Теперь (основная часть) заполните child_table следующим запросом:
insert into child_table(parent_id, data)
with recursive seq(i) as ( -- sequence numbers 0 to 999
select 0
union all
select i + 1
from seq
where i < 999
)
select t.id as parent_id
, json_unquote(json_extract(t.data, concat('$[', s.i, ']'))) as data
from tmp_table t
join seq s on s.i <= json_length(t.data)-1;
child_table теперь содержит следующие данные:
child_id parent_id data
1 1 ffffffff-11111-1111-baaa-xxxx
2 2 zxyvvv-1234567-abcdefghijk
3 2 1234567-abcdefg-hijklmn
Основная идея - объединить tmp_table с последовательностью число от 0 до 999 (генерируется с помощью рекурсивного CTE) и используйте эти числа для извлечения соответствующих элементов из массива JSON.
12 ответов
Триггеры обычно используются неправильно, представляют ошибки и поэтому должны избежаться. Никогда не разрабатывайте триггер, чтобы сделать ограничение целостности, проверяющее, что перекрестные строки в таблице (например, "средняя зарплата отделом не может превысить X).
Tom Kyte , VP Oracle указал, что он предпочел бы , удаляют триггеры как функцию базы данных Oracle из-за их частой роли в ошибках. Он знает, что это - просто мечта, и триггеры устанавливаются, но если он мог он удалять триггеры из Oracle, он был бы (наряду с КОГДА пункт ДРУГИХ и автономные транзакции).
Может триггеры использоваться правильно? Абсолютно.
проблема - они не используются правильно в таком количестве случаев, что я был бы готов бросить любое воспринятое преимущество только для избавлений от злоупотреблений (и ошибки) вызванный ими. - Tom Kyte
Думайте о базе данных как о большом большом объекте - после каждого вызова к нему, это должно быть в логически согласованное состояние.
Базы данных представляют себя через таблицы, и таблицы хранения и последовательные строки могут быть сделаны с триггерами. Другой способ сохранить их последовательными состоит в том, чтобы запретить прямой доступ к таблицам, и только разрешение его через хранимые процедуры и представления.
оборотная сторона триггеров - то, что любое действие может вызвать их; это - также сила - никто не собирается завинтить целостность системы через некомпетентность.
, Поскольку контрапункт, предоставляя доступ к базе данных только через хранимые процедуры и представления все еще предоставляет закулисный доступ полномочий. Пользователи с достаточными полномочиями доверены для не повреждения целостности БД, все другие используют хранимые процедуры.
относительно сокращения объема работы: базы данных потрясающе эффективны, когда они не должны иметь дело с внешним миром; Вы были бы действительно удивлены, сколько даже обрабатывает переключающуюся производительность вреда. Это - другой позитивный аспект хранимых процедур: вместо дюжины вызовов к базе данных (и все связанные распространения в прямом и обратном направлениях), существует то.
материал Группирования в сохраненном proc сингла прекрасен, но что происходит, когда что-то идет не так, как надо? Скажите, что у Вас есть 5 шагов и сбои первого шага, что происходит с другими шагами? Необходимо добавить целый набор логики там для питания ту ситуацию. Как только Вы начинаете делать это, Вы теряете преимущества хранимой процедуры в том сценарии.
Бизнес-логика должна пойти куда-нибудь, и существует много подразумеваемых доменных правил, встроенных в дизайн базы данных - отношения, ограничения и так далее являются попыткой шифровать бизнес-правила путем высказывания, например, у пользователя может только быть один пароль. Учитывая Вы начали пихать бизнес-правила на сервер базы данных при наличии этих отношений и так далее, где Вы разграничиваете? Когда база данных бросает ответственность за целостность данных и начинает доверять приложениям вызова и пользователям базы данных для разбираний в нем? Хранимые процедуры с этими правилами, встроенными в них, могут продвинуть большую политическую власть в руки DBAs. Это сводится, сколько уровней собирается существовать в Вашей n-tier архитектуре; если существует представление, бизнес и слой данных, где делает разделение между бизнесом, и данные лежат? Какое значение - добавляют, что бизнес-слой добавляет? Вы выполните бизнес-слой на сервере базы данных как хранимые процедуры?
Да, я думаю, что необходимость обойти триггер означает "выполнение ее неправильно"; в этом случае триггер не для Вас.
Я работаю с сетью и приложениями winforms в c# и мне НЕНАВИСТЬ триггеры со страстью. Я никогда не сталкивался с ситуацией, где я мог выровнять по ширине использование триггера по перемещению той логики на бизнес-слой приложения и тиражирования триггерной логики там.
я не делаю никакой работы типа DTS или чего-либо как этот, таким образом, могли бы быть некоторые варианты использования для использования триггера там, но если кто-либо в какой-либо из моих команд говорит, что они могли бы хотеть использовать триггер, они лучше подготовили свои аргументы хорошо, потому что я отказываюсь стоять в стороне и позволять триггерам быть добавленными к любой базе данных, я продолжаю работать.
Некоторые причины, почему мне не нравятся триггеры:
- Они перемещают логику в базу данных. Как только Вы начинаете делать это, Вы просите мир боли, потому что Вы теряете свою отладку, свою безопасность времени компиляции, свой логический поток. Это - весь скоростной спуск.
- логика, которую они реализуют, не легко видима никому.
- Не все триггеры поддержки механизмов базы данных так Ваше решение создают зависимости от механизмов базы данных
, я уверен, что мог думать о большем количестве причин первое, что пришло на ум, но одни достаточно для меня для не использования триггеров.
"Никогда не разрабатывайте триггер, чтобы сделать ограничение целостности, проверяющее, что перекрестные строки в таблице" - я не могу согласиться. Вопрос отмечен, 'SQL Server' и пункты ограничений CHECK в SQL Server не могут содержать подзапрос; хуже, реализация, кажется, имеет 'трудное кодированное' предположение, что ПРОВЕРКА включит только единственную строку, настолько использующая функция не надежна. Таким образом, если мне нужно ограничение, которое действительно законно включает больше чем одну строку - и хорошим примером здесь является упорядоченный первичный ключ в классическое 'допустимое время' временная таблица, где я должен предотвратить перекрывающиеся периоды для того же объекта - как я могу сделать это без триггера? Помните, что это - первичный ключ, что-то, чтобы гарантировать, чтобы у меня была целостность данных, таким образом осуществление ее где угодно кроме DBMS вне рассмотрения. Пока ограничения CHECK не получают подзапросы, я не вижу альтернативу использованию триггеров для определенных видов ограничений целостности.
Триггеры могут быть очень полезными. Они могут также быть очень опасными. Я думаю, что они хорошо для задач уборки в доме как заполнение контрольных данных (созданный, измененная дата, и т.д.) и в некоторых базах данных может использоваться для ссылочной целостности.
, Но я не большой поклонник помещения большой бизнес-логики в них. Это может сделать поддержку проблематичной потому что:
- это - дополнительный слой кода к исследованию
- иногда как изученный OP, когда необходимо сделать, данные фиксируют триггер, мог бы делать вещи учитывая, что изменение данных всегда с помощью директивы приложения а не от разработчика или DBA, решающего проблему, или даже из различного приложения
Что касается необходимости обойти триггер, чтобы сделать что-то, это могло означать выполнение чего-то не так, или это могло означать, что триггер делает что-то не так.
общее правило, которое мне нравится использовать с триггерами, состоит в том, чтобы сохранить их легкими, быстрыми, простыми, и максимально неразрушающими.
Я обхожу триггеры при выполнении объемного импорта данных. Я думаю, что это выравнивается по ширине при таких обстоятельствах.
, Если Вы заканчиваете тем, что обходили триггеры очень часто, хотя, вероятно, необходимо бросить другой взгляд на то, для чего Вы помещаете их там во-первых.
В целом, я голосовал бы за, "они служат цели в некоторых сценариях". Я всегда озабочен последствиями производительности.
Я не поклонник, лично. Я буду использовать их, но только когда я раскрываю узкое место в коде, который может быть очищен движущимися действиями в триггер. Обычно я предпочитаю простоту, и один способ сохранить вещи простыми состоит в том, чтобы сохранить логику в одном месте - приложение. Я также работал над заданиями, где доступ очень разделен. В тех средах больше кода я упаковываю в триггеры больше людей, которых я должен привлечь для даже самых простых мер.
Я сначала использовал триггеры несколько недель назад. Мы переключили рабочий сервер от SQL 2000 к SQL 2005, и мы нашли, что драйверы вели себя по-другому с полями NText (хранящий большой XML-документ), привозя последний байт. Я использовал триггер в качестве временного приспособления для добавления дополнительного фиктивного байта (пространство) до конца данных, решая нашу проблему, пока надлежащее решение не могло быть реализовано.
Кроме этого специального, временного случая, я сказал бы, что избегу их, так как они действительно скрывают то, что продолжается, и функция, которую они обеспечивают, должна быть обработана explictly разработчиком скорее тогда как некоторое скрытое волшебство.
Честно единственное время, я использую триггеры для моделирования уникального индекса, которому позволяют иметь ПУСТОЙ УКАЗАТЕЛЬ, которые не значат уникальность.
относительно сокращения объема работы: базы данных потрясающе эффективны, когда они не должны иметь дело с внешним миром; Вы были бы действительно удивлены, сколько даже обрабатывает переключающуюся производительность вреда. Это - другой позитивный аспект хранимых процедур: вместо дюжины вызовов к базе данных (и все связанные распространения в прямом и обратном направлениях), существует то.
это немного вне темы, но необходимо также знать, что Вы только смотрите на это от одного положительного потенциала.
материал Группирования в сохраненном proc сингла прекрасен, но что происходит, когда что-то идет не так, как надо? Скажите, что у Вас есть 5 шагов и сбои первого шага, что происходит с другими шагами? Необходимо добавить целый набор логики там для питания ту ситуацию. Как только Вы начинаете делать это, Вы теряете преимущества хранимой процедуры в том сценарии.
Общий вентилятор,
, но действительно должны использовать его экономно, когда,
-
Потребность поддержать непротиворечивость (особенно, когда таблицы измерений используются на складе и мы должны связать данные в таблице фактов с их надлежащим размером. Когда-то, надлежащая строка в таблице измерений может быть очень дорогой для вычисления так, Вы хотите, чтобы ключ был записан прямо в таблицу фактов, один хороший способ поддержать то "отношение" с триггером.
-
Потребность зарегистрировать изменения (в контрольной таблице, например, полезно знать то, что @@ пользователь сделал изменение и когда это произошло)
Некоторый RDBMS как SQL-сервер 2005 также предоставляет Вам, включает операторы CREATE/ALTER/DROP (таким образом, можно знать, кто создал то, что таблица, когда, отбросила что столбец, когда, и т.д.)
Честно, с помощью триггеров в тех 3 сценариях, я не вижу, почему необходимо было бы когда-либо "отключать" их.
Общее правило ползунка: не используйте триггеры. Как упомянуто прежде, они добавляют наверху и сложность, которой может легко избежать движущаяся логика из уровня DB.
кроме того, в SQL Server MS, триггеры запущены однажды на команду sql, а не на строку. Например, следующий sql оператор выполнит триггер только однажды.
UPDATE tblUsers
SET Age = 11
WHERE State = 'NY'
у Многих людей, включая меня, создалось впечатление, что триггеры запущены в каждую строку, но это не имеет место. Если у Вас есть sql оператор как тот выше этого, может изменить данные больше чем в одной строке, Вы могли бы хотеть включать курсор для обновления всех записей, затронутых триггером. Вы видите, как это может стать замысловатым очень быстро.