Розв'язання проблеми виклику тригера на видалення у двох пов'язаних таблицях

Розв’язання проблеми виклику тригера на видалення у двох пов’язаних таблицях

6 Березня 2024 в 19:31 23

При роботі з базами даних, особливо в складних системах, часто виникає потреба в автоматизації певних операцій. Однією з таких операцій є видалення даних з двох пов’язаних таблиць за допомогою тригерів.

Для початку, розглянемо ситуацію, коли у нас є дві таблиці:

таблиця_а

таблиця_а

та

таблиця_b

таблиця_b

. Припустимо, що у таблиці

таблиця_a

таблиця_a

є зовнішній ключ, який посилається на

table_b_id

1	table_b_id

, щоб гарантувати, що запис існує в таблиці

таблиця_b

таблиця_b

до того, як він буде доданий до

таблиці_a

таблиці_a

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

таблиці_a

таблиці_a

або

таблиці_b

таблиці_b

, асоційований запис був видалений у іншій таблиці.

Можна створити тригер після видалення для

таблиці_a

таблиці_a

, який буде видаляти асоційований запис у

таблиці_b

таблиці_b

без проблем. ОДНАК, якщо спробувати зробити тригер до або після видалення для

таблиці_b

таблиці_b

, це призведе до помилки.

Як вирішити цю проблему? Давайте розглянемо це більш детально.

Уявімо, що ми створили два тригери для реалізації вищезгаданої логіки:

Тригер до видалення на A:
CREATE OR REPLACE FUNCTION trgfunc_delete_b_before_a()
RETURNS trigger
LANGUAGE 'plpgsql'
COST 100
VOLATILE NOT LEAKPROOF
AS $BODY$
DECLARE _b_id_to_delete integer;
BEGIN
    _b_id_to_delete = OLD.b_id;
    DELETE FROM b WHERE id = _b_id_to_delete;
    RETURN OLD;
END;
$BODY$;
CREATE TRIGGER trg_delete_b_before_a
BEFORE DELETE
ON a
FOR EACH ROW
EXECUTE PROCEDURE trgfunc_delete_b_before_a();

Тригер до видалення на A:

CREATE OR REPLACE FUNCTION trgfunc_delete_b_before_a()

RETURNS trigger

LANGUAGE 'plpgsql'

COST 100

VOLATILE NOT LEAKPROOF

AS $BODY$

DECLARE _b_id_to_delete integer;

BEGIN

_b_id_to_delete = OLD.b_id;

DELETE FROM b WHERE id = _b_id_to_delete;

RETURN OLD;

END;

$BODY$;

CREATE TRIGGER trg_delete_b_before_a

BEFORE DELETE

ON a

FOR EACH ROW

EXECUTE PROCEDURE trgfunc_delete_b_before_a();

Тригер після видалення на B:
CREATE OR REPLACE FUNCTION trgfunc_delete_a_after_b()
RETURNS trigger
LANGUAGE 'plpgsql'
COST 100
VOLATILE NOT LEAKPROOF
AS $BODY$
DECLARE _b_id_to_delete integer;
BEGIN
    _b_id_to_delete = OLD.id;
    DELETE FROM a WHERE b_id = _b_id_to_delete;
    RETURN NULL;
END;
$BODY$;
CREATE TRIGGER trg_delete_a_after_b
AFTER DELETE
ON b
FOR EACH ROW
EXECUTE PROCEDURE trgfunc_delete_a_after_b();

Тригер після видалення на B:

CREATE OR REPLACE FUNCTION trgfunc_delete_a_after_b()

RETURNS trigger

LANGUAGE 'plpgsql'

COST 100

VOLATILE NOT LEAKPROOF

AS $BODY$

DECLARE _b_id_to_delete integer;

BEGIN

_b_id_to_delete = OLD.id;

DELETE FROM a WHERE b_id = _b_id_to_delete;

RETURN NULL;

END;

$BODY$;

CREATE TRIGGER trg_delete_a_after_b

AFTER DELETE

ON b

FOR EACH ROW

EXECUTE PROCEDURE trgfunc_delete_a_after_b();

Проблема виникає тут. Якщо ми спробуємо видалити запис з

таблиці_b

таблиці_b

, тригер спрацює, але коли спробуємо видалити запис з

таблиці_a

таблиці_a

, виникає помилка, оскільки запис вже був змінений операцією, спричиненою поточною командою. Це пов’язано з тим, що тригер виконується перед операцією видалення в

таблиці_b

таблиці_b

, тож зміни вже відбулися.

Як розв’язати цю проблему? Одним з варіантів є створення тригера до видалення на

таблиці_b

таблиці_b

, який видалятиме асоційовані записи в

таблиці_a

таблиці_a

. Але це також може призвести до проблем, оскільки видалення з

таблиці_a

таблиці_a

викличе тригер для

таблиці_b

таблиці_b

, який знову спробує видалити записи в

таблиці_a

таблиці_a

, створюючи циклічну ситуацію.

Один із способів вирішення цієї проблеми – використання визначення DISABLE TRIGGER для тимчасового вимкнення тригерів під час виконання операції видалення. Після видалення запису можна знову увімкнути тригери. Також можна переглянути логіку тригерів і спробувати уникнути циклічних ситуацій, змінюючи послідовність видалення чи логіку тригерів.

У будь-якому випадку, робота з тригерами та обробка взаємопов’язаних даних в базах даних вимагає уважності та ретельного планування, щоб уникнути непередбачених проблем і збоїв у системі.