Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Журналирование null полей в xml при помощи триггера  [new]
tunknown
Member

Откуда:
Сообщений: 760
Привожу пример журналирования триггером. На форуме такие есть, но не все- подходящие.
Для получения универсальности изменённые данные разложил не по полям, а поместил в xml.
Оказалось, что получить правильный xml, который хранит только изменения- непросто.
Сложность в том, что в xml отсутствие атрибута/элемента можно расценивать как null.
Если это изменение с null на не null, то сохраняя предыдущее значение поля это можно показать так:
<d xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <r>
    <f2 xsi:nil="true" />
  </r>
</d>

Сложность заключается в том, что триггер заранее не знает об изменении поля f2 и это проверяется в процессе выполнения.
Но запрос с for xml explicit захардкоден и директива elementxsinil не подходит.
Решение в том, чтобы вручную решать для значения, будет ли оно заполненным элементом или "пустым", но с атрибутом xsi:nil="true".
Формально, название полей разное и explicit xml генератор это пропускает.
Возможно, это хак и его могут прикрыть.

Пример писался под sql генератор, но руки не дошли доделать.
Концепция не применялась на производственных серверах и сделана только для демонстрации подхода. Используется MSSQL 2008 R2.
+
use	tempdb
if	object_id ( 'dbo.Test',	'u' )	is	not	null
	drop	table	dbo.Test
create	table	dbo.Test
(	f1	int		primary	key
	,f2	varchar ( 256 )	null
	,f3	datetime	null
	,q	rowversion )
----------
if	object_id ( 'dbo.History',	'u' )	is	not	null
	drop	table	dbo.History
create	table	dbo.History	-- универсальное логирование истории, следить за возможным пересечением названий таблиц
(	DB	int		not	null	-- база данных
	,Object	int		not	null	-- sysobjects.id логирумой таблицы или триггера
	,Moment	datetime	not	null	-- момент изменения данных
	,Login	sysname		not	null	-- автор изменения
	,Data	xml		not	null	-- предыдущие значения полей для логируемой операции
,constraint	CKdboHistory	check	( db_name ( DB )	is	not	null ) )	-- rowversion for deleted only?
create	clustered	index	IXdboHistory	on	dbo.History	( Moment )
go
if	object_id ( 'dbo.TestAfter',	'tr' )	is	not	null
	drop	trigger	dbo.TestAfter
go
create	trigger	dbo.TestAfter	on	dbo.Test
after	update,	delete
/*
(c) 2017 TUnknown
License:
public domain as executing code, cc0 as citation
*/
as
--сохраняем только исторические изменения
--в историю не попадают update ... set f2=f2, т.к. это не аудит
--MERGE внутри триггера поддерживать не нужно, будет несколько вызовов триггера
--следить за длиной названия полей при добавке уникализирующего суффикса
--выбирать все PK поля, при их отсутствии- поля из unique clustered или unique с наименьшим числом полей
--выбирать rowversion поле для сохранения последовательности операций insert/update/delete, если они произошли в течение 3 миллисекунд и datetime не даёт порядок следования
--сохранение изменений значений PK полей поддерживается только для случая update ... set f1=f1+N, при несоблюдении последовательности история может быть неверной
set	nocount	on
----------
declare	@x	xml
----------
;with	cte	as
(	select
		d.f1
		,d.f2
		,d.f3
		,d.q
		,f1AAB74C7FFB7D4F0FB606CCF9F5293F5F=	i.f1
		,f2AAB74C7FFB7D4F0FB606CCF9F5293F5F=	i.f2
		,f3AAB74C7FFB7D4F0FB606CCF9F5293F5F=	i.f3
		,AAB74C7FFB7D4F0FB606CCF9F5293F5F=	convert ( tinyint,	case
											when	i.f1	is	null	then	1	-- удаление
											else					0
										end )
	from
		deleted	d
		left	join	inserted	i	on
			i.f1=	d.f1
	where
			not	update ( f1 )			-- список полей в PK/unique; при update PK join по нему не сработает
	union	all
	select
		d.f1
		,d.f2
		,d.f3
		,d.q
		,f1AAB74C7FFB7D4F0FB606CCF9F5293F5F=	i.f1
		,f2AAB74C7FFB7D4F0FB606CCF9F5293F5F=	i.f2
		,f3AAB74C7FFB7D4F0FB606CCF9F5293F5F=	i.f3
		,AAB74C7FFB7D4F0FB606CCF9F5293F5F=	convert ( tinyint,	case
											when	i.f1	is	null	then	1	-- удаление
											else					0
										end )
	from
		( select
			*
			,AAB74C7FFB7D4F0FB606CCF9F5293F5F=	row_number()	over	( order	by	f1 )	-- если update PK не сохраняет последовательность записей, то join получится не с теми записями, поэтому, помечаем как 'p', что говорит о возможной неточности логирования операции
		from
			deleted )	d
		left	join	( select
					*
					,AAB74C7FFB7D4F0FB606CCF9F5293F5F=	row_number()	over	( order	by	f1 )
				from
					inserted )	i	on
			i.AAB74C7FFB7D4F0FB606CCF9F5293F5F=	d.AAB74C7FFB7D4F0FB606CCF9F5293F5F	-- при update PK join по нему не сработает
	where
			update ( f1 )	)								-- перечисление всех PK полей через OR
