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

Откуда:
Сообщений: 7
Доброго времени суток.

Имеем:
1. 2 сервера MS SQL: 2008 R2 и 2014
2. На сервере 2008 настроена БД DB1, с которой достаточно активно работает софтина
3. Настроена репликация транзакций 2008 (DB1) -> 2014 (DB2)
Все это работало отлично до появления другой софтины, которую по политическим соображениям нельзя было вешать на 2008 сервер. И было реализовано следующее решение по синхронизации данных:
4. Создана отдельная БД DB3 на сервере 2014 для работы второй софтины.
5. В БД подписчика для таблицы DB2.TableRepl был создан триггер AFTER INSERT, UPDATE, в котором данные переносились при определенных условиях в таблицу DB3.TableSoft
Некоторое время все работало исправно и красиво. Полная синхронизация между софтинами на уровне БД. Бизнес доволен, ИТ спокоен. Но беда подкралась из ниоткуда! В один "прекрасный" момент репликация встала. Стал выяснять, обнаружил, что проблема в INSERT INTO DB3.Table в триггере.

Скрипты создания таблиц:
 -- База данных - подписчик репликации
use [DB2]
go
CREATE TABLE [dbo].[TableRepl](
	[id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
	...
 CONSTRAINT [PK_TableRepl] PRIMARY KEY NONCLUSTERED 
(
	[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]


 -- БД - отдельная для второй софтины
use [DB3]
go
CREATE TABLE [dbo].[TableSoft](
	[id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
	...
 CONSTRAINT [PK_TableSoft] PRIMARY KEY NONCLUSTERED 
(
	[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]


Триггер:
use [DB2]
go
ALTER TRIGGER [dbo].[trTableReplChange]
   ON [dbo].[TableRepl]
   after INSERT, update
AS 
BEGIN
-- выполняем действия триггера
	insert into [db3].[dbo].[TableSoft]
		([DateLoad])
		values (getdate())
end


Проблема собственно в этом insert и именно при репликации, т.к. просто запуская update таблицы DB2.TableRepl все отрабатывает корректно и даже сообщений не выдает. Первая мысль была: отсутствие прав у пользователя, от которого выполняется репликация, проверил - есть права. Пробовал сохранять и восстанавливать @@Identity - ничем не спасло.

Господа форумчане, помогите справиться с проблемой, пожалуйста!
17 авг 14, 02:16    [16451388]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с INSERT в Триггере  [new]
Crimean
Member

Откуда:
Сообщений: 13147
сообщение какое? небось, проблемы с identity?
17 авг 14, 02:58    [16451438]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с INSERT в Триггере  [new]
aleks2
Guest
1. Нафига триггер то? Он же ваще БЕЗ смысла.
2. Репликация так и говорит: "я фстала!". Али какое более вменяемое сообщение есть?
17 авг 14, 12:28    [16451861]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с INSERT в Триггере  [new]
Bercof
Member

Откуда:
Сообщений: 7
Проблема действительно судя по сообщениям с IDENTITY:
В задаче
Не удалось завершить задание. Запуск задания был произведен Расписание 30 (Расписание агента репликации.). Последним выполнявшимся шагом был шаг 1 (Запустить агента.).

Последний ошибочный шаг
Выполняется от имени пользователя: afpb\sqlserv.Произошел сбой в агенте репликации. Дополнительные сведения см. в журнале в сообщении для предыдущего шага задания или в мониторе репликации. Шаг завершился с ошибкой.

Предыдущий шаг задания
2014-08-17 17:00:01.651
42000 ─ы  ёЄюысЎр шфхэЄшЇшърЄюЁют ЄрсышЎ√ "TableSoft"  тэюх чэрўхэшх эхюсїюфшью єърч√трЄ№ т Єхї ёыєўр ї, ъюуфр ышсю IDENTITY_INSERT шьххЄ чэрўхэшх ON, ышсю ъюуфр яюы№чютрЄхы№ ЁхяышърЎшш юёє∙хёЄты хЄ тёЄртъє т ёЄюысхЎ шфхэЄшЇшърЄюЁют, юЄьхўхээ√щ ъръ NOT FOR REPLICATION. 545
...
2014-08-17 17:01:17.777 ╩юф ёююс∙хэш  рухэЄр 545. ─ы  ёЄюысЎр шфхэЄшЇшърЄюЁют ЄрсышЎ√ "TableSoft"  тэюх чэрўхэшх эхюсїюфшью єърч√трЄ№ т Єхї ёыєўр ї, ъюуфр ышсю IDENTITY_INSERT шьххЄ чэрўхэшх ON, ышсю ъюуфр яюы№чютрЄхы№ ЁхяышърЎшш юёє∙хёЄты хЄ тёЄртъє т ёЄюысхЎ шфхэЄшЇшърЄюЁют, юЄьхўхээ√щ ъръ NOT FOR REPLICATION.
2014-08-17 17:01:17.793 Category:COMMAND
Source: ╩юьрэфр ё ю°шсъющ
Number:
Message: if @@trancount > 0 rollback tran
2014-08-17 17:01:17.793 Category:NULL
Source: Microsoft SQL Server Native Client 11.0
Number: 545
Message: ─ы  ёЄюысЎр шфхэЄшЇшърЄюЁют ЄрсышЎ√ "TableSoft"  тэюх чэрўхэшх эхюсїюфшью єърч√трЄ№ т Єхї ёыєўр ї, ъюуфр ышсю IDENTITY_INSERT шьххЄ чэрўхэшх ON, ышсю ъюуфр яюы№чютрЄхы№ ЁхяышърЎшш юёє∙хёЄты хЄ тёЄртъє т ёЄюысхЎ шфхэЄшЇшърЄюЁют, юЄьхўхээ√щ ъръ NOT FOR REPLICATION.



Пробовал восстанавливать таким образом:

use [DB2]
go
ALTER TRIGGER [dbo].[trTableReplChange]
   ON [dbo].[TableRepl]
   after INSERT, update
AS 
BEGIN
DECLARE
     @Idt int,
     @s varchar(255)

-- запомним значение глобальной переменной @@Identity
SET @Idt = @@Identity

-- выполняем действия триггера
	insert into [db3].[dbo].[TableSoft]
		([DateLoad])
		values (getdate())
-- восстанавливаем значение глобальной переменной @@Identity
IF @Idt IS NOT NULL
 BEGIN
  Set @s = 'Select Identity(Int, ' +
           Cast(@Idt As VarChar(10)) +' ,1)  As idt Into #Tmp'
  Execute(@s)
 END
end


Не помогло. Подскажите, пожалуйста, как с этим справиться?


To aleks2
В триггере выполняется набор проверок и только при определенных условиях производится изменение базы DB3. Суть в том, чтобы синхронизировать разные по структуре базы для разных программ. Если есть какое-то более элегантное решение, с удовольствием прислушаюсь и реализую!
18 авг 14, 09:56    [16454177]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с INSERT в Триггере  [new]
aleks2
Guest
Bercof
Если есть какое-то более элегантное решение, с удовольствием прислушаюсь и реализую!

Убрать IDENTITY из TableSoft. Ибо оно там нафег не фперлось.

ЗЫ. Тока я сумлеваюсь, что тут дело в этом...

>>Message: ─ы ёЄюысЎр шфхэЄшЇшърЄюЁют ЄрсышЎ√ "TableSoft" тэюх чэрўхэшх эхюсїюфшью єърч√трЄ№ т Єхї ёыєўр ї, ъюуфр ышсю IDENTITY_INSERT шьххЄ чэрўхэшх ON, ышсю ъюуфр яюы№чютрЄхы№ ЁхяышърЎшш юёє∙хёЄты хЄ тёЄртъє т ёЄюысхЎ шфхэЄшЇшърЄюЁют, юЄьхўхээ√

Вот тут, на замечательном олбанском сервер явно возмущается попыткой вставить в TableSoft чего-то в поле IDENTITY, без
set IDENTITY_INSERT ON.

Что-то ты недоговариваешь, дарагой страдалец.
18 авг 14, 10:12    [16454280]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с INSERT в Триггере  [new]
invm
Member

Откуда: Москва
Сообщений: 9837
Ваша проблема в NOT FOR REPLICATION для id в TableSoft.
Когда триггер запущен в контексте агента репликации, т.е. sessionproperty('replication_agent') = 1, при вставке в TableSoft необходимо явно указывать значения столбца id.
18 авг 14, 10:17    [16454315]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с INSERT в Триггере  [new]
Crimean
Member

Откуда:
Сообщений: 13147
поскольку identity и там и там а в триггере это не обрабатывается - последствия логичны
18 авг 14, 10:34    [16454406]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с INSERT в Триггере  [new]
Bercof
Member

Откуда:
Сообщений: 7
Попробовал добавить перед вставкой:
set IDENTITY_INSERT [db3].[dbo].[TableSoft] ON;

Эффект такой же как и без.

Пробовал указать точный ID:
insert into [db3].[dbo].[TableSoft]
	([id], [DateLoad])
	values ((select max(id)+ 1 as id from [db3].[dbo].[TableSoft]),getdate())

Результат - все отработало. НО! таким образом летит порядок идентификаторов в таблице TableSoft и при последующих insert возникает ошибка:
Msg 2627, Level 14, State 1, Line 18
Нарушено "PK_TableSoft" ограничения PRIMARY KEY. Не удается вставить повторяющийся ключ в объект "dbo.TableSoft". Повторяющееся значение ключа: (4).

Выполнение данной инструкции было прервано.
Этот момент как-то лечится?


aleks2
Что-то ты недоговариваешь, дарагой страдалец.

Разве то, чего не знаю...

aleks2
Убрать IDENTITY из TableSoft. Ибо оно там нафег не фперлось.

Править базы DB1 и DB3 запрещено по политическим соображениям.
18 авг 14, 11:39    [16454792]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с INSERT в Триггере  [new]
invm
Member

Откуда: Москва
Сообщений: 9837
Bercof
Этот момент как-то лечится?
Bercof
Править базы DB1 и DB3 запрещено по политическим соображениям.
Причину происходящего я вам уже объяснил. Теперь решайте, что для вас важнее: "шашечки" или "ехать". Потому что "править базы" так или иначе придется.
18 авг 14, 12:04    [16454937]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с INSERT в Триггере  [new]
invm
Member

Откуда: Москва
Сообщений: 9837
Хотя вот вам парочка вариантов:
1. Триггер складывает данные в промежуточную таблицу в БД2. Эта таблица штатным образом реплицируется в БД3.
2. Триггер передает данные в локальный Service Broker в очередь с внутренней активацией. Процедура активации переносит данные в БД3.
18 авг 14, 12:46    [16455261]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с INSERT в Триггере  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4901
У репликации есть настройки, связанные с запуском триггеров и вставкой identity -- вы читали про них? И правильно ли они у вас настроены?
18 авг 14, 14:28    [16456322]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с INSERT в Триггере  [new]
Bercof
Member

Откуда:
Сообщений: 7
Все спасибо большое за советы.

To a_voronin,
Не знал, буду устранять пробелы.

To invm,
Да, жаль, что стандартными методами этого не обойти.
18 авг 14, 14:42    [16456468]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с INSERT в Триггере  [new]
invm
Member

Откуда: Москва
Сообщений: 9837
Bercof
Да, жаль, что стандартными методами этого не обойти.
Стандартный метод - убрать NOT FOR REPLICATION с IDENTITY в TableSoft. Эта опция там вообще ни к чему, т.к. таблица не включена в репликацию. И если "политические" доводы перевесят, придется вам приседать и плясать с бубном.
18 авг 14, 15:05    [16456649]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с INSERT в Триггере  [new]
Bercof
Member

Откуда:
Сообщений: 7
Всем спасибо. Таки сошлись на сдвиге identity
DBCC CHECKIDENT (TableSoft, RESEED, @Ident)


Нашел как определить, выполняется ли insert в процессе репликации или нет: Тут
21 авг 14, 11:11    [16472596]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с INSERT в Триггере  [new]
Glory
Member

Откуда:
Сообщений: 104751
Bercof
Нашел как определить, выполняется ли insert в процессе репликации или нет: Тут

Help - Replicating Identity Columns
21 авг 14, 11:14    [16472639]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить