Как отловить ошибки в тригере

← →
Helen (2007-04-05 10:45) [0]

Добрый день. У меня такая проблемс: есть приложение (написано не мной), которое пишет данные в MS SQL 2000. У таблице, в которую пишутся данные, создан тригер instead of insert. Данные пишутся-пишутся, а потом вдруг приложение выдает ошибку:
Ошибка записи в БД: Could not find prepared statement with handle -1.
И все, данные перестают писаться, перезапуск программы не помогает. После удаления и повторного создания тригера все опять работает, т.е. дело в тригере. Вопрос: как узнать, по какой причине он зависает?

← →
Jan1 (2007-04-05 10:48) [1]

некрасиво спамить несколько форумов сразу...

← →
Helen (2007-04-05 10:50) [2]

просто нужно очень быстро исправить ошибку

← →
ANB © (2007-04-05 11:24) [3]

> Helen (05.04.07 10:45)

1. Сделай триггер пустым - убедись что ничего не падает (есть подозрение, что не триггер виноват)
2. Если таки проблема в триггере, то :
а) запости его исходник
б) раставь по телу триггера принты и смотри, что получается (тута удобнее будет инициировать работу триггера в QA, добавляя записи в таблицу).

← →
Helen (2007-04-05 12:05) [4]

инициировать работу триггера в QA, добавляя записи в таблицу пробовала, все проходило успешно
Дело в том, что в основную таблицу пишется от 5 до 50 записей в секунду

← →
ЮЮ © (2007-04-05 12:20) [5]

> Дело в том, что в основную таблицу пишется от 5 до 50 записей
> в секунду

И все клиенты такие скорострельныые?
Запросы на запись у "автоматчика" параметрические?
А то между Query.Prepare; и Query.ExecSQL казалось бы Prepared запрос на серевере уже и не Prepared, на что наводит сообщение "Could not find prepared statement" Правда "with handle -1" подозрителен.

← →
ANB © (2007-04-05 14:54) [6]

> инициировать работу триггера в QA, добавляя записи в таблицу
> пробовала, все проходило успешно
> Дело в том, что в основную таблицу пишется от 5 до 50 записей
> в секунду

Тады проблемы не в триггере а в клиенте (о чем говорит и характер ошибки). Для очистки совести заглуши триггер и попробуй.

← →
Helen (2007-04-05 16:42) [7]

>Тады проблемы не в триггере а в клиенте (о чем говорит и характер ошибки). Для очистки совести заглуши триггер и попробуй.

Я имела ввиду, что такой поток не могу создать руками. Если проблема не в тригере, то почему при его удалении и вновь создании опять начинается запись в таблицу. Пока не удалишь тригер программа отказывается писать данные, хотя соединение с сервером устанавливается?

>ЮЮ © (05.04.07 12:20) [5]
Такой скорострельный клиент только один. Программа собирает показания датчиков со всех компьютеров в сети и передает их в SQL-сервер.
>Запросы на запись у "автоматчика" параметрические? -не знаю, программа не мной писана.

← →
Jan1 (2007-04-05 16:45) [8]

> Helen (05.04.07 16:42) [7]

ошибка легко воспроизводима?

← →
Helen (2007-04-05 16:49) [9]

в смысле легко воспроизводима?

← →
Jan1 (2007-04-05 16:52) [10]

> Helen (05.04.07 16:49) [9]
>
> в смысле легко воспроизводима?

в смысле эта ошибка появляется сама по себе и выяснить когда нет возможности или Вы всегда можете ее повторить?

← →
Helen (2007-04-05 17:12) [11]

Она появляется сама по себе. Сегодня почти целый день данные нормально писались. Только что появилась ошибка.

← →
Helen (2007-04-05 17:25) [12]

Exception Error: 8179, Severity: 16, State: 1
что-то не могу найти, что за ошибка

← →
Helen (2007-04-05 17:26) [13]

Нашла:Could not find prepared statement with handle %d.

← →
Jan1 (2007-04-05 17:37) [14]

1. Версия сервера: select @@version
2. Триггер покажите.

← →
Helen (2007-04-06 09:00) [15]

1.
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)

2.CREATE trigger LoadAvarRow on Table1 instead of insert as SET DATEFORMAT dmy DECLARE @Err varchar(3), @ErType varchar(1) DECLARE @Region varchar(3), @AtsNum varchar(3), @Adr1 varchar(3), @Adr2 varchar(3), @Adr3 varchar(3) DECLARE @Date varchar(17), @DateIns varchar(17) SELECT @DateIns=ins.DATTIME, @Region=ins.REGION, @AtsNum=ins.ATSNUM, @Adr1=ins.ADR1, @Adr2=ins.ADR2, @Adr3=ins.ADR3, @Err=ERCODE, @ErType=ins.ERTYPE FROM inserted as ins SET @Date=SUBSTRING(@DateIns, 7, 2)+"."+SUBSTRING(@DateIns, 4, 2)+"."+SUBSTRING(@DateIns, 1, 2)+" "+SUBSTRING(@DateIns, 10, 8) IF @Err IN (...) BEGIN rollback TRAN RETURN END IF @Err IN (...) BEGIN IF not EXISTS(Select * From temp_CurrAvar Where REGION=@Region and ATSNUM=@AtsNum and ADR1=@Adr1 and ADR2=@Adr2 and ErrBegin=@Err and DateEnd is Null) BEGIN INSERT INTO temp_CurrAvar (REGION, ATSNUM, ADR1, ADR2, ErrBegin, DateBegin) VALUES(@Region, @AtsNum, @Adr1, @Adr2, @Err, @Date) INSERT INTO Table1 VALUES (@DateIns, @Err, @Region, @AtsNum, @Adr1, @Adr2, @Adr3, @ErType) COMMIT TRAN RETURN END else begin rollback TRAN RETURN end END IF @Err IN (...) BEGIN DECLARE @ErrBegin varchar(3) SET @ErrBegin=CASE @Err WHEN "150" THEN "151" WHEN "133" THEN "131" WHEN "132" THEN "130" WHEN "160" THEN "127" WHEN "163" THEN "134" WHEN "165" THEN "136" WHEN "162" THEN "129" WHEN "161" THEN "128" END IF EXISTS(Select * From temp_CurrAvar Where (REGION=@Region) and (ATSNUM=@AtsNum) and (ADR1=@Adr1) and (ADR2=@Adr2) and (ErrBegin=@ErrBegin) and (ErrEnd is Null)) BEGIN UPDATE temp_CurrAvar SET DateEnd=@Date, ErrEnd=@Err WHERE REGION=@Region and ATSNUM=@AtsNum and ADR1=@Adr1 and ADR2=@Adr2 and ErrBegin=@ErrBegin and (ErrEnd is Null) INSERT INTO MO_ERLST VALUES (@DateIns, @Err, @Region, @AtsNum, @Adr1, @Adr2, @Adr3, @ErType) COMMIT TRAN RETURN END ELSE BEGIN rollback TRAN RETURN END END IF @Err IN (...) BEGIN INSERT INTO Table2 (DATTIME, ERCODE, REGION, ATSNUM, ADR1, ADR2, ADR3) VALUES (@Date, @Err, @Region, @AtsNum, @Adr1, @Adr2, @Adr3 ) INSERT INTO Table1 VALUES (@DateIns, @Err, @Region, @AtsNum, @Adr1, @Adr2, @Adr3, @ErType) COMMIT TRAN RETURN END IF @Err IN (...) BEGIN INSERT INTO temp_139 (REGION, ATSNUM, ADR1, ADR2, ADR3, ERCODE, ERCOUNT, DATTIME) VALUES(@Region, @AtsNum, @Adr1, @Adr2, @Adr3, @Err, 1, @Date) INSERT INTO Table1 VALUES (@DateIns, @Err, @Region, @AtsNum, @Adr1, @Adr2, @Adr3, @ErType) COMMIT TRAN RETURN END INSERT INTO Table1 VALUES (@DateIns, @Err, @Region, @AtsNum, @Adr1, @Adr2, @Adr3, @ErType) COMMIT TRAN RETURN

← →
Jan1 (2007-04-06 11:13) [16]

1. в начало триггера - set nocount on
2. Перед COMMIT TRAN и rollback TRAN делать проверку на наличие активной транзакции @@trancount
3.
> SET @Date=SUBSTRING(@DateIns, 7, 2)+"."+SUBSTRING(@DateIns,
> 4, 2)+"."+SUBSTRING(@DateIns, 1, 2)+" "+SUBSTRING(@DateIns,
> 10, 8)

вот тут я не понял - зачем такой изврат?
4. Меня бы мой начальник прибил, если бы я так таблицы и поля называл...Читать невозможно.

← →
Helen (2007-04-06 11:43) [17]

>4. Меня бы мой начальник прибил, если бы я так таблицы и поля называл...Читать невозможно.

Я бы тоже прибила того, кто создавал эти таблицы

Тут случайно выяснилась еще одна особенность: если создан тригер, то после запуска программы, она отказывается писать в SQL-сервер. Удаляю тригер, запись начинается. Создаю тригер, запись продолжается без проблем.

2. Перед COMMIT TRAN и rollback TRAN делать проверку на наличие активной транзакции @@trancount
Если есть активная транзакция, то что?

← →
ANB © (2007-04-06 11:56) [18]

> то после запуска программы, она отказывается писать в SQL-
> сервер.

А если создан триггер, но пустой ?

> SELECT @DateIns=ins.DATTIME, @Region=ins.REGION, @AtsNum=ins.
> ATSNUM, @Adr1=ins.ADR1, @Adr2=ins.ADR2, @Adr3=ins.ADR3,
> @Err=ERCODE, @ErType=ins.ERTYPE
> FROM inserted as ins

Вообще то inserted - набор данных и строк в нем может быть несколько. А триггер обрабатывает только одну.

← →
Helen (2007-04-06 12:00) [19]

>Вообще то inserted - набор данных и строк в нем может быть несколько. А триггер обрабатывает только одну
Записи приходят только по одной.

← →
Helen (2007-04-06 12:05) [20]

>А если создан триггер, но пустой ?

если создан пустой тригер, то после запуска программы записи пишутся нормально

← →
Jan1 (2007-04-06 12:10) [21]

> Записи приходят только по одной.

не факт. это важно если вы будет работать с данными как набором.

← →
ЮЮ © (2007-04-06 12:11) [22]

> Тут случайно выяснилась еще одна особенность: если создан
> тригер, то после запуска программы, она отказывается писать
> в SQL-сервер. Удаляю тригер, запись начинается. Создаю тригер,
> запись продолжается без проблем.

Очевидно причиной служат записи в temp_CurrAvar
По ним, очевидно, и предполагается выявлять ошибочные данные
При наличии записи вызывается rollback TRAN

Честно говоря, не понимаю, зачеи здесь RoollBack и Commit присутствуют, если BEGIN TRAN нет? Наверное, такой подход и называют "бизнес-логика расположена на сервере" :)

← →
Helen (2007-04-06 12:20) [23]

Честно говоря, не понимаю, зачеи здесь RoollBack и Commit присутствуют, если BEGIN TRAN нет? Наверное, такой подход и называют "бизнес-логика расположена на сервере" :)
Когда пользователь начинает вставку данных, сервер автоматически начинает транзакцию, в которой и выполняется тригер. В теле транзакции разработчик может реализовывать различные алгоритмы...В конце концов, работа тригера сводится либо к фиксации, либо к откату транзакции.

← →
ANB © (2007-04-06 12:23) [24]

> Helen (06.04.07 12:20) [23]

Тады без вариантов придется логировать, чтобы выяснить, где триггер клинит.

← →
Helen (2007-04-06 12:26) [25]

задам глупый вопрос, но... как это сделать?

> алгоритмы...

C таким триггером сложновато будет выполнить такое:

BEGIN TRAN
INSERT INTO Table1 VALUES(...)
...
INSERT INTO Table1 VALUES(...)
...
IF ... COMMIT TRAN ELSE rollback TRAN

ибо решение о COMMIT/rollback принял за меня триггер.
Более того, триггер не позволит выполнить и пакетную вставку типа

INSERT into Table1
SELECT * FROM ... WHERE ...
ибо вставляеят только первую запись из пакета

...В конце концов, работа тригера сводится либо к фиксации, либо к откату транзакции.
Максимум, что может позволить триггер - вызвыть EXEPTION, но никак не подтверждать/откатывать транзакцию, которрую НЕ ОН СТАРТОВАЛ.

А все работать перестает из-за наличия записей в temp_CurrAvar и логике триггера. Он не работать перестает, а делает Rollback, ИМХО.

P/S/ Точне до [26] (не все скопировал в буфер)

> сервер автоматически начинает транзакцию

Также также он её автоматически и закончит. Не дело триггера ввязвываться а это тонкое дело.
Более того, cервер ли?

> В теле транзакции разработчик может реализовывать различные
> алгоритмы...

> задам глупый вопрос, но... как это сделать?

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

В триггере так много косяков, что удивительно как он вообще работает.

ЗЫ. Кстати, оракл запрещает триггерам манипулировать транзакциями. Судя по этому коду - правильно делает.

ЗЫЫ. Хотя savepoint/rollback to - непонятно зачем запрещены.

← →
Helen (2007-04-06 12:53) [29]

Из последних сообщений понимаю, что у меня неправильный подход к написанию тригеров. Так, ткнете носом, как надо делать?
Первый же вопрос: тригер for insert, в теле делаем проверку данных, данные нас не удовлтворяют, хотим отменить вставку, как (не используя rollback tran)?

← →
Helen (2007-04-06 12:54) [30]

>В триггере так много косяков, что удивительно как он вообще работает.
Расскажите где косяки, а то всю жизнь и буду косячить

on Table1 instead of insert

У тебя триггер, заменяющий операцию вставки. Просто не делай ничего - и всех делов то.

> как (не используя rollback tran)?

Достаточно возбудить "серьезное" исключене. Например:

CREATE TRIGGER U_WorkLines_OnInsert ON dbo.U_WorkLines INSTEAD OF INSERT AS BEGIN DECLARE @Admin int SELECT @Admin = dbo.UuaIsUserAdmin() IF (@Admin <> 1) AND EXISTS(SELECT WorkTermActivity FROM inserted WHERE dbo.UuaWorkTermActivities_Id(WorkTermActivity) = 0) BEGIN RAISERROR("#1: U_WorkLines_OnInsert: This record cannot be inserted", 16, 1) RETURN END INSERT INTO U_WorkLines SELECT * FROM inserted END

Если проверять надо "по записям", создать CURSOR по inserted и пробещать ао всему inserted по записям

← →
Helen (2007-04-10 10:34) [33]

>ЮЮ © (09.04.07 03:31) [32]
Спасибо большое за совет про исключение, приму к сведению.

Курсор уже сделала, изменила логику тригера (в том числе избавилась от commit и rollback tran). Вобщем все работает как часы, еще и duration уменьшилась.

← →
Jan1 (2007-04-10 19:45) [34]

> Курсор уже сделала, изменила логику тригера (в том числе
> избавилась от commit и rollback tran). Вобщем все работает
> как часы, еще и duration уменьшилась.

зачем курсор?

Лена, я после того, как увидел твой триггер, неделю не спал !
КТО КОМПЕНСИРУЕТ МНЕ ПСИХОУРОН ?
;)

Как отловить ошибки в тригере Найти похожие ветки