ReSQL.ru
     
powered by simpleCommunicator - 2.0.53     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Журналирование null полей в xml при помощи триггера
15 сообщений из 15, страница 1 из 1
Журналирование null полей в xml при помощи триггера
    #39438998
tunknown
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
tunknown
Участник
Привожу пример журналирования триггером. На форуме такие есть, но не все- подходящие.
Для получения универсальности изменённые данные разложил не по полям, а поместил в xml.
Оказалось, что получить правильный xml, который хранит только изменения- непросто.
Сложность в том, что в xml отсутствие атрибута/элемента можно расценивать как null.
Если это изменение с null на не null, то сохраняя предыдущее значение поля это можно показать так:
Код: xml
1.
2.
3.
4.
5.
<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.
 
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
118.
119.
120.
121.
122.
123.
124.
125.
126.
127.
128.
129.
130.
131.
132.
133.
134.
135.
136.
137.
138.
139.
140.
141.
142.
143.
144.
145.
146.
147.
148.
149.
150.
151.
152.
153.
154.
155.
156.
157.
158.
159.
160.
161.
162.
163.
164.
165.
166.
167.
168.
169.
170.
171.
172.
173.
174.
175.
176.
177.
178.
179.
180.
181.
182.
183.
184.
185.
186.
187.
188.
189.
190.
191.
192.
193.
194.
195.
196.
197.
198.
199.
200.
201.
202.
203.
204.
205.
206.
207.
208.
209.
210.
211.
212.
213.
214.
215.
216.
217.
218.
219.
220.
221.
222.
223.
224.
225.
226.
227.
228.
229.
230.
231.
232.
233.
234.
235.
236.
237.
238.
239.
240.
241.
242.
243.
244.
245.
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



для теста
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
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 подобный метод уже есть . Нашёл его уже после написания своего.
...
Рейтинг: 0 / 0
14.04.2017, 22:04
| Ответить | Цитировать | Написать  
Журналирование null полей в xml при помощи триггера
    #39439026
felix_ff
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
felix_ff
Участник
tunknown,

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


 
code
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
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 это как воду из кувшина в реку лить
...
Рейтинг: 0 / 0
15.04.2017, 02:51
| Ответить | Цитировать | Написать  
Журналирование null полей в xml при помощи триггера
    #39439162
tunknown
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
tunknown
Участник
felix_ffвообще система отслеживания изменений не требует изобретений велосипедов, есть вполне неплохая CT а если нужна историчность то CDCНекоторые используют 2005 express, в нём не все расширения есть.

felix_ffP.s ну и на последок: ловить в триггере @@PROCID это как воду из кувшина в реку литьСудя по документации @@PROCID работает в триггере. А что не так?
...
Рейтинг: 0 / 0
15.04.2017, 17:55
| Ответить | Цитировать | Написать  
Журналирование null полей в xml при помощи триггера
    #39439194
felix_ff
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
felix_ff
Участник
tunknown,

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

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

Хотя если вы хотели отлавливать именно сами триггера - ну это совсем другое дело :)
...
Рейтинг: 0 / 0
15.04.2017, 21:27
| Ответить | Цитировать | Написать  
Журналирование null полей в xml при помощи триггера
    #39439291
tunknown
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
tunknown
Участник
felix_ffХотя если вы хотели отлавливать именно сами триггера - ну это совсем другое дело :)Я хотел получить название таблицы, но этого сделать нельзя без добавления динамики и перекомпиляции, т.к. предполагается, что таблица может быть использована из разных баз.

felix_ff
Код: sql
1.
ISNULL(d.[f3], 'NULL')

Предполагаю, что такой вариант не сработает из-за попытки приведения к единому типу, т.е. datetime. Или придётся писать собственный конвертер всех типов в строковые значения xml.
...
Рейтинг: 0 / 0
16.04.2017, 12:21
| Ответить | Цитировать | Написать  
Журналирование null полей в xml при помощи триггера
    #39439546
Владислав Колосов
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Владислав Колосов
Участник
tunknown,
авторв историю не попадают update ... set f2=f2, т.к. это не аудит
путаете способ хранения и отображения, т.е. структуру и представление. Отсюда возникла потребность в сложном преобразовании при сохранении. Создали проблему, с которой сами же и боретесь :)
...
Рейтинг: 0 / 0
17.04.2017, 11:06
| Ответить | Цитировать | Написать  
Период между сообщениями больше года.
Журналирование null полей в xml при помощи триггера
    #39903251
tunknown
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
tunknown
Участник
Ранее я упоминал генератор "универсального" триггера логгирования. Теперь привожу его код. Требуется sql 2005+, других зависимостей не предполагается. Процедура генератора может быть создана и в master. Поддерживаются таблицы в любой базе локального сервера, на которую есть права доступа на создание триггера. Логгирование только частичное, т.к. не поддерживаются BLOB поля и откат транзакции влияет на результат. Побочный эффект- переназначает на себя first/last trigger. Если для таблицы лога создать отдельную базу на быстром диске, то сильного падения производительности не наблюдается. Проверен на таблицах с 200+ полями.
 
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
118.
119.
120.
121.
122.
123.
124.
125.
126.
127.
128.
129.
130.
131.
132.
133.
134.
135.
136.
137.
138.
139.
140.
141.
142.
143.
144.
145.
146.
147.
148.
149.
150.
151.
152.
153.
154.
155.
156.
157.
158.
159.
160.
161.
162.
163.
164.
165.
166.
167.
168.
169.
170.
171.
172.
173.
174.
175.
176.
177.
178.
179.
180.
181.
182.
183.
184.
185.
186.
187.
188.
189.
190.
191.
192.
193.
194.
195.
196.
197.
198.
199.
200.
201.
202.
203.
204.
205.
206.
207.
208.
209.
210.
211.
212.
213.
214.
215.
216.
217.
218.
219.
220.
221.
222.
223.
224.
225.
226.
227.
228.
229.
230.
231.
232.
233.
234.
235.
236.
237.
238.
239.
240.
241.
242.
243.
244.
245.
246.
247.
248.
249.
250.
251.
252.
253.
254.
255.
256.
257.
258.
259.
260.
261.
262.
263.
264.
265.
266.
267.
268.
269.
270.
271.
272.
273.
274.
275.
276.
277.
278.
279.
280.
281.
282.
283.
284.
285.
286.
287.
288.
289.
290.
291.
292.
293.
294.
295.
296.
297.
298.
299.
300.
301.
302.
303.
304.
305.
306.
307.
308.
309.
310.
311.
312.
313.
314.
315.
316.
317.
318.
319.
320.
321.
322.
323.
324.
325.
326.
327.
328.
329.
330.
331.
332.
333.
334.
335.
336.
337.
338.
339.
340.
341.
342.
343.
344.
345.
346.
347.
348.
349.
350.
351.
352.
353.
354.
355.
356.
357.
358.
359.
360.
361.
362.
363.
364.
365.
366.
367.
368.
369.
370.
371.
372.
373.
374.
375.
376.
377.
378.
379.
380.
381.
382.
383.
384.
385.
386.
387.
388.
389.
390.
391.
392.
393.
394.
395.
396.
397.
398.
399.
400.
401.
402.
403.
404.
405.
406.
407.
408.
409.
410.
411.
412.
413.
414.
415.
416.
417.
418.
419.
420.
421.
422.
423.
424.
425.
426.
427.
428.
429.
430.
431.
432.
433.
434.
435.
436.
437.
438.
439.
440.
441.
442.
443.
444.
445.
446.
447.
448.
449.
450.
451.
452.
453.
454.
455.
456.
457.
458.
459.
460.
461.
462.
463.
464.
465.
466.
467.
468.
469.
470.
471.
472.
473.
474.
475.
476.
477.
478.
479.
480.
481.
482.
483.
484.
485.
486.
487.
488.
489.
490.
491.
492.
493.
494.
495.
496.
497.
498.
499.
500.
501.
502.
503.
504.
505.
506.
507.
508.
509.
510.
511.
512.
513.
514.
515.
516.
517.
518.
519.
520.
521.
522.
523.
524.
525.
526.
527.
528.
529.
530.
531.
532.
533.
534.
535.
536.
537.
538.
539.
540.
541.
542.
543.
544.
545.
546.
547.
548.
549.
550.
551.
552.
553.
554.
555.
556.
557.
558.
559.
560.
561.
562.
563.
564.
565.
566.
567.
568.
569.
570.
571.
572.
573.
574.
575.
576.
577.
578.
579.
580.
581.
582.
583.
584.
585.
586.
587.
588.
589.
590.
591.
592.
593.
594.
595.
596.
597.
598.
599.
600.
601.
602.
603.
604.
605.
606.
607.
608.
609.
610.
611.
612.
613.
614.
615.
616.
617.
618.
619.
620.
621.
622.
623.
624.
625.
626.
627.
628.
629.
630.
631.
632.
633.
634.
635.
636.
637.
638.
639.
640.
641.
642.
643.
644.
645.
646.
647.
648.
649.
650.
651.
652.
653.
654.
655.
656.
657.
658.
659.
660.
661.
662.
663.
664.
665.
666.
667.
668.
669.
670.
671.
672.
673.
674.
675.
676.
677.
678.
679.
680.
681.
682.
683.
684.
685.
686.
687.
688.
689.
690.
691.
692.
693.
694.
695.
696.
697.
698.
699.
700.
701.
702.
703.
704.
705.
706.
707.
708.
709.
710.
711.
712.
713.
714.
715.
716.
717.
718.
719.
720.
721.
722.
723.
724.
725.
726.
727.
728.
729.
730.
731.
732.
733.
734.
735.
736.
737.
738.
739.
740.
741.
742.
743.
744.
745.
746.
747.
748.
749.
750.
751.
752.
753.
754.
755.
756.
757.
758.
759.
760.
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

...
Рейтинг: 0 / 0
15.12.2019, 23:06
| Ответить | Цитировать | Написать  
Журналирование null полей в xml при помощи триггера
    #39903258
invm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
invm
Участник
tunknown,

Если честно, то на первый взгляд это полная жесть, которую можно и нужно упрощать до вменяемого вида.
...
Рейтинг: 0 / 0
16.12.2019, 00:51
| Ответить | Цитировать | Написать  
Журналирование null полей в xml при помощи триггера
    #39903618
tunknown
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
tunknown
Участник
invm
Если честно, то на первый взгляд это полная жесть, которую можно и нужно упрощать до вменяемого вида.
Возможно, но проще у меня не получилось. Давно хотел сделать простое заполнение шаблонов с макросами. Но кроме хардкода ничего не выходит.
...
Рейтинг: 0 / 0
16.12.2019, 17:05
| Ответить | Цитировать | Написать  
Журналирование null полей в xml при помощи триггера
    #39903645
Фотография alexeyvg
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
alexeyvg
Участник
tunknown
invm
Если честно, то на первый взгляд это полная жесть, которую можно и нужно упрощать до вменяемого вида.
Возможно, но проще у меня не получилось. Давно хотел сделать простое заполнение шаблонов с макросами. Но кроме хардкода ничего не выходит.
Лучше cделать универсальную процедуру, которая генерит триггер логирования подобного вида
Код: sql
1.
2.
inserted MyTable_log(dt, user, field1, field2, ...) 
select getdate(), susersname(), field1, field2, ... from inserted


А ваш универсальный триггер крут, как все "универсальные процедуры", но непригоден для использования в продакшене.
...
Рейтинг: 0 / 0
16.12.2019, 18:04
| Ответить | Цитировать | Написать  
Журналирование null полей в xml при помощи триггера
    #39903656
Владислав Колосов
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Владислав Колосов
Участник
Оптимальный аудит таблиц можно сделать при помощи CDC.
Темпоральные таблицы - тоже хорошая штука, но имеют свои особенности. Триггеры приемлемы в малонагруженных таблицах, но там городить "унивесальный" код нет смысла.
...
Рейтинг: 0 / 0
16.12.2019, 18:19
| Ответить | Цитировать | Написать  
Журналирование null полей в xml при помощи триггера
    #39903708
Фотография Критик
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Критик
Участник
Владислав Колосов
Триггеры приемлемы в малонагруженных таблицах


Довольно спорное утверждение
...
Рейтинг: 0 / 0
16.12.2019, 20:52
| Ответить | Цитировать | Написать  
Журналирование null полей в xml при помощи триггера
    #39903718
Фотография alexeyvg
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
alexeyvg
Участник
Владислав Колосов
Оптимальный аудит таблиц можно сделать при помощи CDC.
Там же пользователь не пишется...

Владислав Колосов
Триггеры приемлемы в малонагруженных таблицах,
Конечно, CDC быстрее, но логирование в триггере не сильно просаживает производительность, речь о процентах, а не о разах. Поэтому, почему бы не в "высоконагруженных"?
...
Рейтинг: 0 / 0
16.12.2019, 21:18
| Ответить | Цитировать | Написать  
Журналирование null полей в xml при помощи триггера
    #39903794
Владислав Колосов
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Владислав Колосов
Участник
alexeyvg,

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

В некоторых случаях историю формируют парой "изменённое поле" - "значение" и помещают в триггер. Фиксация транзакции затягивается.
...
Рейтинг: 0 / 0
17.12.2019, 01:22
| Ответить | Цитировать | Написать  
Журналирование null полей в xml при помощи триггера
    #39903834
Фотография alexeyvg
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
alexeyvg
Участник
Владислав Колосов
Под высокой нагрузкой я имею в виду большой объем обновлений, например месячные или квартальные расчеты. Простой триггер даст просадку, темпоральная таблица будет лучше, CDC - вообще великолепно.
Я и говорил про просадку, она будет уровня единиц процентов.
Разве это так критично, что: "нельзя использовать из за просадки производительности"?
Обычная, вполне приемлемая, плата за функциональность.
...
Рейтинг: 0 / 0
17.12.2019, 08:28
| Ответить | Цитировать | Написать  
15 сообщений из 15, страница 1 из 1
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Журналирование null полей в xml при помощи триггера
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=46&fpage=76&tid=1686770]:
 0ms
get settings:
 10ms
get forum list:
 15ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 31ms
get topic data:
 12ms
get forum data:
 3ms
get page messages:
 54ms
get tp. blocked users:
 2ms
others: 336ms
total:  469ms
созд. / загр.: 469ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]