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

Откуда: Москва
Сообщений: 877
Всем привет, написал триггер, который логирует ряд действий пользователей на сервере:

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 не особо знаком, поможет ли эта инструкция в обработке этой ошибки внутри триггера?
23 апр 21, 16:04    [22313283]     Ответить | Цитировать Сообщить модератору
 Re: Обработка ошибки внутри триггера  [new]
Ведущий профессионал
Member

Откуда: Санкт-Петербург
Сообщений: 117
teCa
С try catch не особо знаком, поможет ли эта инструкция в обработке этой ошибки внутри триггера?
Пора знакомиться. Да, поможет.
23 апр 21, 16:06    [22313284]     Ответить | Цитировать Сообщить модератору
 Re: Обработка ошибки внутри триггера  [new]
teCa
Member

Откуда: Москва
Сообщений: 877
Ведущий профессионал,

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

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, получаю ошибку из данного блока:

    IF (XACT_STATE()) = -1  
    BEGIN  
        PRINT  
            N'The transaction is in an uncommittable state.' +  
            'Rolling back transaction.'  
        ROLLBACK TRANSACTION;  
    END;  


В данном случае, какая из транзакций будет считаться некомментируемой? Та, которая к блоке try? Почему тогда прерывается транзакция, по которой срабатывает триггер?

Сообщение было отредактировано: 23 апр 21, 16:40
23 апр 21, 16:44    [22313296]     Ответить | Цитировать Сообщить модератору
 Re: Обработка ошибки внутри триггера  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37232
В триггере всегда ровно одна транзакция. Вернее, не более одной.

Сообщение было отредактировано: 23 апр 21, 16:44
23 апр 21, 16:51    [22313302]     Ответить | Цитировать Сообщить модератору
 Re: Обработка ошибки внутри триггера  [new]
teCa
Member

Откуда: Москва
Сообщений: 877
Гавриленко Сергей Алексеевич,

Хорошо, как мне добиться, что-бы не прерывалась транзакция, которая инициализирует срабатывание триггера?
23 апр 21, 16:54    [22313304]     Ответить | Цитировать Сообщить модератору
 Re: Обработка ошибки внутри триггера  [new]
invm
Member

Откуда: Москва
Сообщений: 9725
Бывают ошибки компиляции и ошибки выполнения.
Чтобы отлавливать и те и другие, содержимое блока try должно быть оформлено в виде DSQL

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

Commit или rollback в триггере приводит к ошибке 3609 и прекращению выполнения текущего пакета.
23 апр 21, 16:58    [22313307]     Ответить | Цитировать Сообщить модератору
 Re: Обработка ошибки внутри триггера  [new]
teCa
Member

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

Получается, что внутри триггера у меня происходит ошибка выполнения, и из вашего текста, можно сделать вывод, что и проигнорировать эту ошибку нельзя и транзакция в любом случае будет переведена в нефиксируемое состояние?
23 апр 21, 17:14    [22313316]     Ответить | Цитировать Сообщить модератору
 Re: Обработка ошибки внутри триггера  [new]
invm
Member

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

+
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
23 апр 21, 17:43    [22313344]     Ответить | Цитировать Сообщить модератору
 Re: Обработка ошибки внутри триггера  [new]
Ведущий профессионал
Member

Откуда: Санкт-Петербург
Сообщений: 117
teCa
invm,

Получается, что внутри триггера у меня происходит ошибка выполнения, и из вашего текста, можно сделать вывод, что и проигнорировать эту ошибку нельзя и транзакция в любом случае будет переведена в нефиксируемое состояние?
Напишите в начале триггера SET XACT_ABORT OFF;
23 апр 21, 18:06    [22313357]     Ответить | Цитировать Сообщить модератору
 Re: Обработка ошибки внутри триггера  [new]
Владислав Колосов
Member

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

для таких целей существуют аудиты.
23 апр 21, 20:17    [22313423]     Ответить | Цитировать Сообщить модератору
 Re: Обработка ошибки внутри триггера  [new]
teCa
Member

Откуда: Москва
Сообщений: 877
invm
teCa,

+
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
Произошла ошибка при выполнении триггера. Выполнение пакета аварийно завершено, и произведен откат пользовательской транзакции, если она существует.



Сам триггер:
+
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
27 апр 21, 12:05    [22314991]     Ответить | Цитировать Сообщить модератору
 Re: Обработка ошибки внутри триггера  [new]
invm
Member

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

Самый простой и самый неправильный способ
1.
alter database master set trustworthy on;
2.
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
27 апр 21, 12:49    [22315025]     Ответить | Цитировать Сообщить модератору
 Re: Обработка ошибки внутри триггера  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8587
Первый способ не то, чтобы неправильный, но он требует ограниченного доступа проверенного персонала к системе и система должна быть изолирована от внешней среды.
27 апр 21, 13:11    [22315033]     Ответить | Цитировать Сообщить модератору
 Re: Обработка ошибки внутри триггера  [new]
teCa
Member

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

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

Попробую разобраться с событиями.
27 апр 21, 13:49    [22315068]     Ответить | Цитировать Сообщить модератору
 Re: Обработка ошибки внутри триггера  [new]
teCa
Member

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

Хотя original_login() всё верно определяет, так, что второй вариант вполне себе меня устроит.
27 апр 21, 14:03    [22315077]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить