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

Откуда: Москва
Сообщений: 6
Доброго дня!

У меня следующая проблема:
на таблице существует триггер на обновление записи AFTER UPDATE для логирования изменений.

Вчера было замечено, что при вызове процедуры через веб-клиент, которая меняет данную таблицу, триггер не срабатывает.

При выполнении процедуры через Management Studio - отрабатывает.

В чём может быть проблема?

Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64)
20 авг 09, 11:52    [7560720]     Ответить | Цитировать Сообщить модератору
 Re: Триггер на срабатывает  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74927
автор
Вчера было замечено, что при вызове процедуры через веб-клиент, которая меняет данную таблицу, триггер не срабатывает.

При выполнении процедуры через Management Studio - отрабатывает.


И это было проверено с помощью профайлера с включенными событиями SP:StmtStarting\StmtCompleted?

Сообщение было отредактировано: 20 авг 09, 11:58
20 авг 09, 11:57    [7560772]     Ответить | Цитировать Сообщить модератору
 Re: Триггер на срабатывает  [new]
Ramin Hashimzade
Member

Откуда: Азербайджан, Баку
Сообщений: 9979
Блог
посмотри в профайлере запрос апдейта точно туда идет? для сервера до лампочки запрос откуда ему основной что б запрос был правильный....
покажы триггер может какойта иф поставлено и не пападает в иф ???
20 авг 09, 11:58    [7560777]     Ответить | Цитировать Сообщить модератору
 Re: Триггер на срабатывает  [new]
Kuzglamdring
Member

Откуда: Москва
Сообщений: 6
Посмотрел триггер. If дествительно есть.
Вы правы, профайлер показывает, что if-условие проверятся, но дальше не идёт.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER dbo.CompanyEmployee_UpdateLog ON dbo.CompanyEmployee
AFTER UPDATE
AS
SET NOCOUNT ON;

IF EXISTS (
	SELECT *
	FROM glo.DescriptTable_GetUpdatedColumns('dbo.CompanyEmployee', COLUMNS_UPDATED())
	WHERE ColumnName NOT IN ('Login', 'ModifiedDate', 'LogOperation')
)
BEGIN

DECLARE
  @DescriptTableId int
, @LogActionId int
, @UserName nvarchar(255);

SET @UserName = SYSTEM_USER;

SELECT @DescriptTableId = dt.DescriptTableId
FROM glo.DescriptTable dt
WHERE dt.TableName = 'dbo.CompanyEmployee';

-- Update
SET @LogActionId = 2;

WITH T (
  RowId
, [Login]
, LogOperationId
, Name
, NewValue
, OldValue
)
AS (
	SELECT
	  T1.RowId
	, T1.[Login]
	, T1.LogOperationId
	, T1.Field
	, T1.NewValue
	, T2.OldValue
	FROM (
		SELECT
		  RowId
		, [Login]
		, LogOperationId
		, Field
		, NewValue
		FROM (
			SELECT
			  RowId = i.CompanyEmployeeId
			, [Login] = ISNULL(i.[Login], @UserName)
			, LogOperationId = ISNULL(lo.LogOperationId, 1)
			, FirstName = ISNULL(CONVERT(varchar(max), i.FirstName), 'NULL')
			, MiddleName = ISNULL(CONVERT(varchar(max), i.MiddleName), 'NULL')
			, LastName = ISNULL(CONVERT(varchar(max), i.LastName), 'NULL')
			, Telephone1 = ISNULL(CONVERT(varchar(max), i.Telephone1), 'NULL')
			, Telephone2 = ISNULL(CONVERT(varchar(max), i.Telephone2), 'NULL')
			, Telephone3 = ISNULL(CONVERT(varchar(max), i.Telephone3), 'NULL')
			, Fax1 = ISNULL(CONVERT(varchar(max), i.Fax1), 'NULL')
			, Fax2 = ISNULL(CONVERT(varchar(max), i.Fax2), 'NULL')
			, eMail = ISNULL(CONVERT(varchar(max), i.eMail), 'NULL')
			, eMailHome = ISNULL(CONVERT(varchar(max), i.eMailHome), 'NULL')
			, PasportNumber = ISNULL(CONVERT(varchar(max), i.PasportNumber), 'NULL')
			, PasportSeries = ISNULL(CONVERT(varchar(max), i.PasportSeries), 'NULL')
			, PasportPlace = ISNULL(CONVERT(varchar(max), i.PasportPlace), 'NULL')
			, Address = ISNULL(CONVERT(varchar(max), i.Address), 'NULL')
			, Birthday = ISNULL(CONVERT(varchar(max), i.Birthday, 120), 'NULL')
			, PasportDate = ISNULL(CONVERT(varchar(max), i.PasportDate, 120), 'NULL')
			, Residence = ISNULL(CONVERT(varchar(max), i.Residence), 'NULL')
			, ForeignPaspNumber = ISNULL(CONVERT(varchar(max), i.ForeignPaspNumber), 'NULL')
			, ForeignPaspSeries = ISNULL(CONVERT(varchar(max), i.ForeignPaspSeries), 'NULL')
			, ForeignPaspDateIssue = ISNULL(CONVERT(varchar(max), i.ForeignPaspDateIssue, 120), 'NULL')
			, ForeignPaspDateExpiry = ISNULL(CONVERT(varchar(max), i.ForeignPaspDateExpiry, 120), 'NULL')
			, ForeignPaspPlace = ISNULL(CONVERT(varchar(max), i.ForeignPaspPlace), 'NULL')
			, FirstNameEng = ISNULL(CONVERT(varchar(max), i.FirstNameEng), 'NULL')
			, LastNameEng = ISNULL(CONVERT(varchar(max), i.LastNameEng), 'NULL')
			, NationalityId = ISNULL(CONVERT(varchar(max), i.NationalityId), 'NULL')
			, FamilyStatusId = ISNULL(CONVERT(varchar(max), i.FamilyStatusId), 'NULL')
			, Sex = ISNULL(CONVERT(varchar(max), Sex), 'NULL')
			, TemporaryRegistered = ISNULL(CONVERT(varchar(max), i.TemporaryRegistered), 'NULL')
			, Photo = ISNULL(CONVERT(varchar(max), CASE WHEN i.Photo IS NULL THEN NULL ELSE BINARY_CHECKSUM(i.Photo) END), 'NULL')
			, PhotoFileName = ISNULL(CONVERT(varchar(max), i.PhotoFileName), 'NULL')
			, MilitaryService = ISNULL(CONVERT(varchar(max), i.MilitaryService), 'NULL')
			, Signature = ISNULL(CONVERT(varchar(max), i.Signature), 'NULL')
			, SignatureFileName = ISNULL(CONVERT(varchar(max), i.SignatureFileName), 'NULL')
			, IsWorkBook = ISNULL(CONVERT(varchar(max), i.IsWorkBook), 'NULL')
			, FormalEmployee = ISNULL(CONVERT(varchar(max), i.FormalEmployee), 'NULL')
			, IdentificationNumber = ISNULL(CONVERT(varchar(max), i.IdentificationNumber), 'NULL')
			, ClockNumber = ISNULL(CONVERT(varchar(max), i.ClockNumber), 'NULL')
			, CitizenshipId = ISNULL(CONVERT(varchar(max), i.CitizenshipId), 'NULL')
			, RetirementInsurance = ISNULL(CONVERT(varchar(max), i.RetirementInsurance), 'NULL')
			, WinLogin = ISNULL(CONVERT(varchar(max), i.WinLogin), 'NULL')
			, AddressBookName = ISNULL(CONVERT(varchar(max), i.AddressBookName), 'NULL')
			, Counteragent = ISNULL(CONVERT(varchar(max), i.Counteragent), 'NULL')
			, IsNewSent = ISNULL(CONVERT(varchar(max), i.IsNewSent), 'NULL')
			, CompanyEmployeeCode = ISNULL(CONVERT(varchar(max), i.CompanyEmployeeCode), 'NULL')
			, MiddleNameEng = ISNULL(CONVERT(varchar(max), i.MiddleNameEng), 'NULL')
			, PlannedAppointmentDate = ISNULL(CONVERT(varchar(max), i.PlannedAppointmentDate, 120), 'NULL')
			, IsCongratulated = ISNULL(CONVERT(varchar(max), i.IsCongratulated), 'NULL')
			, Note = ISNULL(CONVERT(varchar(max), i.Note), 'NULL')
			FROM inserted i
			LEFT  JOIN glo.LogOperation lo
			        ON lo.LogOperationCode = i.LogOperation 
		) p
		UNPIVOT (
			NewValue FOR Field IN ( 
			  FirstName
			, MiddleName
			, LastName
			, Telephone1
			, Telephone2
			, Telephone3
			, Fax1
			, Fax2
			, eMail
			, eMailHome
			, PasportNumber
			, PasportSeries
			, PasportPlace
			, Address
			, Birthday
			, PasportDate
			, Residence
			, ForeignPaspNumber
			, ForeignPaspSeries
			, ForeignPaspDateIssue
			, ForeignPaspDateExpiry
			, ForeignPaspPlace
			, FirstNameEng
			, LastNameEng
			, NationalityId
			, FamilyStatusId
			, Sex
			, TemporaryRegistered
			, Photo
			, PhotoFileName
			, MilitaryService
			, Signature
			, SignatureFileName
			, IsWorkBook
			, FormalEmployee
			, IdentificationNumber
			, ClockNumber
			, CitizenshipId
			, RetirementInsurance
			, WinLogin
			, AddressBookName
			, Counteragent
			, IsNewSent
			, CompanyEmployeeCode
			, MiddleNameEng
			, PlannedAppointmentDate
			, IsCongratulated
			, Note
			)
		)AS unpvt
	) T1
	INNER JOIN (
		SELECT
		  RowId
		, Field
		, OldValue
		FROM (
			SELECT
			  RowId = d.CompanyEmployeeId
			, FirstName = ISNULL(CONVERT(varchar(max), FirstName), 'NULL')
			, MiddleName = ISNULL(CONVERT(varchar(max), MiddleName), 'NULL')
			, LastName = ISNULL(CONVERT(varchar(max), LastName), 'NULL')
			, Telephone1 = ISNULL(CONVERT(varchar(max), Telephone1), 'NULL')
			, Telephone2 = ISNULL(CONVERT(varchar(max), Telephone2), 'NULL')
			, Telephone3 = ISNULL(CONVERT(varchar(max), Telephone3), 'NULL')
			, Fax1 = ISNULL(CONVERT(varchar(max), Fax1), 'NULL')
			, Fax2 = ISNULL(CONVERT(varchar(max), Fax2), 'NULL')
			, eMail = ISNULL(CONVERT(varchar(max), eMail), 'NULL')
			, eMailHome = ISNULL(CONVERT(varchar(max), eMailHome), 'NULL')
			, PasportNumber = ISNULL(CONVERT(varchar(max), PasportNumber), 'NULL')
			, PasportSeries = ISNULL(CONVERT(varchar(max), PasportSeries), 'NULL')
			, PasportPlace = ISNULL(CONVERT(varchar(max), PasportPlace), 'NULL')
			, Address = ISNULL(CONVERT(varchar(max), Address), 'NULL')
			, Birthday = ISNULL(CONVERT(varchar(max), Birthday, 120), 'NULL')
			, PasportDate = ISNULL(CONVERT(varchar(max), PasportDate, 120), 'NULL')
			, Residence = ISNULL(CONVERT(varchar(max), Residence), 'NULL')
			, ForeignPaspNumber = ISNULL(CONVERT(varchar(max), ForeignPaspNumber), 'NULL')
			, ForeignPaspSeries = ISNULL(CONVERT(varchar(max), ForeignPaspSeries), 'NULL')
			, ForeignPaspDateIssue = ISNULL(CONVERT(varchar(max), ForeignPaspDateIssue, 120), 'NULL')
			, ForeignPaspDateExpiry = ISNULL(CONVERT(varchar(max), ForeignPaspDateExpiry, 120), 'NULL')
			, ForeignPaspPlace = ISNULL(CONVERT(varchar(max), ForeignPaspPlace), 'NULL')
			, FirstNameEng = ISNULL(CONVERT(varchar(max), FirstNameEng), 'NULL')
			, LastNameEng = ISNULL(CONVERT(varchar(max), LastNameEng), 'NULL')
			, NationalityId = ISNULL(CONVERT(varchar(max), NationalityId), 'NULL')
			, FamilyStatusId = ISNULL(CONVERT(varchar(max), FamilyStatusId), 'NULL')
			, Sex = ISNULL(CONVERT(varchar(max), Sex), 'NULL')
			, TemporaryRegistered = ISNULL(CONVERT(varchar(max), TemporaryRegistered), 'NULL')
			, Photo = ISNULL(CONVERT(varchar(max), CASE WHEN Photo IS NULL THEN NULL ELSE BINARY_CHECKSUM(Photo) END), 'NULL')
			, PhotoFileName = ISNULL(CONVERT(varchar(max), PhotoFileName), 'NULL')
			, MilitaryService = ISNULL(CONVERT(varchar(max), MilitaryService), 'NULL')
			, Signature = ISNULL(CONVERT(varchar(max), Signature), 'NULL')
			, SignatureFileName = ISNULL(CONVERT(varchar(max), SignatureFileName), 'NULL')
			, IsWorkBook = ISNULL(CONVERT(varchar(max), IsWorkBook), 'NULL')
			, FormalEmployee = ISNULL(CONVERT(varchar(max), FormalEmployee), 'NULL')
			, IdentificationNumber = ISNULL(CONVERT(varchar(max), IdentificationNumber), 'NULL')
			, ClockNumber = ISNULL(CONVERT(varchar(max), ClockNumber), 'NULL')
			, CitizenshipId = ISNULL(CONVERT(varchar(max), CitizenshipId), 'NULL')
			, RetirementInsurance = ISNULL(CONVERT(varchar(max), RetirementInsurance), 'NULL')
			, WinLogin = ISNULL(CONVERT(varchar(max), WinLogin), 'NULL')
			, AddressBookName = ISNULL(CONVERT(varchar(max), AddressBookName), 'NULL')
			, Counteragent = ISNULL(CONVERT(varchar(max), Counteragent), 'NULL')
			, IsNewSent = ISNULL(CONVERT(varchar(max), IsNewSent), 'NULL')
			, CompanyEmployeeCode = ISNULL(CONVERT(varchar(max), CompanyEmployeeCode), 'NULL')
			, MiddleNameEng = ISNULL(CONVERT(varchar(max), MiddleNameEng), 'NULL')
			, PlannedAppointmentDate = ISNULL(CONVERT(varchar(max), PlannedAppointmentDate, 120), 'NULL')
			, IsCongratulated = ISNULL(CONVERT(varchar(max), IsCongratulated), 'NULL')
			, Note = ISNULL(CONVERT(varchar(max), Note), 'NULL')
			FROM deleted d
		) p
		UNPIVOT (
			OldValue FOR Field IN ( 
			  FirstName
			, MiddleName
			, LastName
			, Telephone1
			, Telephone2
			, Telephone3
			, Fax1
			, Fax2
			, eMail
			, eMailHome
			, PasportNumber
			, PasportSeries
			, PasportPlace
			, Address
			, Birthday
			, PasportDate
			, Residence
			, ForeignPaspNumber
			, ForeignPaspSeries
			, ForeignPaspDateIssue
			, ForeignPaspDateExpiry
			, ForeignPaspPlace
			, FirstNameEng
			, LastNameEng
			, NationalityId
			, FamilyStatusId
			, Sex
			, TemporaryRegistered
			, Photo
			, PhotoFileName
			, MilitaryService
			, Signature
			, SignatureFileName
			, IsWorkBook
			, FormalEmployee
			, IdentificationNumber
			, ClockNumber
			, CitizenshipId
			, RetirementInsurance
			, WinLogin
			, AddressBookName
			, Counteragent
			, IsNewSent
			, CompanyEmployeeCode
			, MiddleNameEng
			, PlannedAppointmentDate
			, IsCongratulated
			, Note
			)
		)AS unpvt
	) T2
			ON T1.RowId = T2.RowId
		   AND T1.Field = T2.Field
		   AND T1.NewValue <> T2.OldValue
)
INSERT glo.LogTable(
  DescriptTableId
, LogActionId
, LogOperationId
, RowId
, [Login]
, NoteXml
)
SELECT
  @DescriptTableId
, @LogActionId
, T.LogOperationId
, T.RowId
, T.[Login]
, (
	SELECT Name, NewValue, OldValue
	FROM T Field
	WHERE Field.RowId = T.RowId
	FOR XML AUTO, ROOT('LogUpdate'), Type
)
FROM T
GROUP BY T.RowId, T.[Login], T.LogOperationId;

UPDATE ce SET
  [Login] = NULL
, LogOperation = NULL
FROM dbo.CompanyEmployee ce
INNER JOIN inserted i
        ON i.CompanyEmployeeId = ce.CompanyEmployeeId
WHERE i.[Login] IS NOT NULL
END

Тогда не понятно, почему срабатывает, если выполнить через MS...
20 авг 09, 12:06    [7560837]     Ответить | Цитировать Сообщить модератору
 Re: Триггер на срабатывает  [new]
Kuzglamdring
Member

Откуда: Москва
Сообщений: 6
Функция определения обновляемых полей

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION glo.DescriptTable_GetUpdatedColumns (	
  @TableName sysname 
, @ColumnsUpdated varbinary(max)
)
RETURNS TABLE 
AS
RETURN (
      SELECT
        ColumnName = [C].[name]
      FROM (
            SELECT
              [Bytes] = @ColumnsUpdated
            , [Len] = CONVERT(int, DATALENGTH(@ColumnsUpdated))
      ) [Updated]
      CROSS JOIN (
            SELECT 0 [number], CONVERT(tinyint, POWER(2, 0)) [degree]
            UNION ALL
            SELECT 1, CONVERT(tinyint, POWER(2, 1))
            UNION ALL
            SELECT 2, CONVERT(tinyint, POWER(2, 2))
            UNION ALL
            SELECT 3, CONVERT(tinyint, POWER(2, 3))
            UNION ALL
            SELECT 4, CONVERT(tinyint, POWER(2, 4))
            UNION ALL
            SELECT 5, CONVERT(tinyint, POWER(2, 5))
            UNION ALL
            SELECT 6, CONVERT(tinyint, POWER(2, 6))
            UNION ALL
            SELECT 7, CONVERT(tinyint, POWER(2, 7))
      ) [BitNumber]
      INNER JOIN [master].[dbo].[spt_values] [ByteNumber]
              ON [ByteNumber].[type]='P'
             AND [ByteNumber].[number] BETWEEN 1 AND [Updated].[Len]
             AND CONVERT(tinyint, SUBSTRING([Updated].[Bytes],[ByteNumber].[number],1)) & [BitNumber].[degree] <> 0
      INNER JOIN sys.columns [C]
              ON [C].column_id = 8*([ByteNumber].[number]-1)+[BitNumber].[number]+1
      WHERE [C].[object_id] = OBJECT_ID(@TableName, 'U')
)
20 авг 09, 12:08    [7560854]     Ответить | Цитировать Сообщить модератору
 Re: Триггер на срабатывает  [new]
Kuzglamdring
Member

Откуда: Москва
Сообщений: 6
Выяснил, что функция glo.DescriptTable_GetUpdatedColumns(), будучи выполняемой под пользователем, от имени которого выполняются запросы веб-клиента, возвращает не полный результат, подчастую пустой.

Проблемы, я так понимаю, в правах.
Только понять бы, на что именно...
20 авг 09, 12:38    [7561058]     Ответить | Цитировать Сообщить модератору
 Re: Триггер на срабатывает  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
Не понимаю, что даёт COLUMNS_UPDATED()?
Почему бы IF не написать так:
IF EXISTS(SELECT ID, F1,F2,F3,...,Fn FROM inserted EXCEPT SELECT ID, F1,F2,F3,...,Fn FROM deleted)
где ID - это PK таблицы dbo.CompanyEmployee (видимо, CompanyEmployeeId)?
20 авг 09, 12:51    [7561159]     Ответить | Цитировать Сообщить модератору
 Re: Триггер на срабатывает  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74927
Kuzglamdring
Проблемы, я так понимаю, в правах.
Только понять бы, на что именно...


Возможно вот в этом вызове:

OBJECT_ID(@TableName, 'U')

Поумолчанию не всем доступны метаданные.

GRANT VIEW DEFINITION TO public
20 авг 09, 12:51    [7561160]     Ответить | Цитировать Сообщить модератору
 Re: Триггер на срабатывает  [new]
Kuzglamdring
Member

Откуда: Москва
Сообщений: 6
pkarklin:

Да, я это тоже выяснил!

GRANT VIEW DEFINITION TO public

Всё заработало, спасибо!

iap:

интересная идея, можно попробывать посмотреть и этот вариант
20 авг 09, 12:57    [7561220]     Ответить | Цитировать Сообщить модератору
 Re: Триггер на срабатывает  [new]
Kuzglamdring
Member

Откуда: Москва
Сообщений: 6
Только проблема в sys.columns, а не Object_Id()
20 авг 09, 13:00    [7561257]     Ответить | Цитировать Сообщить модератору
 Re: Триггер на срабатывает  [new]
Ramin Hashimzade
Member

Откуда: Азербайджан, Баку
Сообщений: 9979
Блог
автор
Посмотрел триггер. If дествительно есть.
Вы правы, профайлер показывает, что if-условие проверятся, но дальше не идёт.

профайлер девушка красивая :)
20 авг 09, 13:11    [7561350]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить