ReSQL.ru
     
powered by simpleCommunicator - 2.0.51     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Обработка ошибки внутри триггера
19 сообщений из 19, страница 1 из 1
Обработка ошибки внутри триггера
    #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:04
| Ответить | Цитировать | Написать  
Обработка ошибки внутри триггера
    #40065264
Ведущий профессионал
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Ведущий профессионал
Гость
teCa
С try catch не особо знаком, поможет ли эта инструкция в обработке этой ошибки внутри триггера?
Пора знакомиться. Да, поможет.
...
Рейтинг: 0 / 0
23.04.2021, 16:06
| Ответить | Цитировать | Написать  
Обработка ошибки внутри триггера
    #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:44
| Ответить | Цитировать | Написать  
Обработка ошибки внутри триггера
    #40065280
Гавриленко Сергей Алексеевич
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гавриленко Сергей Алексеевич
Участник
В триггере всегда ровно одна транзакция. Вернее, не более одной.
...
Рейтинг: 0 / 0
23.04.2021, 16:51
| Ответить | Цитировать | Написать  
Обработка ошибки внутри триггера
    #40065282
teCa
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
teCa
Участник
Гавриленко Сергей Алексеевич,

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

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

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

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

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

для таких целей существуют аудиты.
...
Рейтинг: 0 / 0
23.04.2021, 20:17
| Ответить | Цитировать | Написать  
Обработка ошибки внутри триггера
    #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:05
| Ответить | Цитировать | Написать  
Обработка ошибки внутри триггера
    #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, 12:49
| Ответить | Цитировать | Написать  
Обработка ошибки внутри триггера
    #40066304
Владислав Колосов
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Владислав Колосов
Участник
Первый способ не то, чтобы неправильный, но он требует ограниченного доступа проверенного персонала к системе и система должна быть изолирована от внешней среды.
...
Рейтинг: 0 / 0
27.04.2021, 13:11
| Ответить | Цитировать | Написать  
Обработка ошибки внутри триггера
    #40066327
teCa
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
teCa
Участник
invm,

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

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

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

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

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


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

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

Сколько десятков миллионов сообщений в секунду у вас шло, что сервис брокер не справился?
...
Рейтинг: 0 / 0
24.05.2021, 18:38
| Ответить | Цитировать | Написать  
19 сообщений из 19, страница 1 из 1
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Обработка ошибки внутри триггера
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=46&msg=40065285&tid=1684683]:
 0ms
get settings:
 8ms
get forum list:
 11ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 63ms
get topic data:
 9ms
get forum data:
 2ms
get page messages:
 51ms
get tp. blocked users:
 1ms
others: 288ms
total:  441ms
созд. / загр.: 441ms
x
x
Закрыть


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