удаление связанных записей в БД

[NewUser]

в таблице 1 сотрудники - поля
id
name
otdel
dolgn

2 таблица - должности
id
name

3 таблица - отдел
id
name

таблица 1, связана с 2 и 3 по otdel и dolgn
теперь если я удалю запись из 2 или 3 таблицы 1 обратится к несуществующей записи, каким образом решается такая зада?

если выбирать
1.otdel=2.id AND 1.dolgn=3.id
то сотрудник у которого попадает должность под удаленную виден не будет

[v-t-l]

Используй LEFT JOIN

[SAK]

Вообще-то исходя из логики для данного случая, надо бы запретить удаление записей из 2-й и 3-й таблиц, если они используются в 1-й. А то получается сотрудник без должности - нелогично.

[Михаил]

создай триггер чтобы выполнялся до удаления записей из 2-й и 3-й таблицы...... можешь либо удалить эти поля из 1-й таблицы, либо как-нибудь их пометить

Код: Выделить всё

CREATE TRIGGER delete2  FOR 2
BEFORE DELETE AS
BEGIN
  DELETE FROM 1 WHERE DOLGN=OLD.id;
END

CREATE TRIGGER delete3  FOR 3
BEFORE DELETE AS
BEGIN
  DELETE FROM 1 WHERE otdel=OLD.id;
END


[NewUser]

при таких условиях я не смогу обращаться к сотрудникам, у которых удалены должности или отделы. получается один вариант - не удалять их, а если удалять, то с уверенностью, что запись нигде не используется

[debi12345]

А в чем проблема ?

Всегда нужно знать, что происходит и не должно быть никаких "сюрпризов" для пользователей, для этого нужно :

1) отказаться от использования триггеров (что Вам предложено ) и каскадированного удаления ( на что Вы намекаете )
2) проверять наличие ссылок на другие таблицы и уведомлять об этом пользователя !
3) если пользователь все же подтверждает удаление - запомнить ID мастер-аблицы, затем открыть транзакцию, удалить в подчиненных таблицах ссылки на этот ID, потом удалить в мастер-таблице, и подтвердить транзакцию.

Необоснованное (как здесь) использование триггеров плохо еще и тем, что :

1) замедляет работу сервера (даже если на мизер - но зачем это нужно ? )

2) создает проблемы с обслуживанием БД типа "отбросить устаревшие" (потребуется временное удаление этих триггеров, потом пересоздание )

ПС:

А для просмотра записей с удаленными ссылками наряду с оставшимися ссылками - да, LEFT/RIGHT OUTER JOIN. Именно OUTER.

[Attid]

debi12345

1) отказаться от использования триггеров

тригеров не надо боятся они хорошие.

замедляет работу сервера

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

(потребуется временное удаление этих триггеров, потом пересоздание )

во первых их можно просто деактивировать
во вторых предусматривать такие вещи заранее.

NewUser
каскадированное удаление это вообще большое зло если не уметь им пользоваться , соответственно вам советую отказаться.

и нечего без надобности перекрестные ссылки делать.

[debi12345]

Ну не знаю..
За последний год поудалял почти все триггеры - и вздохнул широкой грудью. Оставил только триггеры для автоматического пересчета итогов ( пару навороченных функций по несколько сот строк кода каждая ) и для замены черновых записей-залушек, то есть операций, недоступных пользователям.
Вообще, даже авторы серверов БД убеждают - "используйте триггеры только при крайней необходимости".
Ну и самая большая ошибка начинающих - использование ссылочой целостности и FOREIGN_KEY ( внутренне реализуемых на триггерах ). Это такой потенциальный геморрой !

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

Зато хук на этот триггер проверяется на каждой операции с записями. Посмотрите исходники любого сервера БД.
А отдельные запросы отрабатывают строго по необходимости.

Да, еще, спрятанные триггерные операции могут выключать индексирование, или наоборот - включать там , где оно вызывает тормоза. Можно в скорости в десятки раз проиграть. И задолбаешься искать, в чем причина - сколько таких историй в Сети !

[alexs]

Очень смелы и спорные утверждения
на мой взягляд - тригеры - это ОЧЕНЬ удобное средство
но как и любой инструмент ими надо уметь пользоваться

Зато хук на этот триггер проверяется на каждой операции с записями

Ну не на каждой операции -а только на ттой к типу который принадлежит тригер

Ну и самая большая ошибка начинающих - использование ссылочой целостности и FOREIGN_KEY

вы сказал не верно - надо так:

Ну и самая большая ошибка начинающих - НЕ использование ссылочой целостности и FOREIGN_KEY

я столько огрёб с чужими данными в которых защит по уелостности не было - что это для меня очень важно
пусть приложение будет чуть медленне (но обычно и замедлений нет) - но данные ВСЕГДа будут именно теми, которые ты ожидаеш

И вобще - обычно проблемы появляются при полном не жалении правильно спректировать БД - принци сначал напишем - а потом подумем как оно работает

[debi12345]

Ну не на каждой операции -а только на той к типу который принадлежит тригер

А этого мало ?

я столько огрёб с чужими данными в которых защит по уелостности не было - что это для меня очень важно
пусть приложение будет чуть медленне (но обычно и замедлений нет) - но данные ВСЕГДа будут именно теми, которые ты ожидаеш

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

Ну и самая большая ошибка начинающих - НЕ использование ссылочой целостности и FOREIGN_KEY

Эта целостность создает наитупейшие триггеры - этим все сказано. Которые еще и не всегда можно отключить - например, в старых версиях PostgreSQL.

на мой взягляд - тригеры - это ОЧЕНЬ удобное средство
но как и любой инструмент ими надо уметь пользоваться

Еще какое удобное! Например, благодаря им удалось избежать постоянного запуска 30-минутного итогового запроса с "GROUP BY", вместо этого триггерная функция рассчитывает и пишет эти итоги в спецтаблицу непосредственно в момент репликации. Данный отход ( ведение таблицы итогов вместо актуального их расчета через SQL-группировку ) от нормального реляционного подхода к проектированию БД дал экономию времени в сотни раз. А вы говорите - "грамотное проектирование". Иногда "неграмотное" дает суперэффект!

Но "ссылочная целостность" и каскадированные операции - уж увольте !

[Attid]

Ссылочную целостность нужно гарантировать пользовательскими программами,

в многопользовательском варианте это просто невозможно сделать.
через глабли конечно можно все, но зачем?

Эта целостность создает наитупейшие триггеры - этим все сказано. Которые еще и не всегда можно отключить - например, в старых версиях PostgreSQL.

ошибки бывают у всех даже у серверов БД =)
механизм может и похож на тригерный, но работает вне транзакций.

пишет эти итоги в спецтаблицу

может лучше былоб сделать функцию которая положит итоги в эту таблицу и запускать её после реплики?? хотя это все зависит от структуры и сервера.

"неграмотное" дает суперэффект!

Зачем же вы это в слух говорите, тут же дети ходят, им и так правила не писаны тк они еще решат что они ошибочны.
я бы не утверждал что это не грамотно, иногда нет другого выбора.
помнится были процедуры для отчетов запускаемые на ночь, чтоб на не загруженых серерах все пересчиталось и утром у начальства уже работали с пол тычка.

[NewUser]

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

я так понимаю что тогда их можно вовсе не создавать?

[debi12345]

я так понимаю что тогда их можно вовсе не создавать?

Чем меньше ограничений в структуре БД - тем лучше. Уж поверьте ! Лучше эти ограничения ввести на более высоком уровне - в пользовательской программе. Пользователь всегда должен знать, когда он что-то делает неправильно !
Тем более что все равно БД-компоненты не умеют гарантированно правильно извещать о типах ошибок при работе с БД ( например - ошибка нарушения ссылочной целостности ), коды и тексты ошибок меняются,... - поэтому среагировать на такую ошибку проблематично.

[debi12345]

в многопользовательском варианте это просто невозможно сделать.
через глабли конечно можно все, но зачем?

Ну уж прям-таки грабли:

1) механизм BEGIN/LOCK ../COMMIT
2) однократная выборка ( "дотрагивание" ) новых ID через SEQUENCE

И отсутствие конфликтов гарантировано.

может лучше было б сделать функцию которая положит итоги в эту таблицу и запускать её после реплики?? хотя это все зависит от структуры и сервера.

Фишка этого подхода в том, что при репликации идут интенсивные вставки записей ( медленная операция, так как - запись на файловую систему ), на фоне которой чуток затянутый SELEСT-запрос, инициируемый триггером, и вызываемый по факту и делающий точечный пересчет (потому что имеет всю информацию "на руках" ) - погоды не делает. Обычным же путем приходилось пересчитывать все скопом, "не-точечно" - что отнимало огромное время и поэтому не позволяло запускать это отчет несколько раз в день.

Зачем же вы это в слух говорите, тут же дети ходят, им и так правила не писаны тк они еще решат что они ошибочны.
я бы не утверждал что это не грамотно, иногда нет другого выбора.

Это я к тому, что догматизм бывает вреден - особенно в столь творческой профессии. Многие БД-админы грешат параноидальной приверженностью к "нормализации" БД.

[Сергей Смирнов]

Чем меньше ограничений в структуре БД - тем лучше. Уж поверьте ! Лучше эти ограничения ввести на более высоком уровне - в пользовательской программе. Пользователь всегда должен знать, когда он что-то делает неправильно !

А как Вы думаете, если пользователь знает, что он что-то делает неправильно, он точно не будет так делать?

Тем более что все равно БД-компоненты не умеют гарантированно правильно извещать о типах ошибок при работе с БД ( например - ошибка нарушения ссылочной целостности ), коды и тексты ошибок меняются,... - поэтому среагировать на такую ошибку проблематично.

Что за ужасные сервера БД и жуткие компоненты доступа к ним Вы использовали?

ЗЫ: Не учите детей плохому. Ссылочная целостность - это благо и единственный гарантированно надёжный способ предотвратить проблемы с данными. Каскадные операции - да, опасная штука сомнительной применимости для большинства задач.