Универсальный триггер с вызовом процедуры и передачей таблицы deleted

Asteroidishe

Имеется куча таблиц и триггеры к ним вида
ALTER trigger [dbo].[Payments_del_upd_trg] on [dbo].[Payments]
after delete, update
as 
begin
	set nocount on;
	declare @id bigint;
	if (exists (select <b>1</b> from inserted))
	begin
		insert into audit (Operation) values('update'); select @id = @@IDENTITY;
		
		insert into AuditPayments
		select @id, d.* from deleted d
	end;
	else 	
	begin
		insert into audit (Operation) values('delete'); select @id = @@IDENTITY;		
		
		insert into AuditPayments
		select @id, d.* from deleted d
	end;
end
Тобиш при удалении либо изменении данных происхдит полное дублирование данных в соотвествующую таблицу аудита. Сервер - sql express поэтому change tracking недоступен.Т.к. таблиц и триггеров куча - соотвественно куча копипастинга, сейчас возникла необходимость всю эту кучу переписать (ну например вылезла ошибка из-за того что в deleted может быть не отдна запись, и @id нарушает уникальность pk в AuditPayments, ну и вообще немного схему работы аудита меняю)Начал переделывать, получилось такое
ALTER trigger [dbo].[Payments_del_upd_trg] on [dbo].[Payments]
after delete, update
as 
begin
	set nocount on;	
	declare @operation ******* = <b>255</b>;
	if (exists (select <b>1</b> from inserted))	
		set @operation = <b>1</b>; -- update	
	else	
		set @operation = <b>2</b>; -- delete	
				
	insert into AuditPayments
	select dbo.InsertIntoAudit(@operation), d.* from deleted d	
end
CREATE FUNCTION dbo.InsertIntoAudit
(
	@operation *******
)
RETURNS bigint
AS
BEGIN	
	declare @id bigint;	
	execute [dbo].[InsertIntoAudit_prc] @operation ,@id OUTPUT;
	RETURN @id;
END
GO
CREATE PROCEDURE [dbo].InsertIntoAudit_prc	
	@operation *******,
	@id bigint out 
AS
BEGIN	
	if (@operation = <b>1</b>)
		insert into audit (Operation) values('update');
	else
		insert into audit (Operation) values('delete'); 
			
	select @id = SCOPE_IDENTITY();
END
delete from payments where idPayment in (<b>5350</b>, <b>5351</b>)
Msg <b>557</b>, Level <b>16</b>, State <b>2</b>, Procedure Payments_del_upd_trg, Line <b>13</b>
Внутри функции можно вызывать только функции и некоторые расширенные хранимые процедуры.
Ессно такое (неработающее) состояние меня не устраивает %)В общем что хочетсяПолучить триггер вида
ALTER trigger [dbo].[Payments_del_upd_trg] on [dbo].[Payments]
after delete, update
as 
begin
	exec AllTriggersPrc((exists (select <b>1</b> from inserted)), deleted);
end
Такой триггер создать не проблема - проблема создать процедуру AllTriggersPrc, помогите люди добрые.
24 ответа

Asteroidishe

Во-первых, неправильно определяется, на какое действие вызван триггер.Получается, что если я делаю UPDATE 0 записей, зафмксируется, что был DELETE.Во-вторых, Вы не назвали номер версии сервера, а от этого много зависит в Вашем случае.


Asteroidishe

Во-первых, неправильно определяется, на какое действие вызван триггер.Получается, что если я делаю UPDATE 0 записей, зафмксируется, что был DELETE.
Первый триггер переписан будет, он только для образца того, что внутрях делается.
Во-вторых, Вы не назвали номер версии сервера, а от этого много зависит в Вашем случае.
Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86) Mar 29 2009 10:27:29 Copyright (c) 1988-2008 Microsoft Corporation Express Edition on Windows NT 6.1 (Build 7600: ) (WOW64)


Asteroidishe

Что-то никто даже идей не предлагает, подозрительно %)


Asteroidishe

Вы сюда заглядывали?http://sql.ru/forum/actualthread.aspx?tid=746258&pg=-1это не ответ на ваш вопрос, но возможно вы можете пересмотреть структуру приложения если не поздно. И это решит все ваши проблемы.


Asteroidishe

Вы сюда заглядывали?http://sql.ru/forum/actualthread.aspx?tid=746258&pg=-1
Да, очень занимательный срач, но я против premature optimizations и не хочу здесь разводить аналогичный.
это не ответ на ваш вопрос, но возможно вы можете пересмотреть структуру приложения если не поздно. И это решит все ваши проблемы.
У меня нет проблем. У меня сложность с написанием того что я хочу, синтаксис не знаю, если будет угодно.Мне сейчас важно написать так чтобы можно было быстро менять. Предлагать писать для каждой операции (select/insert/update/delete) для каждой таблицы свою хранимку - это тупо и обезьянья работа и менять это будет отнюдь не быстро.Хотя если кто-то может предложить код универсальной хранимки (требования описаны в первом посте) вместо универсального триггера - я не откажусь.


Asteroidishe

Да, очень занимательный срач, но я против premature optimizations и не хочу здесь разводить аналогичный.
О! Вы тоже в рекомендациях MVP видите какой-то срач. Чувствуется что в эксСССР все идет своим путем - даже при написании софта используется некоторая духовность.
Мне сейчас важно написать так чтобы можно было быстро менять.
Быстро менять что? Значение в поле для миллионов записей или код аудита?
Предлагать писать для каждой операции (select/insert/update/delete) для каждой таблицы свою хранимку - это тупо и обезьянья работа и менять это будет отнюдь не быстро.
А значит писать триггера это работа для нобелевских лауреатов. При этом триггеров конечно у вас значительно меньше, так? И пишутся они естественно быстрее ибо питаются божьим духом.
Хотя если кто-то может предложить код универсальной хранимки (требования описаны в первом посте) вместо универсального триггера - я не откажусь.
(пожимая плечами) ХмCREATE SP_UPDATEINSERT INTO AuditTable (OldValue, NewValue, DateUpdate)....SELECT OLdValue, Fn(OldValue), GETDATE() FROM Table WHERE id =...UPDATE Table SET OLdValue = Fn(OldValue)WHERE id = ...Очень сложно, да? Если уж не хочется генерить по 4 SP для каждой таблицы - используйте одну SP с модификатором 1 - select 2 - insert ...


Asteroidishe

О! Вы тоже в рекомендациях MVP видите какой-то срач.
У меня не те объемы где надо писать по руководствам мвп. Для справки - ограничение express - 4gb на базу.
Быстро менять что? Значение в поле для миллионов записей или код аудита?
Код аудита.
При этом триггеров конечно у вас значительно меньше, так? И пишутся они естественно быстрее ибо питаются божьим духом.
Да, триггеров как минимум в 2 раза меньше - вместо хранимок для delete+update один триггер after delete, update.Далее - во всех этих триггерах я планирую вызывать одну хранимку с разными параметрами.Получается одна хранимка вместо 2*кол-во таблиц. Это достаточно меньше?На этой ноте предлагаю перестать пустословить и начать помогать.
CREATE SP_UPDATEINSERT INTO AuditTable (OldValue, NewValue, DateUpdate)....SELECT OLdValue, Fn(OldValue), GETDATE() FROM Table WHERE id =...UPDATE Table SET OLdValue = Fn(OldValue)WHERE id = ...
А теперь добавь сюда все поля (ну например возьмем кол-во в 5 полей на таблицу)А теперь включи мозг и пойми, что поля во всех таблицах разныеА теперь пойми что название AuditTable для всех таблиц - тоже разное.И каким местом она универсальная? Как шаблон - сгодится, но когда этот шаблон будет раскопирован на несколько десятков таблиц - он несколько утухнет в своем очаровании.Как погляжу товарищ фыркающий писатель, а не читатель.


Asteroidishe

