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

Откуда:
Сообщений: 230
Есть веб-приложение, исходный код которого доступен, но делать новые релизы затруднительно.
Это приложение умеет удалять записи в одной из табличек БД под mssql2008r2
Сейчас выяснилось, что нужно обеспечить ссылочную целостность с таблицей аж на другом сервере: если в ней нет записи с таким же айдишником, то удалять можно, иначе -- отказ.
Написал триггер, но правильно он срабатывает только при удалении запросом через ssms, а при удалении через приложение -- отказ всегда.
Почему так может быть?
+ метод

        private void AutoDelete(DbCommand cmd)
        {
            string tableName = this.GetDbTableName(SqlOperations.Delete);
            if (string.IsNullOrEmpty(tableName))
                throw new Exception("GetDbTableName method is not specified. Please specify DB table name");
            if (this.GetDbKeyFields().Length == 0)
                throw new Exception("Not one key field specified");

            string queryTemplate = @"delete from {0} where {1}";

            cmd.CommandText = string.Format(queryTemplate, tableName, this.GetFiltersByKeyFields(SqlOperations.Delete).ToString(false));
			cmd.ExecuteNonQuery();
        }


+ триггер

ALTER trigger [dbo].[tr_Requests_Check]
on [CG_78].[dbo].[Requests]
   after delete
as
begin
	print 'Триггер запустился'
	if exists(
				select 1
				from dwh_cg.dbo.vw_dwhi_Document_Specification_D
					join deleted
				on ID = cast(НомерЗаявки as int) 
	)
		begin 
		print 'Есть спецификация'
		rollback tran
		end
	else
		begin
		print 'Нету спецификации'
		end
	print 'Триггер завершился'
end


+ @@version
Microsoft SQL Server 2008 R2 (SP2) - 10.50.4042.0 (X64) Mar 26 2015 21:18:04 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)


пытался по трассировке понять, что происходит, но там мало что вразумительного. Понятно только, что триггер при удалении через приложение запускается, но как бы не завершается
+ трасса

BEGIN TRANSACTION
BEGIN TRANSACTION
select  [ItemLinks].[ID] , [ItemLinks].[entity1ID] , [ItemLinks].[entity2ID] , [ItemLinks].[entityType] , [ItemLinks].[entity1Quantity] , [ItemLinks].[entity2Quantity] , [ItemLinks].[objectId] , [ItemLinks].[lastUpdate] , [ItemLinks].[serverTimestamp]  from ItemLinks WHERE ItemLinks.entityType = 9 AND ItemLinks.entity2ID = N'44249'  group by  [ItemLinks].[ID] , [ItemLinks].[entity1ID] , [ItemLinks].[entity2ID] , [ItemLinks].[entityType] , [ItemLinks].[entity1Quantity] , [ItemLinks].[entity2Quantity] , [ItemLinks].[objectId] , [ItemLinks].[lastUpdate] , [ItemLinks].[serverTimestamp]  ORDER BY @@Identity ASC
select  [ItemLinks].[ID] , [ItemLinks].[entity1ID] , [ItemLinks].[entity2ID] , [ItemLinks].[entityType] , [ItemLinks].[entity1Quantity] , [ItemLinks].[entity2Quantity] , [ItemLinks].[objectId] , [ItemLinks].[lastUpdate] , [ItemLinks].[serverTimestamp]  from ItemLinks WHERE ItemLinks.entityType = 9 AND ItemLinks.entity2ID = N'44030'  group by  [ItemLinks].[ID] , [ItemLinks].[entity1ID] , [ItemLinks].[entity2ID] , [ItemLinks].[entityType] , [ItemLinks].[entity1Quantity] , [ItemLinks].[entity2Quantity] , [ItemLinks].[objectId] , [ItemLinks].[lastUpdate] , [ItemLinks].[serverTimestamp]  ORDER BY @@Identity ASC
insert into sfxLoadRequest (REQUEST_ID, ROW_NEW, [DATE], [DELETED])    select r.ID, 0, GETDATE(), 1 from deleted   join ProjectNeeds pn ON pn.id = deleted.Entity2ID and deleted.EntityType = 1 --BudgetResourceItemToProjectNeed = 1    join [ItemLinks] pn2r ON pn.id = pn2r.Entity1ID and pn2r.EntityType = 9 --ProjectNeedToRequest = 9   join Requests r ON r.id = pn2r.Entity2ID   where exists(select 1 from GroupRequests gr     where gr.ID = r.GroupRequestID and gr.IsAgreed = 1 and gr.IsApproved = 1)
delete from ItemLinks where ItemLinks.ID = N'421141' 
insert into sfxLoadRequest (REQUEST_ID, ROW_NEW, [DATE], [DELETED])    select r.ID, 0, GETDATE(), 1 from deleted   join ProjectNeeds pn ON pn.id = deleted.Entity2ID and deleted.EntityType = 1 --BudgetResourceItemToProjectNeed = 1    join [ItemLinks] pn2r ON pn.id = pn2r.Entity1ID and pn2r.EntityType = 9 --ProjectNeedToRequest = 9   join Requests r ON r.id = pn2r.Entity2ID   where exists(select 1 from GroupRequests gr     where gr.ID = r.GroupRequestID and gr.IsAgreed = 1 and gr.IsApproved = 1)
delete from ItemLinks where ItemLinks.ID = N'421460' 
SET FMTONLY OFF;   
SET FMTONLY OFF;   
SET nocount OFF;   
SET nocount OFF;   
print 'Триггер запустился'   
print 'Триггер запустился'   
delete from Requests where Requests.ID = N'44249' 
delete from Requests where Requests.ID = N'44030' 
ROLLBACK TRANSACTION
ROLLBACK TRANSACTION
set fmtonly on
declare @1 nvarchar(max) = N'44249' delete from Requests where ID = @1 
set fmtonly off
SET nocount OFF;   
print 'Триггер запустился'   
if exists(      select 1      from dwh_cg.dbo.vw_dwhi_Document_Specification_D       join deleted      on ID = cast(НомерЗаявки as int)    )    
print 'Нету спецификации'
print 'Триггер завершился'  
    declare @1 nvarchar(max) = N'44249'  delete from Requests where ID = @1  

5 авг 15, 15:06    [17978944]     Ответить | Цитировать Сообщить модератору
 Re: привередливый триггер  [new]
iap
Member

Откуда: Москва
Сообщений: 46999
MyNiGoo
нужно обеспечить ссылочную целостность с таблицей аж на другом сервере
Тут все таблицы на одном сервере, но в разных базах.
В какой таблице ID, а в какой - [НомерЗаявки]?
Почему не используете алиасы?
5 авг 15, 15:25    [17979068]     Ответить | Цитировать Сообщить модератору
 Re: привередливый триггер  [new]
iap
Member

Откуда: Москва
Сообщений: 46999
iap
MyNiGoo
нужно обеспечить ссылочную целостность с таблицей аж на другом сервере
Тут все таблицы на одном сервере, но в разных базах.
В какой таблице ID, а в какой - [НомерЗаявки]?
Почему не используете алиасы?
Хотя, возможно, vw_dwhi_Document_Specification_D - это view с SELECTами с другого сервера?
5 авг 15, 15:27    [17979085]     Ответить | Цитировать Сообщить модератору
 Re: привередливый триггер  [new]
MyNiGoo
Member

Откуда:
Сообщений: 230
iap, vw_dwhi_Document_Specification_D -- представление, которое обращается к LinkedServer
Requests.Id и vw_dwhi_Document_Specification_D.НомерЗаявки

Алиасы использую, но опустил в данном случае по причине разного набора полей. Прежде, чем публиковать код, надо было написать, конечно
5 авг 15, 15:29    [17979094]     Ответить | Цитировать Сообщить модератору
 Re: привередливый триггер  [new]
o-o
Guest
iap
Тут все таблицы на одном сервере, но в разных базах.
Хотя, возможно, vw_dwhi_Document_Specification_D - это view с SELECTами с другого сервера?

или синоним.
глядящий как раз на линкованный сервер
5 авг 15, 15:30    [17979098]     Ответить | Цитировать Сообщить модератору
 Re: привередливый триггер  [new]
Владислав Колосов
Member

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

триггером это нельзя делать. Напишите сервис-прослойку.
5 авг 15, 15:36    [17979153]     Ответить | Цитировать Сообщить модератору
 Re: привередливый триггер  [new]
MyNiGoo
Member

Откуда:
Сообщений: 230
Владислав Колосов, это вряд ли. Какие аргументы?
5 авг 15, 15:52    [17979294]     Ответить | Цитировать Сообщить модератору
 Re: привередливый триггер  [new]
Maxx_UA
Guest
А вообще оригинально ..
	if exists(
				select 1
				from dwh_cg.dbo.vw_dwhi_Document_Specification_D
					join deleted
				on ID = cast(НомерЗаявки as int) 


При условии ,что
delete from Requests where Requests.ID = N'44249' 

Мало того,что алисов нет ,так еще и дурную работу делать сервер заставляете.
5 авг 15, 16:07    [17979429]     Ответить | Цитировать Сообщить модератору
 Re: привередливый триггер  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7868
MyNiGoo, Вы будете держать блокировки, пока триггер не отработает. При недоступности внешнего сервера вообще можно получить вечный таймаут. Если надежность и качество работы не волнует - тогда вперёд :)
5 авг 15, 16:09    [17979443]     Ответить | Цитировать Сообщить модератору
 Re: привередливый триггер  [new]
Glory
Member

Откуда:
Сообщений: 104760
MyNiGoo
пытался по трассировке понять, что происходит, но там мало что вразумительного. Понятно только, что триггер при удалении через приложение запускается, но как бы не завершается

А ваша трасса что кроме запуска ловит то ? Ошибки/исключения скорее всего нет ? И поэтому и кажется, что "но как бы не завершается" ?
5 авг 15, 16:09    [17979444]     Ответить | Цитировать Сообщить модератору
 Re: привередливый триггер  [new]
MyNiGoo
Member

Откуда:
Сообщений: 230
Maxx_UA, это известный факт, да) заставить разработчиков переделывать уже не представляется возможным((
Владислав Колосов, к сожалению, как минимум на данный момент доступны только средства sql server
Glory, спасибо, что обратились к исходной постановке вопроса. Подписан на RPC:Completed, SP:StmtCompletedSQL, SQL:BatchCompleted. Скажите, пожалуйста, что надо бы добавить?
5 авг 15, 16:19    [17979551]     Ответить | Цитировать Сообщить модератору
 Re: привередливый триггер  [new]
Glory
Member

Откуда:
Сообщений: 104760
MyNiGoo
Glory, спасибо, что обратились к исходной постановке вопроса. Подписан на RPC:Completed, SP:StmtCompletedSQL, SQL:BatchCompleted. Скажите, пожалуйста, что надо бы добавить?

все это, только со ...Started и Exceptions

Сообщение было отредактировано: 5 авг 15, 16:22
5 авг 15, 16:21    [17979572]     Ответить | Цитировать Сообщить модератору
 Re: привередливый триггер  [new]
o-o
Guest
MyNiGoo
Написал триггер, но правильно он срабатывает только при удалении запросом через ssms, а при удалении через приложение -- отказ всегда.
Почему так может быть?

а как мапится пользователь на удаленный сервер, сам в себя?
может, в студии вы запускаете код под другим логином,
а в той таблице на удаленном сервере логин приложения видит не все строки?
может, он их видит не все даже на этом сервере, у вас ведь представление, а не просто исходная таблица?

у меня сделано спец. представление, скрывающее от товарища l2 истинные значения таблицы.
на картинке:
1 окно -- селект делает тот пользователь, против к-ого все задумано
2 окно -- оттуда же выбираю под своей учеткой
3 окно -- текст этого вью

К сообщению приложен файл. Размер - 71Kb
5 авг 15, 16:42    [17979746]     Ответить | Цитировать Сообщить модератору
 Re: привередливый триггер  [new]
MyNiGoo
Member

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

Триггер запустился
Есть спецификация
Триггер завершился
Msg 3609, Level 16, State 1, Line 2
Транзакция завершилась в триггере. Выполнение пакета прервано.


посмотрел в профайлере, сервер-то справедливо мне указывает
Exception | Error: 3903, Severity: 16, State: 1
User Error Message | Запрос ROLLBACK TRANSACTION не имеет соответствующей инструкции BEGIN TRANSACTION.

Картинка с другого сайта.

и

Картинка с другого сайта.

там по коду метод класса обертывается в transaction begin / commit. Как бы мне её прервать покорректнее?
Вот тут про raiserror писали, однако эффект тот же. При 11 уровне запись удаляется в любом случае, при 20 -- никогда
5 авг 15, 16:58    [17979855]     Ответить | Цитировать Сообщить модератору
 Re: привередливый триггер  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37050
Корректно - в триггере делать raiserror а "снаружи" уже принимать решение о коммите или откате.
5 авг 15, 17:00    [17979863]     Ответить | Цитировать Сообщить модератору
 Re: привередливый триггер  [new]
Glory
Member

Откуда:
Сообщений: 104760
Ну так во второй трассе нет события завершения if exists
Вы уверены, что все следующие команды трассы из того же коннекта ?
5 авг 15, 17:03    [17979880]     Ответить | Цитировать Сообщить модератору
 Re: привередливый триггер  [new]
MyNiGoo
Member

Откуда:
Сообщений: 230
для ясности повторил с rollback tran

через интерфейс
удаление неудаляемой записи
удаление удаляемой записи

через ssms
удаление неудаляемой записи
удаляемая тихонько удаляется
5 авг 15, 17:19    [17979972]     Ответить | Цитировать Сообщить модератору
 Re: привередливый триггер  [new]
MyNiGoo
Member

Откуда:
Сообщений: 230
Glory, события завершения if exists действительно нет.

Гавриленко Сергей Алексеевич, возможности нет снаружи решение принять((
5 авг 15, 17:21    [17979985]     Ответить | Цитировать Сообщить модератору
 Re: привередливый триггер  [new]
MyNiGoo
Member

Откуда:
Сообщений: 230
очень, конечно, непонятно, почему сервер возвращает
Exception | Error: 3903, Severity: 16, State: 1
User Error Message | Запрос ROLLBACK TRANSACTION не имеет соответствующей инструкции BEGIN TRANSACTION.
почти все примеры на триггеры delete иллюстрируют откат с помощью rollback, а в моем случае - ошибка.
почему сервер при выполнении не понимает, какую транзакцию надо откатить?
даже если написать подряд
BEGIN TRANSACTION
ROLLBACK TRANSACTION

все равно выкидывает ошибку
6 авг 15, 11:11    [17982680]     Ответить | Цитировать Сообщить модератору
 Re: привередливый триггер  [new]
Glory
Member

Откуда:
Сообщений: 104760
MyNiGoo
даже если написать подряд
BEGIN TRANSACTION
ROLLBACK TRANSACTION


все равно выкидывает ошибку

Написать где ?

Перед COMMIT/ROLLBACK TRANSACTION вообще надо проверять наличие транзакции
6 авг 15, 11:27    [17982786]     Ответить | Цитировать Сообщить модератору
 Re: привередливый триггер  [new]
MyNiGoo
Member

Откуда:
Сообщений: 230
Glory, а разве так бывает? хотя бы неявная, но должна быть, наверное
и почему тогда такой код заканчивается той же ошибкой?
ALTER trigger [dbo].[tr_Requests_Check]
on [CG_78].[dbo].[Requests]
   after delete
as
begin
	print 'Триггер запустился'
	if exists(
				select 1
				from dwh_cg.dbo.vw_dwhi_Document_Specification_D
					join deleted
				on ID = cast(НомерЗаявки as int) 
	)
		begin 
		print 'Есть спецификация'
		BEGIN TRANSACTION      --<-----------<--------<--------
		ROLLBACK TRANSACTION --<-----------<--------<--------
		end
	else
		begin
		print 'Нету спецификации'
		end
	print 'Триггер завершился'
end
6 авг 15, 11:35    [17982848]     Ответить | Цитировать Сообщить модератору
 Re: привередливый триггер  [new]
o-o
Guest
MyNiGoo,
может, переписать через instead of delete?
alter trigger [dbo].[tr_Requests_Check]
       on [dbo].[Requests]
instead of delete
as
begin
       set nocount on;
       	if exists(
				select 1
				from dwh_cg.dbo.vw_dwhi_Document_Specification_D t
					join deleted d on t.ID = d.id 
		     ) 
 
              raiserror(N'Есть спецификация',16 ,1)
       else

              delete from [dbo].[Requests]
              where id in (select id from deleted)
end
6 авг 15, 11:36    [17982855]     Ответить | Цитировать Сообщить модератору
 Re: привередливый триггер  [new]
Glory
Member

Откуда:
Сообщений: 104760
MyNiGoo
Glory, а разве так бывает?

Бывает

MyNiGoo
хотя бы неявная, но должна быть, наверное

То, что транзакция началась не означает, что к моменту вашего COMMIT/ROLLBACK она еще существует

MyNiGoo
и почему тогда такой код заканчивается той же ошибкой?

Потому, что вы по прежнему не проверяте перед COMMIT/ROLLBACK TRANSACTION наличие транзакции
6 авг 15, 11:44    [17982910]     Ответить | Цитировать Сообщить модератору
 Re: привередливый триггер  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7868
Никаких ROLLBACK не должно быть в триггере. BEGIN TRAN также.
6 авг 15, 12:05    [17983061]     Ответить | Цитировать Сообщить модератору
 Re: привередливый триггер  [new]
o-o
Guest
Владислав Колосов
Никаких ROLLBACK не должно быть в триггере. BEGIN TRAN также.

это в триггере ТС можно обойтись без (но не хочет вроде вариант с instead of)
а в логон-триггере, например, почему нет?
как не пускать кого-то, если удовлетворяет критерию непускательства?
без роллбэка как организуете?
а DDL-триггер, не дающий что-то дропнуть, как без роллбэка написать?
6 авг 15, 12:23    [17983185]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить