Процедура каскадного удаления

Назначение

Процедура предназначена для реализация функциональности каскадного удаления с учетом того, что

• SQL-Server не умеет каскадно удалять для случаев "многие ко многим"
• SQL-Server не умеет удалять, если записи помечаются на удаление

Развертывание

1. Добавьте все требуемые вторичные ключи. Обычно с типом "запрет удаления"
2. Пропишите в таблицу tls все вторичные ключи. Ключ может быть составным
   1. key_name - имя вторичного ключа
   2. opType - тип обработки (1 - каскадное удаление, 2 - запрет удаления)
   3. msg - сообщение об ошибке на человеко-читаемом языке
3. Настройте функцию retUniqueKey так, чтобы по названию таблицы она возвращала идентифицирующее поле. Процедура работает в случае использования составных ключей. Однако в таблице все равно должно быть поле (не обязательно ключевое), которое однозначно идентифицирует запись. Какой-нибудь rowguid.

Примечание: Если составные ключи не используются, то обычно уникальное поле - это primary key. Соответственно тело функции будет выглядеть следующим образом:

Transact-SQL:

ALTER FUNCTION [dbo].[retUniqueKey] 
(
	@table_name varchar(50)
)
RETURNS varchar(50)
BEGIN
 	declare @result varchar(max)
	set @result = 
		(select column_name
		from INFORMATION_SCHEMA.TABLE_CONSTRAINTS
			i1 inner join INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 
			ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME 
		where CONSTRAINT_TYPE = 'PRIMARY KEY'
			and i2.TABLE_NAME = @table_name)
	RETURN @result
END

PostgreSQL:

CREATE OR REPLACE FUNCTION retuniquekey(IN inputdata text, OUT val character varying)
  RETURNS character varying AS
$BODY$

BEGIN
val = (select column_name from information_schema.key_column_usage where constraint_name = 
	(select constraint_name from information_schema.table_constraints where constraint_type = 'PRIMARY KEY' and table_name = inputdata));
END$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION retuniquekey(text)
  OWNER TO postgres;

Иногда во всех таблицах это поле называется одинаково. Иногда требуется создать дополнительную таблицу, определяющую уникальные поля для всех таблиц БД.

Использование

Transact-SQL

declare @mes varchar(255)
exec deleteCascade '<rec id="31"/><rec id="32"/>', 't3', 1, @mes out
select @mes

Параметры:

1. @ids_todelete - список кодов на удаление. Пример:

   '<rec id="41"/><rec id="42"/>'
   

2. @table_name - имя таблицы
3. @force_delete - 1, если записи требуется физически удалить несмотря ни на что. 0 - если требуется учесть условия в таблице с индексами. Обратите внимание, что в базовом виде процедура не удаляет записи, а помечает их на удаление.
4. @err_mes out - сообщение об ошибке на человекочитаемом языке.

PostgreSQL

select * from public.deletecascade('<root><rec id="4b21a6b8-2a6b-11e3-b304-b756ac6db454"/></root>'::xml, 'book_names'::varchar, 1::bit);

Параметры:

1. ids_todelete - список кодов на удаление. Пример:

   <root><rec id="4b21a6b8-2a6b-11e3-b304-b756ac6db454"/></root>
   

   Список кодов обязательно должен быть обёрнут в произвольный корневой тэг, иначе PostgreSQL откажется корректно его распознавать.

2. table_name - имя таблицы
3. force_delete - 1, если записи требуется физически удалить несмотря ни на что. 0 - если требуется учесть условия в таблице с индексами. Обратите внимание, что в базовом виде процедура не удаляет записи, а помечает их на удаление.

Заметьте, что для параметров необходимо прописывать явное преобразование типов.

Файлы

Файл:Cascade scripts mssql.zip

Файл:Cascade scripts postgres.zip {{#allow-groups:user}}