У меня не те объемы где надо писать по руководствам мвп. Для справки - ограничение express - 4gb на базу.
Ну тогда флаг вам в руки, барабан вам на шею и попутного якоря. Все с такого начинают как вы - заканчивают тоже одинаково.
Код аудита.
Об этом все должны были догадаться по особой духовности в глазах.
Да, триггеров как минимум в 2 раза меньше - вместо хранимок для delete+update один триггер after delete, update.
Судя по вопросам - они у вас еще и работают минимум в 2 раза лучше чем предложеный вариант.
Далее - во всех этих триггерах я планирую вызывать одну хранимку с разными параметрами.Получается одна хранимка вместо 2*кол-во таблиц. Это достаточно меньше?
То есть вызывать одну хранимку из другой вам Коран не позволяет? Из Триггера значит можно, а их SP - аллах покарает?
На этой ноте предлагаю перестать пустословить и начать помогать.
Вы такой забавный
А теперь добавь сюда все поля (ну например возьмем кол-во в 5 полей на таблицу)А теперь включи мозг и пойми, что поля во всех таблицах разныеА теперь пойми что название AuditTable для всех таблиц - тоже разное.
То есть в тригерах вам это все проделывать не прийдется. Или у вас какие-то особенные мозги.
И каким местом она универсальная? Как шаблон - сгодится, но когда этот шаблон будет раскопирован на несколько десятков таблиц - он несколько утухнет в своем очаровании.
Еще раз - триггеры создадутся божьим духом - он материализует разные поля в код.
Как погляжу товарищ фыркающий писатель, а не читатель.
Это товарищь который уже это все давно прошел. Но у вас свой путь - через болото по краю пропасти. Рабочее название фильма - "Бегущая по граблям"PS Попробуйте на досуге подумать ЧЕМ триггер отличается от SP. Триггер это SP привязаная к ивенту и имеющая несколько дополнительных объектов типа inserted и deleted. Чего такого таинственного можно сделать триггером чего нельзя сделать в SP.


Asteroidishe

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


Asteroidishe

1. Нету смысла писать в таблицу аудита ничего, кроме DELETED. Ибо фсе остальное есть в основной таблице.2. Триггер должон быть примитивным
ALTER trigger [dbo].[Payments_del_upd_trg] on [dbo].[Payments]
after delete, update
as 
begin
	insert into AuditPayments
	select * from deleted
end
4. Структура AuditPayments должна быть копией Payments, за вычетом IDENTITY, foreign key, индексов и т.п.3. Ежели вам надо в AuditPayments дополнительные поля - пихайте их в КОНЕЦ таблицы и вешайте на них DEFAULT.


Asteroidishe

1. Нету смысла писать в таблицу аудита ничего, кроме DELETED. Ибо фсе остальное есть в основной таблице.
Ага - кроме разве что последовательности изменений значений в полях


Asteroidishe

Об этом все должны были догадаться по особой духовности в глазах.
Об этом несложно догадаться если прочитать самый первый пост в ветке. Ради этого она и создавалась - узнать как реализовать легкое изменение кода аудита.
То есть в тригерах вам это все проделывать не прийдется. Или у вас какие-то особенные мозги.
Придется.
Это товарищь который уже это все давно прошел. Но у вас свой путь - через болото по краю пропасти. Рабочее название фильма - "Бегущая по граблям"
Представься, гуру, компания, фио, должность, стаж работы. Или выйди лесом из моей ветки.
PS Попробуйте на досуге подумать ЧЕМ триггер отличается от SP. Триггер это SP привязаная к ивенту и имеющая несколько дополнительных объектов типа inserted и deleted. Чего такого таинственного можно сделать триггером чего нельзя сделать в SP.
Можно не делать описание всех полей в таблице. Как-раз из-за наличия deleted/inserted


Asteroidishe

1.2. ...4. ...3. ...
Зачем это тут? Вопрос по-омему ОЧЕНЬ конкретен - проблема в УНИВЕРСАЛЬНОЙ передаче таблицы deleted из триггера в хранимку.


Asteroidishe

Ага - кроме разве что последовательности изменений значений в полях
insert into AuditPayments
	select *, SUSER_NAME(), GETDATE() from deleted
Так хорошо?


Asteroidishe

Зачем это тут? Вопрос по-омему ОЧЕНЬ конкретен - проблема в УНИВЕРСАЛЬНОЙ передаче таблицы deleted из триггера в хранимку.
Придется писать очень много динамики, и даже немного динамики в динамике.


Asteroidishe

Все отлично, всем печеньки. Только вопрос не в этом.У меня давно все работает по старому алгоритму.Вот напрмер табличка аудит
CREATE TABLE [dbo].[Audit](
	[id] [bigint] IDENTITY(<b>1</b>,<b>1</b>) NOT NULL,
	[AuditTime] [datetime] NOT NULL,
	[SystemUser] [nvarchar](max) NULL,
	[SystemUserSID] [varbinary](max) NULL,
	[OriginalLogin] [nvarchar](max) NULL,
	[OriginalLoginSID] [varbinary](max) NULL,
	[Operation] [nvarchar](<b>50</b>) NULL,
 CONSTRAINT [PK_Audit] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[Audit] ADD CONSTRAINT [DF_Audit_datetime2] DEFAULT (sysdatetime()) FOR [AuditTime]
GO

ALTER TABLE [dbo].[Audit] ADD CONSTRAINT [DF_Audit_User] DEFAULT (suser_sname()) FOR [SystemUser]
GO

ALTER TABLE [dbo].[Audit] ADD CONSTRAINT [DF_Audit_UserSID] DEFAULT (suser_sid(suser_sname())) FOR [SystemUserSID]
GO

ALTER TABLE [dbo].[Audit] ADD CONSTRAINT [DF_Audit_OriginalLogin] DEFAULT (original_login()) FOR [OriginalLogin]
GO

ALTER TABLE [dbo].[Audit] ADD CONSTRAINT [DF_Audit_OriginalLoginSID] DEFAULT (suser_sid(original_login())) FOR [OriginalLoginSID]
GO
Хватит лечить проблемы которых у меня нет.


Asteroidishe

Ага - кроме разве что последовательности изменений значений в полях
Плохо понимаете?


Asteroidishe

Зачем это тут? Вопрос по-омему ОЧЕНЬ конкретен - проблема в УНИВЕРСАЛЬНОЙ передаче таблицы deleted из триггера в хранимку.
Вопрос глупый, если удосужиться прочитать, что же такое deleted. Если так уж невтерпеж ее передать - это можно сделать через select * into #temp_table from deleted.


Asteroidishe

Придется писать очень много динамики, и даже немного динамики в динамике.
Все-таки хочется немного примеров кода. Или ссылок.


Asteroidishe

При жестких ограничениях по месту, у вас местами явно избыточные типы данных. Особенно пестрит max'ами всякими. Точно нет проблем?


Asteroidishe

Все-таки хочется немного примеров кода. Или ссылок.
Ну, про динамические запросы в факе прочитать можно. Дальше только дело техники - нужный текст состряпать.


Asteroidishe

Придется писать очень много динамики, и даже немного динамики в динамике.
1. Да какая там динамика? В триггере ее не будет.
ALTER trigger [dbo].[Payments_del_upd_trg] on [dbo].[Payments]
after delete, update
as 
begin
	select * into #UniversalAudit from deleted
 exec UniversalAuditSP @table='[dbo].[Payments]'
end
2. В процедуре будет динамика... Ну да тредстартера это быстро утомит.


Asteroidishe

При жестких ограничениях по месту, у вас местами явно избыточные типы данных. Особенно пестрит max'ами всякими. Точно нет проблем?
Ну замечательно показал табличку теперь ее лечат %)Именно из-за избыточности начал всю эту канитель. Считайте что таблички уже нет.Про динамику появилось пару мыслей ща попробую написать чего-нибудь.


Asteroidishe

select * into #UniversalAudit from deleted
Я может что не знаю, но разве получится вставить разные данные в одну таблицу? Local temporary tables are visible only to their creators during the same connection to an instance of SQL Server as when the tables were first created or referenced.Если будет вызвано два триггера для разных таблиц - получим облом, нет?Тут придется создавать темповых таблиц с привязкой к имени таблицы.