select	@x=
	(select
		Tag
		,Parent
		,[d!1!xmlns:xsi]
		,[d!1!host]
		,[d!1!program]
		,[r!2!f1]
		,[r!2!q]
		,[r!2!!hide]

		,[f2!3!!element]		-- /хак- одинаковое имя элемента и атрибута, используется только одно, другое игнорируется
		,[f2!3!xsi:nil]			-- \

		,[f3!4!!element]
		,[f3!4!xsi:nil]
	from
		( select	distinct
			Tag=			1
			,Parent=		null
			,[d!1!xmlns:xsi]=	'http://www.w3.org/2001/XMLSchema-instance'	-- хак необходим из-за невозможности применения elementsxsinil, для поддержки xsi:nil
			,[d!1!host]=		host_name()
			,[d!1!program]=		program_name()
			,[r!2!f1]=		convert ( int,			null )
			,[r!2!q]=		convert ( binary ( 8 ),		null )
			,[r!2!!hide]=		convert ( smallint,		null )

			,[f2!3!!element]=	convert ( varchar ( 256 ),	null )
			,[f2!3!xsi:nil]=	convert ( varchar ( 4 ),	null )

			,[f3!4!!element]=	convert ( datetime,		null )
			,[f3!4!xsi:nil]=	convert ( varchar ( 4 ),	null )
		from
			cte
		where
				(	f1<>	f1AAB74C7FFB7D4F0FB606CCF9F5293F5F
				or	f1	is	not	null	and	f1AAB74C7FFB7D4F0FB606CCF9F5293F5F	is		null
				or	f1	is		null	and	f1AAB74C7FFB7D4F0FB606CCF9F5293F5F	is	not	null )
			or	(	f2<>	f2AAB74C7FFB7D4F0FB606CCF9F5293F5F
				or	f2	is	not	null	and	f2AAB74C7FFB7D4F0FB606CCF9F5293F5F	is		null
				or	f2	is		null	and	f2AAB74C7FFB7D4F0FB606CCF9F5293F5F	is	not	null )
			or	(	f3<>	f3AAB74C7FFB7D4F0FB606CCF9F5293F5F
				or	f3	is	not	null	and	f3AAB74C7FFB7D4F0FB606CCF9F5293F5F	is		null
				or	f3	is		null	and	f3AAB74C7FFB7D4F0FB606CCF9F5293F5F	is	not	null )
			or	AAB74C7FFB7D4F0FB606CCF9F5293F5F=	1
		union	all
 		select
			Tag=			2
			,Parent=		1
			,[d!1!xmlns:xsi]=	null
			,[d!1!host]=		null
			,[d!1!program]=		null
			,[r!2!f1]=		f1
			,[r!2!q]=		q
			,[r!2!!hide]=		1

			,[f2!3!!element]=	null
			,[f2!3!xsi:nil]=	null

			,[f3!4!!element]=	null
			,[f3!4!xsi:nil]=	null
		from
			cte
		where
				(	f1<>	f1AAB74C7FFB7D4F0FB606CCF9F5293F5F
				or	f1	is	not	null	and	f1AAB74C7FFB7D4F0FB606CCF9F5293F5F	is		null
				or	f1	is		null	and	f1AAB74C7FFB7D4F0FB606CCF9F5293F5F	is	not	null )
			or	(	f2<>	f2AAB74C7FFB7D4F0FB606CCF9F5293F5F
				or	f2	is	not	null	and	f2AAB74C7FFB7D4F0FB606CCF9F5293F5F	is		null
				or	f2	is		null	and	f2AAB74C7FFB7D4F0FB606CCF9F5293F5F	is	not	null )
			or	(	f3<>	f3AAB74C7FFB7D4F0FB606CCF9F5293F5F
				or	f3	is	not	null	and	f3AAB74C7FFB7D4F0FB606CCF9F5293F5F	is		null
				or	f3	is		null	and	f3AAB74C7FFB7D4F0FB606CCF9F5293F5F	is	not	null )
			or	AAB74C7FFB7D4F0FB606CCF9F5293F5F=	1
		group	by
			f1
			,q
		union	all
		select
			Tag=			3
			,Parent=		2
			,[d!1!xmlns:xsi]=	null
			,[d!1!host]=		null
			,[d!1!program]=		null
			,[r!2!f1]=		f1
			,[r!2!q]=		null
			,[f0!2!!hide]=		2

			,[f2!3!!element]=	case
							when	f2=	f2AAB74C7FFB7D4F0FB606CCF9F5293F5F	then	null
							else								f2
						end
			,[f2!3!xsi:nil]=	case
							when	f2=	f2AAB74C7FFB7D4F0FB606CCF9F5293F5F	then	null
							when	f2	is	null	and	f2AAB74C7FFB7D4F0FB606CCF9F5293F5F	is	null	then	null
							when	f2	is	null				then	'true'
							else								null
						end

			,[f3!4!!element]=	null
			,[f3!4!xsi:nil]=	null
		from
			cte
		where
				(	UPDATE ( f2 )
				or	AAB74C7FFB7D4F0FB606CCF9F5293F5F=	1
			and	(	f2<>	f2AAB74C7FFB7D4F0FB606CCF9F5293F5F )
				or	f2	is	not	null	and	f2AAB74C7FFB7D4F0FB606CCF9F5293F5F	is		null
				or	f2	is		null	and	f2AAB74C7FFB7D4F0FB606CCF9F5293F5F	is	not	null )
		union	all
		select
			Tag=			4
			,Parent=		2
			,[d!1!xmlns:xsi]=	null
			,[d!1!host]=		null
			,[d!1!program]=		null
			,[r!2!f1]=		f1
			,[r!2!q]=		null
			,[f0!2!!hide]=		3

			,[f2!3!!element]=	null
			,[f2!3!xsi:nil]=	null

			,[f3!4!!element]=	case
							when	f3=	f3AAB74C7FFB7D4F0FB606CCF9F5293F5F	then	null
							else								f3
						end
			,[f3!4!xsi:nil]=	case
							when	f3=	f3AAB74C7FFB7D4F0FB606CCF9F5293F5F	then	null
							when	f3	is	null	and	f3AAB74C7FFB7D4F0FB606CCF9F5293F5F	is	null	then	null
							when	f3	is	null				then	'true'
							else								null
						end
		from
			cte
		where
				(	UPDATE ( f3 )
				or	AAB74C7FFB7D4F0FB606CCF9F5293F5F=	1 )
			and	(	f3<>	f3AAB74C7FFB7D4F0FB606CCF9F5293F5F
				or	f3	is	not	null	and	f3AAB74C7FFB7D4F0FB606CCF9F5293F5F	is		null
				or	f3	is		null	and	f3AAB74C7FFB7D4F0FB606CCF9F5293F5F	is	not	null ) )	t
	order	by
		[r!2!f1]
		,[r!2!!hide]
	for
		xml	explicit )
----------
if	@x	is	not	null
	insert
		dbo.History	( DB,	Object,	Moment,	Login,	Data )
	select
		db_id()
		,@@procid	--select	id	from	sysobjects	where	parent_obj=	@@procid	-- для другой базы нужен динамический sql
		,getdate()
		,SYSTEM_USER
		,@x
go


для теста
use	tempdb
set	nocount	on
begin	tran

insert	dbo.Test	(f1,	f2,	f3 )
select	1,	'a',	'19000101'
union	all
select	2,	'b',	'19000102'
----------
update	dbo.Test	set	f2=	null	where	f1=	1
----------
select	'update1',	*	from	dbo.History
----------
delete	dbo.Test	where	f1=	2
----------
select	'delete',	*	from	dbo.History
----------
update	dbo.Test
set	f2=	1
	,f3=	f3+	1
----------
select	'update2',	*	from	dbo.History
----------
update	dbo.Test	set	f1=	0
----------
select	'update3',	*	from	dbo.History

rollback


Прошу предложить другие варианты получения null в xml.

PS подобный метод уже есть. Нашёл его уже после написания своего.

