Гость
Войти | Профиль | Очистить
Действия ...
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Обработка ошибки внутри триггера / 19 сообщений из 19, страница 1 из 1
23.04.2021, 16:04
    #40065263
teCa
teCa
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Обработка ошибки внутри триггера
Всем привет, написал триггер, который логирует ряд действий пользователей на сервере:

Код: 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.
USE [master]
GO

/****** Object:  DdlTrigger [ddl_logging]    Script Date: 23.04.2021 14:43:17 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


 
ALTER TRIGGER [ddl_logging]   
ON ALL SERVER
FOR DDL_DATABASE_LEVEL_EVENTS   
AS  
BEGIN

	DECLARE @data XML = EVENTDATA()
	DECLARE @DBname NVARCHAR(255) = @data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'nvarchar(250)') 
	DECLARE @ObjectName NVARCHAR(250) = @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(250)') 
	DECLARE @EventType NVARCHAR(250) = @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(250)') 
	DECLARE @TSQLCommand NVARCHAR(MAX) = @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(max)')	


        IF (@EventType != 'UPDATE_STATISTICS' and @DBName != 'tempdb') 
		BEGIN
	
			INSERT INTO [ADMIN].dbo.ddl_log
			([db_name], [db_object_name], [action_name], [tsql_code])			
			VALUES (@DBName, @ObjectName, @EventType, @TSQLCommand)

		END
	
END
    
GO



Собственно вопрос, как можно избежать прерывание транзакции, например если база ADMIN перестала быть доступной или у пользователю не выданы права на запись в таблицу [ADMIN].dbo.ddl_log?

С try catch не особо знаком, поможет ли эта инструкция в обработке этой ошибки внутри триггера?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
23.04.2021, 16:06
    #40065264
Ведущий профессионал
Ведущий профессионал
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Обработка ошибки внутри триггера
teCa
С try catch не особо знаком, поможет ли эта инструкция в обработке этой ошибки внутри триггера?
Пора знакомиться. Да, поможет.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
23.04.2021, 16:44
    #40065275
teCa
teCa
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Обработка ошибки внутри триггера
Ведущий профессионал,

Не понимаю, как можно подобное оформить.

Код: 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.
BEGIN TRY
	BEGIN TRANSACTION;  
		IF (@EventType != 'UPDATE_STATISTICS' and @DBName != 'tempdb') 
		BEGIN
	
			INSERT INTO [ADMIN].dbo.ddl_log
			([db_name], [db_object_name], [action_name], [tsql_code])			
			VALUES (@DBName, @ObjectName, @EventType, @TSQLCommand)
		END
	COMMIT TRANSACTION
	END TRY
	BEGIN CATCH
    IF (XACT_STATE()) = -1  
    BEGIN  
        PRINT  
            N'The transaction is in an uncommittable state.' +  
            'Rolling back transaction.'  
        ROLLBACK TRANSACTION;  
    END;  
    IF (XACT_STATE()) = 1  
    BEGIN  
        PRINT  
            N'The transaction is committable.' +  
            'Committing transaction.'  
        COMMIT TRANSACTION;     
    END;
	END CATCH



В данной инструкции при отсутствии прав у пользователя на доступ к БД ADMIN, получаю ошибку из данного блока:

Код: sql
1.
2.
3.
4.
5.
6.
7.
    IF (XACT_STATE()) = -1  
    BEGIN  
        PRINT  
            N'The transaction is in an uncommittable state.' +  
            'Rolling back transaction.'  
        ROLLBACK TRANSACTION;  
    END;



В данном случае, какая из транзакций будет считаться некомментируемой? Та, которая к блоке try? Почему тогда прерывается транзакция, по которой срабатывает триггер?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
23.04.2021, 16:51
    #40065280
Гавриленко Сергей Алексеевич
Гавриленко Сергей Алексеевич
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Обработка ошибки внутри триггера
В триггере всегда ровно одна транзакция. Вернее, не более одной.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
23.04.2021, 16:54
    #40065282
teCa
teCa
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Обработка ошибки внутри триггера
Гавриленко Сергей Алексеевич,

Хорошо, как мне добиться, что-бы не прерывалась транзакция, которая инициализирует срабатывание триггера?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
23.04.2021, 16:58
    #40065285
invm
invm
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Обработка ошибки внутри триггера
Бывают ошибки компиляции и ошибки выполнения.
Чтобы отлавливать и те и другие, содержимое блока try должно быть оформлено в виде DSQL

В триггере неявно устанавливно xact_abort = on. Поэтому перехват любой (кроме пользовательских по raiserror) ошибки переведет транзакцию в нефиксируемое состояние.

Commit или rollback в триггере приводит к ошибке 3609 и прекращению выполнения текущего пакета.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
23.04.2021, 17:14
    #40065289
teCa
teCa
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Обработка ошибки внутри триггера
invm,

Получается, что внутри триггера у меня происходит ошибка выполнения, и из вашего текста, можно сделать вывод, что и проигнорировать эту ошибку нельзя и транзакция в любом случае будет переведена в нефиксируемое состояние?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
23.04.2021, 17:43
    #40065309
invm
invm
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Обработка ошибки внутри триггера
teCa,

 
Код: 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.
use tempdb;
set ansi_nulls, quoted_identifier, xact_abort on;
go

create table dbo.t (id int);
go

create or alter trigger dbo.tr_t
on dbo.t
after insert
as
begin
 set nocount on;

 begin try
  exec('select * from NonExistentTable');
 end try
 begin catch
  select 'xact_abort = on', error_message();
 end catch;

end;
go

begin tran;
insert into dbo.t (id) values (1);
select xact_state();
rollback;
go

create or alter trigger dbo.tr_t
on dbo.t
after insert
as
begin
 set nocount on;
 set xact_abort off;

 begin try
  exec('select * from NonExistentTable');
 end try
 begin catch
  select 'xact_abort = off', error_message();
 end catch;

end;
go

begin tran;
insert into dbo.t (id) values (1);
select xact_state();
rollback;
go

drop table dbo.t;
go

...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
23.04.2021, 18:06
    #40065318
Ведущий профессионал
Ведущий профессионал
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Обработка ошибки внутри триггера
teCa
invm,

Получается, что внутри триггера у меня происходит ошибка выполнения, и из вашего текста, можно сделать вывод, что и проигнорировать эту ошибку нельзя и транзакция в любом случае будет переведена в нефиксируемое состояние?
Напишите в начале триггера SET XACT_ABORT OFF;
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
23.04.2021, 20:17
    #40065351
Владислав Колосов
Владислав Колосов
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Обработка ошибки внутри триггера
teCa,

для таких целей существуют аудиты.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
27.04.2021, 12:05
    #40066278
teCa
teCa
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Обработка ошибки внутри триггера
invm
teCa,

 
Код: 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.
use tempdb;
set ansi_nulls, quoted_identifier, xact_abort on;
go

create table dbo.t (id int);
go

create or alter trigger dbo.tr_t
on dbo.t
after insert
as
begin
 set nocount on;

 begin try
  exec('select * from NonExistentTable');
 end try
 begin catch
  select 'xact_abort = on', error_message();
 end catch;

end;
go

begin tran;
insert into dbo.t (id) values (1);
select xact_state();
rollback;
go

create or alter trigger dbo.tr_t
on dbo.t
after insert
as
begin
 set nocount on;
 set xact_abort off;

 begin try
  exec('select * from NonExistentTable');
 end try
 begin catch
  select 'xact_abort = off', error_message();
 end catch;

end;
go

begin tran;
insert into dbo.t (id) values (1);
select xact_state();
rollback;
go

drop table dbo.t;
go



В предложенном скрипте, все отлично, запись в таблицу dbo.t происходит при ошибке внутри триггера.
Делаю по предложенному примеру и все равно получаю ошибку:

авторxact_abort = off Серверу-участнику "BOOKCENTRE\ap.sql" не удалось обратиться к базе данных "ADMIN" в текущем контексте безопасности.

Тестовая учетка, у которой нет прав на базу ADMIN

авторСообщение 3616, уровень 16, состояние 2, строка 4
Произошла ошибка при выполнении триггера. Выполнение пакета аварийно завершено, и произведен откат пользовательской транзакции, если она существует.


Сам триггер:
 
Код: 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.
USE [master]
GO

/****** Object:  DdlTrigger [ddl_logging]    Script Date: 27.04.2021 11:52:26 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



 
ALTER TRIGGER [ddl_logging]   
ON ALL SERVER
FOR DDL_DATABASE_LEVEL_EVENTS   
AS  
BEGIN
	set nocount on;
	set xact_abort off;
	DECLARE @data XML = EVENTDATA()
	DECLARE @DBname NVARCHAR(255) = @data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'nvarchar(250)') 
	DECLARE @ObjectName NVARCHAR(250) = @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(250)') 
	DECLARE @EventType NVARCHAR(250) = @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(250)') 
	DECLARE @TSQLCommand NVARCHAR(MAX) = @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(max)')	

	BEGIN TRY
		IF (@EventType != 'UPDATE_STATISTICS' and @DBName != 'tempdb') 
		BEGIN
	
			INSERT INTO [ADMIN].dbo.ddl_log
			([db_name], [db_object_name], [action_name], [tsql_code])			
			VALUES (@DBName, @ObjectName, @EventType, @TSQLCommand)
		END
	END TRY
	BEGIN CATCH
    select 'xact_abort = off', error_message();
	END CATCH
END
    
GO

...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
27.04.2021, 12:49
    #40066296
invm
invm
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Обработка ошибки внутри триггера
teCa,

Самый простой и самый неправильный способ
1.
Код: sql
1.
alter database master set trustworthy on;

2.
Код: sql
1.
2.
3.
4.
ALTER TRIGGER [ddl_logging]   
ON ALL SERVER
with execute as 'dbo'
FOR DDL_DATABASE_LEVEL_EVENTS



Правильный способ - делать не триггером, а через https://docs.microsoft.com/ru-ru/sql/relational-databases/service-broker/event-notifications?view=sql-server-ver15
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
27.04.2021, 13:11
    #40066304
Владислав Колосов
Владислав Колосов
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Обработка ошибки внутри триггера
Первый способ не то, чтобы неправильный, но он требует ограниченного доступа проверенного персонала к системе и система должна быть изолирована от внешней среды.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
27.04.2021, 13:49
    #40066327
teCa
teCa
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Обработка ошибки внутри триггера
invm,

Второй вариант мне не подходит, в таком случае в логе я вижу, что все действия выполняются пользователем with execute as 'dbo'.

Попробую разобраться с событиями.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
27.04.2021, 14:03
    #40066336
teCa
teCa
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Обработка ошибки внутри триггера
teCa,

Хотя original_login() всё верно определяет, так, что второй вариант вполне себе меня устроит.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
24.05.2021, 13:19
    #40072442
Кесарь
Кесарь
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Обработка ошибки внутри триггера
teCa,

1. Что это за "пользователи" у вас, что могут делать UPDATE_STATISTICS ???

2. "EVENT NOTIFICATION Создает объект, отправляющий в службу компонента Service Broker данные о событии сервера или базы данных". Вроде неплохо выглядит, хотя использовать не приходилось. Но есть одно но. Service Broker весьма чувствителен к нагрузке. Если через него льётся большой поток данных, то могут быть проблемы. Хотя если использовать не для данных, а для вот таких сервисных сообщений, то норм.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
24.05.2021, 18:17
    #40072549
Ennor Tiegael
Ennor Tiegael
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Обработка ошибки внутри триггера
Кесарь
Service Broker весьма чувствителен к нагрузке. Если через него льётся большой поток данных, то могут быть проблемы.
Как интересно. У вас действительно была ситуация, когда брокер все тормозил и прошлось изощряться, типа русановского "150 trick"? Что ж вы качали через него, если не секрет?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
24.05.2021, 18:36
    #40072554
Кесарь
Кесарь
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Обработка ошибки внутри триггера
Ennor Tiegael
Кесарь
Service Broker весьма чувствителен к нагрузке. Если через него льётся большой поток данных, то могут быть проблемы.
Как интересно. У вас действительно была ситуация, когда брокер все тормозил и прошлось изощряться, типа русановского "150 trick"? Что ж вы качали через него, если не секрет?


Ситуация была не у меня, а у компании. Качался весь поток операционных данных, разумеется. Как внешний, так и внутренний. Хранилища ессно обмены никакие не использовали.

Чем больше был поток, тем чаще были затыки. Перешли на кафку, от брокера отказались почти полностью (только для существенно меньшего внутреннего потока, и то не всего, а только части).
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
24.05.2021, 18:38
    #40072555
Ennor Tiegael
Ennor Tiegael
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Обработка ошибки внутри триггера
Кесарь,

Сколько десятков миллионов сообщений в секунду у вас шло, что сервис брокер не справился?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Обработка ошибки внутри триггера / 19 сообщений из 19, страница 1 из 1
Целевая тема:
Создать новую тему:
Автор:
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
созд. / загр.: 453ms
Закрыть
start [/forum/topic.php?fid=46&tablet=1&tid=1684683]:
 0ms
get settings:
 9ms
get forum list:
 14ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 34ms
get topic data:
 14ms
get forum data:
 3ms
get page messages:
 62ms
get tp. blocked users:
 2ms
others: 307ms
total:  453ms
x
x
Закрыть


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