Сообщение было отредактировано: 15 дек 19, 23:12
14 апр 17, 22:04    [20403610]     Ответить | Цитировать Сообщить модератору
 Re: Журналирование null полей в xml при помощи триггера  [new]
felix_ff
Member

Откуда: Moscow
Сообщений: 1364
tunknown,

имхо так было бы немного красивее, но это таки опять не оптимальное решение для любой таблицы не накрутишь - надо писать динамику учета PK


+ code

ALTER TRIGGER [TestAfter] ON [dbo].[Test] AFTER INSERT, UPDATE, DELETE
AS
SET NOCOUNT ON
DECLARE @x XML

IF EXISTS (SELECT 1 FROM inserted) AND EXISTS (SELECT 1 FROM deleted) AND NOT UPDATE(f1) --инструкция UPDATE (NOT PRIMARY KEY)
    SET @x = (SELECT
                    'http://www.w3.org/2001/XMLSchema-instance11' AS [xmlns:xsi],
                    HOST_NAME() AS [hostname],
                    APP_NAME() AS [program],
                    (SELECT ISNULL(CONVERT(VARCHAR, d.f1), 'NULL') as [old_f1],
					        ISNULL(CONVERT(VARCHAR, i.f1), 'NULL') as [new_f1],
                            d.[q] as [old_q],
					        iif(d.[q] = i.[q], null, i.[q]) as [new_q],
					       (SELECT ISNULL(d.f2, 'NULL') as [old_f2],
						           ISNULL(i.[f2], 'NULL') as [new_f2],
					               ISNULL(d.[f3], 'NULL') as [old_f3],
					               ISNULL(i.[f3], 'NULL') as [new_f3]
					        FOR XML RAW('values'), TYPE)
                     FOR XML RAW('key'), type)
	          FROM inserted i
			      LEFT JOIN deleted d ON d.[f1] = i.[f1]
              FOR XML RAW ('UPDATE'), TYPE, BINARY BASE64)
ELSE IF EXISTS (SELECT 1 FROM inserted) AND EXISTS (SELECT 1 FROM deleted) AND UPDATE(f1) --инструкция UPDATE (PRIMARY KEY)
    SET @x = (SELECT 'Тут мне лень писать' FOR XML PATH('PRIMARY_KEY_HANDLER'), TYPE)
ELSE IF EXISTS (SELECT 1 FROM inserted) --инструкция INSERT
    SET @x = (SELECT * FROM inserted FOR XML RAW('INSERT'), TYPE)
ELSE IF EXISTS (SELECT 1 FROM deleted) --инструкция DELETE
    SET @x = (SELECT * FROM deleted FOR XML RAW('DELETE'), TYPE)
ELSE BEGIN --инструкция не внесшая изменений в таблицу
    DECLARE @ColCount INT, @BitCount INT, @BitsUpdated BIGINT
	IF COLUMNS_UPDATED() = 0x
	    SET @x = (SELECT 'DELETE' AS [@Type], 'No rows affected' AS [@Message] FOR XML PATH('OPERATION'), TYPE)
    ELSE BEGIN
	    SELECT @ColCount = COUNT(1) FROM sys.columns WHERE [object_id] = OBJECT_ID('[dbo].[Test]', 'U')
	    SET @BitCount = 0
	    SET @BitsUpdated = CONVERT(BIGINT, COLUMNS_UPDATED())
	    WHILE @BitsUpdated > 0 BEGIN
	         SET @BitsUpdated = @BitsUpdated & (@BitsUpdated - 1)
		     SET @BitCount += 1
	    END
        SET @x = (SELECT IIF(@BitCount = @ColCount, 'INSERT', 'UPDATE') AS [@Type], 'No rows affected' AS [@Message] FOR XML PATH('OPERATION'), TYPE)
    END
END

INSERT INTO [dbo].[History] ([DB], [Object], [Moment], [Login], [Data])
  VALUES (DB_ID(), @@PROCID, GETDATE(), SUSER_NAME(), ISNULL(@x, CAST('<None/>' AS XML)))




вообще система отслеживания изменений не требует изобретений велосипедов, есть вполне неплохая CT а если нужна историчность то CDC

P.s ну и на последок: ловить в триггере @@PROCID это как воду из кувшина в реку лить
15 апр 17, 02:51    [20404015]     Ответить | Цитировать Сообщить модератору
 Re: Журналирование null полей в xml при помощи триггера  [new]
tunknown
Member

Откуда:
Сообщений: 760
felix_ff
вообще система отслеживания изменений не требует изобретений велосипедов, есть вполне неплохая CT а если нужна историчность то CDC
Некоторые используют 2005 express, в нём не все расширения есть.

felix_ff
P.s ну и на последок: ловить в триггере @@PROCID это как воду из кувшина в реку лить
Судя по документации @@PROCID работает в триггере. А что не так?
15 апр 17, 17:55    [20404696]     Ответить | Цитировать Сообщить модератору
 Re: Журналирование null полей в xml при помощи триггера  [new]
felix_ff
Member

Откуда: Moscow
Сообщений: 1364
tunknown,

@@PROCID несомненно работает, но будет возвращать ID триггера который вызвал вставку в таблицу.

Не особо полезная информация в плане понимания какой бизнес послужил инициатором вызова триггера.
это может быть как и Ad-Hoc запрос так и хранимка.

Хотя если вы хотели отлавливать именно сами триггера - ну это совсем другое дело :)
15 апр 17, 21:27    [20404963]     Ответить | Цитировать Сообщить модератору
 Re: Журналирование null полей в xml при помощи триггера  [new]
tunknown
Member

Откуда:
Сообщений: 760
felix_ff
Хотя если вы хотели отлавливать именно сами триггера - ну это совсем другое дело :)
Я хотел получить название таблицы, но этого сделать нельзя без добавления динамики и перекомпиляции, т.к. предполагается, что таблица может быть использована из разных баз.

felix_ff
ISNULL(d.[f3], 'NULL')
Предполагаю, что такой вариант не сработает из-за попытки приведения к единому типу, т.е. datetime. Или придётся писать собственный конвертер всех типов в строковые значения xml.
16 апр 17, 12:21    [20405501]     Ответить | Цитировать Сообщить модератору
 Re: Журналирование null полей в xml при помощи триггера  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7759
tunknown,
автор
в историю не попадают update ... set f2=f2, т.к. это не аудит

путаете способ хранения и отображения, т.е. структуру и представление. Отсюда возникла потребность в сложном преобразовании при сохранении. Создали проблему, с которой сами же и боретесь :)
17 апр 17, 11:06    [20406962]     Ответить | Цитировать Сообщить модератору
Между сообщениями интервал более 1 года.
 Re: Журналирование null полей в xml при помощи триггера  [new]
tunknown
Member

Откуда:
Сообщений: 760
Ранее я упоминал генератор "универсального" триггера логгирования. Теперь привожу его код. Требуется sql 2005+, других зависимостей не предполагается. Процедура генератора может быть создана и в master. Поддерживаются таблицы в любой базе локального сервера, на которую есть права доступа на создание триггера. Логгирование только частичное, т.к. не поддерживаются BLOB поля и откат транзакции влияет на результат. Побочный эффект- переназначает на себя first/last trigger. Если для таблицы лога создать отдельную базу на быстром диске, то сильного падения производительности не наблюдается. Проверен на таблицах с 200+ полями.
+
use	AAB74C7FFB7D4F0FB606CCF9F5293F5F	-- чтобы не забыть сменить БД
go
if	object_id ( 'dbo.Entriggerate' , 'p' )	is	null
	exec	( 'create	proc	dbo.Entriggerate	as	return' )
go
alter	proc	dbo.Entriggerate
	@sTableData	nvarchar ( 384 )	-- шаблон в формате database.schema.table с поддержкой like, для обработки всех таблиц задать %
	,@sTableLog	nvarchar ( 384 )	-- null/''=отключить триггер; имя таблицы лога в формате database.schema.table. Если база не указана, то в текущей базе
--(c) 2017-2019 TUnknown License: public domain/cc0
as
--сохраняем только исторические изменения
--в историю не попадают update ... set f2=f2, т.к. это не аудит
--в историю не попадают вычисляемые колонки
--MERGE внутри триггера поддерживать не нужно, будет несколько вызовов триггера
--следить за длиной названия полей при добавке уникализирующего суффикса
--выбирать все PK поля, при их отсутствии- поля из unique clustered или unique с наименьшим числом полей
--выбирать rowversion поле для сохранения последовательности операций insert/update/delete, если они произошли в течение 3 миллисекунд и datetime не даёт порядок следования
--сохранение изменений значений PK полей поддерживается только для случая update ... set f1=f1+N, при несоблюдении последовательности история может быть неверной
--после изменения полей таблицы триггер нужно пересоздавать
--errata: таблицы с unique, но без PK не поддерживаются
set	nocount	on
----------
declare	@bDebug			bit
	,@iRowCount		integer
	,@sTableName		sysname
	,@sTableSchema		sysname
	,@sTriggerName		nvarchar ( 512 )
	,@sTriggerName0		nvarchar ( 512 )
	,@sSign			varchar ( 32 )

	,@sExecSQLLog		nvarchar ( 256 )
	,@sExecSQLData		nvarchar ( 256 )
	,@sExecTriggerOrder	nvarchar ( 256 )

	,@sExec			nvarchar ( max )
	,@sExec01		nvarchar ( max )
	,@sExec02		nvarchar ( max )
	,@sExec03		nvarchar ( max )
	,@sExec04		nvarchar ( max )
	,@sExec05		nvarchar ( max )
	,@sExec06		nvarchar ( max )
	,@sExec07		nvarchar ( max )
	,@sExec08		nvarchar ( max )
	,@sExec09		nvarchar ( max )
	,@sExec10		nvarchar ( max )
	,@sExec11		nvarchar ( max )
	,@sExec12		nvarchar ( max )

	,@bFirst		bit
	,@iObjectId		integer
	,@sDBData		sysname
	,@sDBLog		sysname
	,@sSchemaData		sysname
	,@sSchemaLog		sysname
----------
create	table	#ShowColumnDataTypesData
(	ObjectId		int
	,Sequence		smallint
	,IsFirstLast		bit
	,ObjectName		sysname
	,SchemaName		sysname
	,ColumnName		sysname
	,ColumnNameQuoted	sysname
	,DataType		varchar ( 32 )
	,IsNullable		bit
	,IsPrimaryKey		bit

	,ColumnNameSigned	sysname	)
create	unique	clustered	index	IX001	on	#ShowColumnDataTypesData	( ObjectId,	Sequence )	-- без spool
----------
create	table	#ShowColumnDataTypesLog
(	ObjectId		int
	,Sequence		smallint
	,IsFirstLast		bit
	,ObjectName		sysname
	,SchemaName		sysname
	,ColumnName		sysname
	,ColumnNameQuoted	sysname
	,DataType		varchar ( 32 )
	,IsNullable		bit
	,IsPrimaryKey		bit

	,ColumnNameSigned	sysname	)
----------
select	@sSign=			'AAB74C7FFB7D4F0FB606CCF9F5293F5F'	-- предполагаем наличие сигнатуры в первых 4000 символах, чтобы она не попала на стык двух записей syscomments
	,@bDebug=		1
	,@sDBData=		isnull ( parsename ( @sTableData,	3 ),	db_name() )
	,@sDBLog=		isnull ( parsename ( @sTableLog,	3 ),	db_name() )
	,@sSchemaData=		isnull ( parsename ( @sTableData,	2 ),	'dbo'/*schema_name()*/ )
	,@sSchemaLog=		isnull ( parsename ( @sTableLog,	2 ),	'dbo'/*schema_name()*/ )
	,@sTableData=		parsename ( @sTableData,	1 )
	,@sTableLog=		parsename ( @sTableLog,		1 )

	,@sExecSQLLog=		@sDBLog+	'..sp_executesql'
	,@sExecSQLData=		@sDBData+	'..sp_executesql'
	,@sExecTriggerOrder=	@sDBData+	'..sp_settriggerorder'

	,@sExec01=		'
----------
insert
	#ShowColumnDataTypes/*0*/
select
	*
	,ColumnNameSigned=	'''+@sSign+	'_''+	ColumnName
from
	( select
		ObjectId=	o.Id,
		Sequence=	row_number()	over	( partition	by	o.Id	order	by	c.colid ),	--\гарантирует последовательность
	--не работает в случае только одного поля в таблице
		IsFirstLast=	case	
					when	row_number()	over	( partition	by	o.Id	order	by	c.colid )=	1	then	1
					when	row_number()	over	( partition	by	o.Id	order	by	c.colid	desc )=	1	then	0
				end,											-- else null
		ObjectName=	o.name,
		SchemaName=	schema_name ( o.uid ),
		ColumnName=	c.name,
		ColumnNameQuoted=	quotename ( c.name ),
		DataType=	convert ( nvarchar ( 256 ),	case
									when		t2.name	like	''%char''
										or	t2.name	like	''%binary''		then	t2.name
																+	'' ( ''
																+	case	c.prec
																		when	-1	then	''max''
																		else			convert ( varchar ( 256 ),	c.prec )
																	end+	'' )''
									when	t2.name	in	( ''numeric'',	''decimal'' )	then	t2.name
																+	'' ( ''
																+	convert ( varchar ( 256 ),	c.prec )
																+	'' , ''
																+	convert ( varchar ( 256 ),	c.scale )
																+	'' )''
									else								isnull ( t2.name,	t1.name )
								end ),
		IsNullable=	c.isnullable,
		IsPrimaryKey=	convert ( tinyint,	case	c.name		-- тип bit нежелательно использовать в агрегатах?
								when	INDEX_COL ( schema_name ( o.uid )+	''.''+	o.name,	ik.indid,	ik.keyno )	then	1
								else													0
							end )
	from
		sysobjects	o						-- через type_name ( typeproperty ( name , ''systemtype'' ) ) медленнее
		inner	join	syscolumns	c	on
			c.id=		o.id
		inner	join	systypes	t1	on			-- сработает ли inner для select Col_With_UserType into #temp?
			t1.xusertype=	c.xusertype
		left	join	systypes	t2	on			-- left для поддержки hierarchyid чензу=240
			t2.xtype=	t1.xtype
		and	t2.xtype=	t2.xusertype
		left	join	( select
					so.parent_obj
					,i.id
					,i.indid
				from
					sysobjects	so
					,sysindexes	i
				where
						so.xtype=	''pk''		-- дополнительный join из-за определения, что это primary
					and	i.name=		so.name
					and	i.id=		so.parent_obj )	opk	on
			opk.parent_obj=	o.id
		left	join	sysindexkeys	ik	on
			ik.id=		opk.id
		and	ik.indid=	opk.indid
		and	ik.colid=	c.colid
	where
			OBJECTPROPERTY ( o.id , ''IsMSShipped'' )=	0
		and	o.xtype=	''u''
		and	t2.name	not	in	( ''text'',	''ntext'',	''image'' ) )	t	-- inserted/deleted не поддерживают типы данных
where
		/*1*/
order	by
	ObjectId			-- попытка сохранить недокументированную sumstr
	,Sequence'
----------
set	@sExec=	replace ( replace ( @sExec01,	'/*0*/',	'Data' ),	'/*1*/',	'ObjectName	like	'''+	@sTableData+	'''	and	SchemaName	like	'''+	@sSchemaData+	'''' )
if	@bDebug=	1	print	@sExec
exec	@sExecSQLData
		@stmt=	@sExec
----------
set	@sExec=	replace ( replace ( @sExec01,	'/*0*/',	'Log' ),	'/*1*/',	'ObjectName	in	( '''+	@sTableLog+	''',	'''+	@sTableLog+	@sSign+	''' )	and	SchemaName=	'''+	@sSchemaLog+	'''' )
if	@bDebug=	1	print	@sExec
exec	@sExecSQLLog
		@stmt=	@sExec
----------
select
	@iRowCount=	count ( * )	-- если число полей совпадает, то эта таблица подходит; пересчитывать в зависимости от полей таблицы лога
from
	#ShowColumnDataTypesLog
where
		ObjectName=	@sTableLog
	and	SchemaName=	@sSchemaLog
----------
if	@iRowCount	not	in	( 0,	7 )
begin
	raiserror ( 'Под именем для таблицы логгирования есть другая таблица, задайте другое',	18,	1 )
	return
end
----------
if		@iRowCount=	0
	and	isnull ( @sTableLog,	'' )<>	''
begin
	set	@sExec01=	'
create	table	'+	@sSchemaLog+	'.'+	@sTableLog+	'
(	Sequence	bigint	unique	clustered	identity ( 1,	1 )
	,Moment		datetime		not null	default	getdate()
	,Host		sysname			null		default	host_name()
	,Login		sysname			not null	default	SYSTEM_USER
	,Object		nvarchar ( 384 )	null		default	db_name()+	''.''+	schema_name ( OBJECTPROPERTY ( @@procid,	''OwnerId'' ) )+	''.''+	object_name ( @@procid )	-- не идентификаторы, т.к. базу могут стереть, а лог оставить
	,Application	sysname			null		default	program_name()
	,Data		xml			null	)'	-- null, чтобы не запрещать update таблиц при сбое триггера
----------
	if	@bDebug=	1	print	@sExec01
	exec	@sExecSQLLog
			@stmt=	@sExec01		-- можно сделать xml->FILESTREAM
end
----------
declare	c	cursor	local	fast_forward	for
	select
		ObjectId
		,SchemaName
		,ObjectName
	from
		#ShowColumnDataTypesData
	group	by
		ObjectId
		,SchemaName
		,ObjectName
----------
open	c
----------
while	1=	1
begin
	fetch	next	from	c	into	@iObjectId,	@sTableSchema,	@sTableName
	if	@@fetch_status<>	0	break
----------
	select	@sTriggerName=	@sTableSchema+	'.'+	@sTableName+	'_AfterUpdateDelete_'+	@sSign
		,@sTriggerName0=@sDBData+	'.'+	@sTriggerName
----------
	if	object_id ( @sTriggerName0,	'tr' )	is	not	null
	begin
		set	@sExec=	'drop	trigger	'+	@sTriggerName	--***лучше это делать уже после получения текста триггера, если не получится, то не удалять существующий
----------
		exec	@sExecSQLData
				@stmt=	@sExec
	end
----------
	if	isnull ( @sTableLog,	'' )=	''			-- после drop	trigger
		continue
----------
	set	@sExec=	'
create	trigger	'+	@sTriggerName+	'	on	'+	@sTableSchema+	'.'+	@sTableName+	'
after	update,	delete
as
set	nocount	on
----------
if	object_id ( '''+	@sDBLog+	'.'+	@sSchemaLog+	'.'+	@sTableLog+	''',	''u'' )	is	null
begin
	raiserror ( ''
####################################################################################################
Логгирование не действует, т.к. таблица '+	@sDBLog+	'.'+	@sSchemaLog+	'.'+	@sTableLog+	' не найдена
####################################################################################################'',	0,	0 )
	return		-- логгирование не должно нарушать работу
end
----------
declare	@x	xml
----------
;with	cte	as
(	select'
----------
	select	@bFirst=	1
		,@sExec01=	''
		,@sExec02=	''
		,@sExec11=	''
		,@sExec12=	''
----------
	select
		@sExec01=	@sExec01+	'
		'+	case	@bFirst
				when	1	then	''
				else			','
			end+	'd.'+	ColumnName
		,@sExec02=	@sExec02+	'
		,'+	ColumnNameSigned+	'=	i.'+	ColumnName
		,@bFirst=	0

		,@sExec11=	@sExec11+	'
			,'+	ColumnName
		,@sExec12=	@sExec12+	'
					,'+	ColumnName
	from
		#ShowColumnDataTypesData
	where
		ObjectId=	@iObjectId
	order	by
		Sequence
----------
	select	@sExec=		@sExec
			+	@sExec01
			+	@sExec02
----------
	select
		@sExec=		@sExec+	'
		,'+	@sSign+	'=	convert ( tinyint,	case
											when	i.'+	ColumnName+	'	is	null	then	1	-- удаление
											else					0
										end )'
	from
		#ShowColumnDataTypesData
	where
			ObjectId=	@iObjectId
		and	IsPrimaryKey=	1
	order	by
		Sequence
----------
	select	@sExec=		@sExec+	'
	from
		deleted	d
		left	join	inserted	i	on'
----------
	select
		@sExec=		@sExec+	'
			i.'+	ColumnName+	'=	d.'+	ColumnName+	'
	where
			not	update ( '+	ColumnName+	' )			-- список полей в PK/unique; при update PK join по нему не сработает'
	from
		#ShowColumnDataTypesData
	where
			ObjectId=	@iObjectId
		and	IsPrimaryKey=	1
	order	by
		Sequence
----------
	select	@sExec=		@sExec+	'
	union	all
	select'
		,@bFirst=	1
		,@sExec03=	''
		,@sExec04=	''
----------
	select
		@sExec03=	@sExec03+	'
		'+	case	@bFirst
				when	1	then	''
				else			','
			end+	'd.'+	ColumnName
		,@sExec04=	@sExec04+	'
		,'+	ColumnNameSigned+	'=	i.'+	ColumnName
		,@bFirst=	0
	from
		#ShowColumnDataTypesData
	where
		ObjectId=	@iObjectId
	order	by
		Sequence
----------
	select	@sExec=		@sExec
			+	@sExec03
			+	@sExec04
		,@sExec05=	''
		,@sExec06=	''
		,@sExec07=	''
		,@bFirst=	1
----------
	select
		@sExec05=	@sExec05+	'
		,'+	@sSign+	'=	convert ( tinyint,	case
											when	i.'+	ColumnName+	'	is	null	then	1	-- удаление
											else					0
										end )'
		,@sExec06=	@sExec06+	case	@bFirst
							when	0	then	','
							else			''
						end+	'	'+	ColumnName
		,@sExec07=	@sExec07+	'		'+	case	@bFirst
										when	0	then	'or'
										else			''
									end+	'	update ( '+	ColumnName+	' )'
		,@bFirst=	0
	from
		#ShowColumnDataTypesData
	where
			ObjectId=	@iObjectId
		and	IsPrimaryKey=	1
	order	by
		Sequence
----------
	select	@sExec=		@sExec
			+	@sExec05
			+	'
	from
		( select
			'+	@sSign+	'=	row_number()	over	( order	by'+	@sExec06+	' )	-- если update PK не сохраняет последовательность записей, то join получится не с теми записями, поэтому, помечаем как ''p'', что говорит о возможной неточности логирования операции'
			+	@sExec11+	'
		from
			deleted )	d
		left	join	( select
					'+	@sSign+	'=	row_number()	over	( order	by'+	@sExec06+	' )'
			+	@sExec12+	'
				from
					inserted )	i	on
			i.'+	@sSign+	'=	d.'+	@sSign+	'	-- при update PK join по нему не сработает
	where								-- перечисление всех PK полей через OR
'+	@sExec07	+	'	)
select	@x=
	(select
		Tag
		,Parent
		,[d!1!xmlns:xsi]
		,'
		,@sExec08=	''
----------
	select
		@sExec08=	@sExec08+		case	@sExec08
								when	''	then	''
								else			'
		,'
							end+	'[r!2!'+	ColumnName+	']'
	from
		#ShowColumnDataTypesData
	where
			ObjectId=	@iObjectId
		and	IsPrimaryKey=	1		-- ***здесь же обрабатывать и поля RowVersion
	order	by
		Sequence
----------
	select	@sExec=		@sExec+	@sExec08+	'
		,[r!2!!hide]'
----------
	select														-- хак- одинаковое имя элемента и атрибута, используется только одно, другое игнорируется
		@sExec=		@sExec
			+	'
		,['+	ColumnName+	'!'+	convert ( varchar ( 4 ),	Sequence+	2 )+	'!!element]'
			+	case	IsNullable
					when	1	then	'
		,['+	ColumnName+	'!'+	convert ( varchar ( 4 ),	Sequence+	2 )+	'!xsi:nil]'
					else			''
				end
	from
		#ShowColumnDataTypesData
	where
			ObjectId=	@iObjectId
		and	IsPrimaryKey=	0
	order	by
		Sequence
----------
	select	@sExec=		@sExec+	+	'
	from
		( select
			Tag=			1
			,Parent=		null
			,[d!1!xmlns:xsi]=	''http://www.w3.org/2001/XMLSchema-instance'''	-- хак необходим из-за невозможности применения elementsxsinil, для поддержки xsi:nil
----------
	select
		@sExec=		@sExec+	'
			,[r!2!'+	ColumnName+	']=		convert ( '+	DataType+	',	null )'
	from
		#ShowColumnDataTypesData
	where
			ObjectId=	@iObjectId
		and	IsPrimaryKey=	1		-- ***здесь же обрабатывать и поля RowVersion
	order	by
		Sequence
----------
	select	@sExec=		@sExec+	+	'
			,[r!2!!hide]=		convert ( smallint,		null )'		-- тип данных для 1024 полей
----------
	select														-- хак- одинаковое имя элемента и атрибута, используется только одно, другое игнорируется
		@sExec=		@sExec
			+	'
			,['+	ColumnName+	'!'+	convert ( varchar ( 4 ),	Sequence+	2 )+	'!!element]=		convert ( '+	DataType+	',	null )'
			+	case	IsNullable
					when	1	then	'
			,['+	ColumnName+	'!'+	convert ( varchar ( 4 ),	Sequence+	2 )+	'!xsi:nil]=	convert ( varchar ( 4 ),	null )'
					else			''
				end
	from
		#ShowColumnDataTypesData
	where
			ObjectId=	@iObjectId
		and	IsPrimaryKey=	0
	order	by
		Sequence
----------
	select	@sExec=		@sExec+	+	'
		union	all
		select
			Tag=			2
			,Parent=		1
			,[d!1!xmlns:xsi]=	null'
----------
	select
		@sExec=		@sExec+	'
			,[r!2!'+	ColumnName+	']=		'+	quotename ( ColumnName )
	from
		#ShowColumnDataTypesData
	where
			ObjectId=	@iObjectId
		and	IsPrimaryKey=	1		-- ***здесь же обрабатывать и поля RowVersion
	order	by
		Sequence
----------
	select	@sExec=		@sExec+	+	'
			,[r!2!!hide]=		1'
		,@sExec09=	''
----------
	select														-- хак- одинаковое имя элемента и атрибута, используется только одно, другое игнорируется
		@sExec09=	@sExec09
			+	'
			,['+	ColumnName+	'!'+	convert ( varchar ( 4 ),	Sequence+	2 )+	'!!element]=		null'
			+	case	IsNullable
					when	1	then	'
			,['+	ColumnName+	'!'+	convert ( varchar ( 4 ),	Sequence+	2 )+	'!xsi:nil]=	null'
					else			''
				end
	from
		#ShowColumnDataTypesData
	where
			ObjectId=	@iObjectId
		and	IsPrimaryKey=	0
	order	by
		Sequence
----------
	select	@sExec=		@sExec+	@sExec09+	'
		from
			cte
		where'
		,@sExec09=	'
				'+	@sSign+	'=	1'
----------
	select
		@sExec09=	@sExec09+	'
			or	(	'+	ColumnNameQuoted+	'<>	'+	quotename ( ColumnNameSigned )+	'
				or	'+	ColumnNameQuoted+	'	is	not	null	and	'+	quotename ( ColumnNameSigned )+	'	is		null
				or	'+	ColumnNameQuoted+	'	is		null	and	'+	quotename ( ColumnNameSigned )+	'	is	not	null )'

	from
		#ShowColumnDataTypesData
	where
		ObjectId=	@iObjectId
	order	by
		Sequence
----------
	select	@sExec=		@sExec+	@sExec09+	'
		group	by'
		,@bFirst=	1
----------
	select
		@sExec=		@sExec+	'
			'+	case	@bFirst
					when	0	then	','
					else			''
				end
			+	ColumnNameQuoted
		,@bFirst=	0
	from
		#ShowColumnDataTypesData
	where
			ObjectId=	@iObjectId
		and	IsPrimaryKey=	1		-- ***здесь же обрабатывать и поля RowVersion
	order	by
		Sequence
----------
	set	@sExec10=	''
----------
	select
		@sExec10=	@sExec10+	'
			,[r!2!'+	ColumnName+	']=		'+	ColumnNameQuoted
	from
		#ShowColumnDataTypesData
	where
			ObjectId=	@iObjectId
		and	IsPrimaryKey=	1
	order	by
		Sequence
----------
--***до и после обрабатываемого поля перечислить все предыдущие и последующие поля как=null

	select
		@sExec=	@sExec+			case
							when	c.IsFirstLast=	1	then	'
		union	all
		select
			Tag=			'+	convert ( varchar ( 4 ),	p.Sequence+	2 )+	'
			,Parent=		2
			,[d!1!xmlns:xsi]=	null'
					+	@sExec10
					+	'
			,[r!2!!hide]=		'+	convert ( varchar ( 4 ),	p.Sequence+	2 )
							else					''
						end
					+	case
							when		p.Sequence=	c.Sequence
								and	c.IsPrimaryKey=	0	then	'
			,['+	p.ColumnName+	'!'+	convert ( varchar ( 4 ),	p.Sequence+	2 )+	'!!element]=	case
							when	'+	p.ColumnNameQuoted+	'=	'+	quotename ( p.ColumnNameSigned )+	'	then	null
							else								'+	p.ColumnNameQuoted+	'
						end'+	case	c.IsNullable
								when	1	then	'
			,['+	p.ColumnName+	'!'+	convert ( varchar ( 4 ),	p.Sequence+	2 )+	'!xsi:nil]=	case
							when	'+	p.ColumnNameQuoted+	'=	'+	quotename ( p.ColumnNameSigned )+	'	then	null
							when	'+	p.ColumnNameQuoted+	'	is	null	and	'+	quotename ( p.ColumnNameSigned )+	'	is	null	then	null
							when	'+	p.ColumnNameQuoted+	'	is	null				then	''true''
							else								null
						end'
								else			''
							end
							when		p.Sequence<>	c.Sequence
								and	c.IsPrimaryKey=	0	then	'
			,['+	c.ColumnName+	'!'+	convert ( varchar ( 4 ),	c.Sequence+	2 )+	'!!element]=	null'+	case	c.IsNullable
																		when	1	then	'
			,['+	c.ColumnName+	'!'+	convert ( varchar ( 4 ),	c.Sequence+	2 )+	'!xsi:nil]=	null'
																		else			''
																	end
							else						''
						end
					+	case	c.IsFirstLast
							when	0	then	'
		from
			cte
		where
				(	UPDATE ( '+	p.ColumnNameQuoted+	' )
				or	'+	@sSign+	'=	1
			and	(	'+	p.ColumnNameQuoted+	'<>	'+	quotename ( p.ColumnNameSigned )+	' )
				or	'+	p.ColumnNameQuoted+	'	is	not	null	and	'+	quotename ( p.ColumnNameSigned )+	'	is		null
				or	'+	p.ColumnNameQuoted+	'	is		null	and	'+	quotename ( p.ColumnNameSigned )+	'	is	not	null )'
							else			''
						end
	from
		#ShowColumnDataTypesData	p				-- цикл по каждому полю
		,#ShowColumnDataTypesData	c
	where
			p.ObjectId=	@iObjectId
		and	p.IsPrimaryKey=	0
		and	c.ObjectId=	@iObjectId
	order	by
		p.Sequence
		,c.Sequence
----------
	set	@sExec=	@sExec+	' )	t
	order	by
		'+	@sExec08+	'
	for
		xml	explicit )
----------
if	@x	is	not	null
	insert	'+	@sDBLog+	'.'+	@sSchemaLog+	'.'+	@sTableLog+	'	( Data )
	select	@x'
----------
	if	@bDebug=	1
	begin
		print	( substring ( @sExec,	1,	4000 ) )
		print	( substring ( @sExec,	4001,	8000 ) )
		print	( substring ( @sExec,	8001,	12000 ) )
		print	( substring ( @sExec,	12001,	20000 ) )
		print	( substring ( @sExec,	16001,	20000 ) )
		print	( substring ( @sExec,	20001,	24000 ) )
		print	( substring ( @sExec,	24001,	28000 ) )
		print	( substring ( @sExec,	28001,	32000 ) )
		print	( substring ( @sExec,	32001,	36000 ) )
		print	( substring ( @sExec,	36001,	40000 ) )
		print	( substring ( @sExec,	40001,	44000 ) )
		print	( substring ( @sExec,	44001,	48000 ) )
		print	( substring ( @sExec,	48001,	52000 ) )
		print	( substring ( @sExec,	52001,	56000 ) )
		print	( substring ( @sExec,	56001,	60000 ) )
		print	( substring ( @sExec,	60001,	64000 ) )
		print	( substring ( @sExec,	64001,	68000 ) )
		print	( substring ( @sExec,	68001,	72000 ) )
	end
----------
	exec	@sExecSQLData
			@stmt=	@sExec
----------
	select	@sExec01=		@sDBData+	'.'+	@sTableSchema+	'.'+	@sTableName
		,@sTriggerName0=	null
		,@sExec=		'
	select
		@sTriggerName0=	schema_name ( uid )+	''.''+	name
	from
		sysobjects
	where
			xtype=		''tr''
		and	parent_obj=	object_id ( @sExec01,	''u'' )
		and	objectproperty ( id,	''ExecIsFirstUpdateTrigger'' )=	1'
----------
	exec	@sExecSQLData
			@stmt=			@sExec
			,@params=		N'@sTriggerName0	nvarchar ( 256 )	output,	@sExec01	nvarchar ( 384 )'
			,@sTriggerName0=	@sTriggerName0	output
			,@sExec01=		@sExec01
----------
	if	@sTriggerName0	is	not	null
	begin
		exec	@sExecTriggerOrder
				@triggername=	@sTriggerName0
				,@order=	N'None'
				,@stmttype=	N'update'
	end
----------
	exec	@sExecTriggerOrder
			@triggername=	@sTriggerName
			,@order=	N'First'
			,@stmttype=	N'update'
----------
	select	@sTriggerName0=	null
		,@sExec=		'
	select
		@sTriggerName0=	schema_name ( uid )+	''.''+	name
	from
		sysobjects
	where
			xtype=		''tr''
		and	parent_obj=	object_id ( @sExec01,	''u'' )
		and	objectproperty ( id,	''ExecIsLastDeleteTrigger'' )=	1'
----------
	exec	@sExecSQLData
			@stmt=			@sExec
			,@params=		N'@sTriggerName0	nvarchar ( 256 )	output,	@sExec01	nvarchar ( 384 )'
			,@sTriggerName0=	@sTriggerName0	output
			,@sExec01=		@sExec01
----------
	if	@sTriggerName0	is	not	null
	begin
		exec	@sExecTriggerOrder
				@triggername=	@sTriggerName0
				,@order=	N'None'
				,@stmttype=	N'delete'
	end
----------
	exec	@sExecTriggerOrder
			@triggername=	@sTriggerName
			,@order=	N'Last'
			,@stmttype=	N'delete'
end
deallocate	c
go
----------------------------------------------------------------------------------------------------

--TEST

set	xact_abort	on

begin	tran

if	object_id ( 'dbo.Test',	'u' )	is	not	null
	drop	table	dbo.Test
create	table	dbo.Test
(	f1	int		primary	key
	,f2	varchar ( 256 )	null
	,f3	datetime	null
	,q	rowversion )
exec	dbo.Entriggerate
		@sTableData=	'dbo.Test'
		,@sTableLog=	'tempdb.dbo.Log'
insert	dbo.Test	( f1,	f2,	f3 )
select	1
	,'1'
	,getdate()
update	dbo.Test	set	f3=	getdate()

rollback
15 дек 19, 23:06    [22040813]     Ответить | Цитировать Сообщить модератору
 Re: Журналирование null полей в xml при помощи триггера  [new]
invm
Member

Откуда: Москва
Сообщений: 9347
tunknown,

Если честно, то на первый взгляд это полная жесть, которую можно и нужно упрощать до вменяемого вида.
16 дек 19, 00:51    [22040830]     Ответить | Цитировать Сообщить модератору
 Re: Журналирование null полей в xml при помощи триггера  [new]
tunknown
Member

Откуда:
Сообщений: 760
invm
Если честно, то на первый взгляд это полная жесть, которую можно и нужно упрощать до вменяемого вида.
Возможно, но проще у меня не получилось. Давно хотел сделать простое заполнение шаблонов с макросами. Но кроме хардкода ничего не выходит.
16 дек 19, 17:05    [22041440]     Ответить | Цитировать Сообщить модератору
 Re: Журналирование null полей в xml при помощи триггера  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31355
tunknown
invm
Если честно, то на первый взгляд это полная жесть, которую можно и нужно упрощать до вменяемого вида.
Возможно, но проще у меня не получилось. Давно хотел сделать простое заполнение шаблонов с макросами. Но кроме хардкода ничего не выходит.
Лучше cделать универсальную процедуру, которая генерит триггер логирования подобного вида
inserted MyTable_log(dt, user, field1, field2, ...) 
select getdate(), susersname(), field1, field2, ... from inserted 

А ваш универсальный триггер крут, как все "универсальные процедуры", но непригоден для использования в продакшене.
16 дек 19, 18:04    [22041476]     Ответить | Цитировать Сообщить модератору
 Re: Журналирование null полей в xml при помощи триггера  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7759
Оптимальный аудит таблиц можно сделать при помощи CDC.
Темпоральные таблицы - тоже хорошая штука, но имеют свои особенности. Триггеры приемлемы в малонагруженных таблицах, но там городить "унивесальный" код нет смысла.
16 дек 19, 18:19    [22041489]     Ответить | Цитировать Сообщить модератору
 Re: Журналирование null полей в xml при помощи триггера  [new]
Критик
Member

Откуда: Москва / Калуга
Сообщений: 33658
Блог
Владислав Колосов
Триггеры приемлемы в малонагруженных таблицах


Довольно спорное утверждение
16 дек 19, 20:52    [22041579]     Ответить | Цитировать Сообщить модератору
 Re: Журналирование null полей в xml при помощи триггера  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31355
Владислав Колосов
Оптимальный аудит таблиц можно сделать при помощи CDC.
Там же пользователь не пишется...

Владислав Колосов
Триггеры приемлемы в малонагруженных таблицах,
Конечно, CDC быстрее, но логирование в триггере не сильно просаживает производительность, речь о процентах, а не о разах. Поэтому, почему бы не в "высоконагруженных"?
16 дек 19, 21:18    [22041593]     Ответить | Цитировать Сообщить модератору
 Re: Журналирование null полей в xml при помощи триггера  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7759
alexeyvg,

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

В некоторых случаях историю формируют парой "изменённое поле" - "значение" и помещают в триггер. Фиксация транзакции затягивается.
17 дек 19, 01:22    [22041707]     Ответить | Цитировать Сообщить модератору
 Re: Журналирование null полей в xml при помощи триггера  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31355
Владислав Колосов
Под высокой нагрузкой я имею в виду большой объем обновлений, например месячные или квартальные расчеты. Простой триггер даст просадку, темпоральная таблица будет лучше, CDC - вообще великолепно.
Я и говорил про просадку, она будет уровня единиц процентов.
Разве это так критично, что: "нельзя использовать из за просадки производительности"?
Обычная, вполне приемлемая, плата за функциональность.
17 дек 19, 08:28    [22041775]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить