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

Откуда:
Сообщений: 269
Как в триггере получить имена полей в которых изменилось значение?
Желательно примерчик или ткните куда копать!
26 окт 05, 10:05    [2006335]     Ответить | Цитировать Сообщить модератору
 Re: Триггер: имена изменяемых полей  [new]
Glory
Member

Откуда:
Сообщений: 104760
Через функцию COLUMNS_UPDATED() описанную в CREATE TRIGGER
26 окт 05, 10:09    [2006365]     Ответить | Цитировать Сообщить модератору
 Re: Триггер: имена изменяемых полей  [new]
lazovik
Member

Откуда:
Сообщений: 269
Понятно что через COLUMNS_UPDATED() но как получить имена полей?
26 окт 05, 10:20    [2006425]     Ответить | Цитировать Сообщить модератору
 Re: Триггер: имена изменяемых полей  [new]
Glory
Member

Откуда:
Сообщений: 104760
lazovik
Понятно что через COLUMNS_UPDATED() но как получить имена полей?

По полученному номеру из таблицы syscolumns например. Или из INFORMATION_SCHEMA.COLUMNS
26 окт 05, 10:22    [2006435]     Ответить | Цитировать Сообщить модератору
 Re: Триггер: имена изменяемых полей  [new]
lazovik
Member

Откуда:
Сообщений: 269
>Glory
Большое спасибо!
26 окт 05, 10:33    [2006483]     Ответить | Цитировать Сообщить модератору
 Re: Триггер: имена изменяемых полей  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31596
lazovik
>Glory
Большое спасибо!
Только это будет не "имена полей в которых изменилось значение", а имена полей, которые участвовали в запросе.
26 окт 05, 12:16    [2007158]     Ответить | Цитировать Сообщить модератору
 Re: Триггер: имена изменяемых полей  [new]
WiRuc
Member

Откуда: Воронеж
Сообщений: 1280
alexeyvg
lazovik
>Glory
Большое спасибо!
Только это будет не "имена полей в которых изменилось значение", а имена полей, которые участвовали в запросе.


Ну, если уж быть совсем точным, то это будут имена полей, перечисленных после оператора SET
26 окт 05, 12:40    [2007351]     Ответить | Цитировать Сообщить модератору
 Re: Триггер: имена изменяемых полей  [new]
lazovik
Member

Откуда:
Сообщений: 269
WiRuc
alexeyvg
lazovik
>Glory
Большое спасибо!
Только это будет не "имена полей в которых изменилось значение", а имена полей, которые участвовали в запросе.


Ну, если уж быть совсем точным, то это будут имена полей, перечисленных после оператора SET

КАК получить имена полей?
26 окт 05, 13:04    [2007501]     Ответить | Цитировать Сообщить модератору
 Re: Триггер: имена изменяемых полей  [new]
Ray D
Member

Откуда: from the middle of nowhere
Сообщений: 3598
Блог
Читайте классиков. (c) Ken Henderson, Guru's Guide To SP's, XML&HTML
Глава 8.
...
DECLARE @ChangedColumns varbinary(8000), @Size int, @i int, @colname sysname
SET @ChangedColumns=COLUMNS_UPDATED()
SET @Size=DATALENGTH(@ChangedColumns)*8
SET @i=0
WHILE @i<@Size BEGIN
    IF ((@ChangedColumns & POWER(2,@i))<>0) BEGIN
             SELECT @ColName=COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='ToyInventory' AND ORDINAL_POSITION-1=@i
       PRINT 'Column '+@ColName+' changed'
    END
    SET @i=@i+1
END
...
26 окт 05, 13:39    [2007752]     Ответить | Цитировать Сообщить модератору
 Re: Триггер: имена изменяемых полей  [new]
lazovik
Member

Откуда:
Сообщений: 269
>Ray D
Класс, спасибо!!!
А как получить новые значения этих полей и конвентировать их в текст?
26 окт 05, 14:56    [2008231]     Ответить | Цитировать Сообщить модератору
 Re: Триггер: имена изменяемых полей  [new]
lazovik
Member

Откуда:
Сообщений: 269
Ну как есть у кого соображения по получению значений изменяемых полей?
27 окт 05, 07:41    [2010428]     Ответить | Цитировать Сообщить модератору
 Re: Триггер: имена изменяемых полей  [new]
Glory
Member

Откуда:
Сообщений: 104760
lazovik
Ну как есть у кого соображения по получению значений изменяемых полей?

Из таблиц inserted/deleted. Только у вас динамический запрос будет, а значит придется эти таблицы заносить в промежуточные временные таблицы.


ЗЫ
Универсальный триггер не есть самое лучшее решение для задачи аудита.
27 окт 05, 09:53    [2010719]     Ответить | Цитировать Сообщить модератору
 Re: Триггер: имена изменяемых полей  [new]
lazovik
Member

Откуда:
Сообщений: 269
Glory

ЗЫ
Универсальный триггер не есть самое лучшее решение для задачи аудита.


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

CREATE TRIGGER [tr_469576711_change] ON dbo.pupils FOR INSERT, UPDATE, DELETE AS
  DECLARE @triggertypeint INT, @tablename VARCHAR(128), @operation CHAR(6), @username VARCHAR(128), @datetime DATETIME, @count INT, @fieldname VARCHAR(128)
  SET @count = @@ROWCOUNT
  SELECT @tablename = name FROM sysobjects WHERE @@PROCID IN (instrig, deltrig, updtrig)
  SET @triggertypeint = 0
  IF EXISTS(SELECT * FROM INSERTED) SET @triggertypeint = @triggertypeint + 1
  IF EXISTS(SELECT * FROM DELETED) SET @triggertypeint = @triggertypeint + 2
  IF (@triggertypeint = 3)
    SET @operation = 'UPDATE'
  ELSE IF (@triggertypeint = 2)
    SET @operation = 'DELETE'
  ELSE IF (@triggertypeint = 1)
    SET @operation = 'INSERT'
  ELSE
    SET @operation = 'NOTYPE'
  SET @username = SUSER_SNAME()
  SET @datetime = GETDATE()

DECLARE @ChangedColumns varbinary(8000), @Size int, @i int, @colname sysname, @value VARCHAR(128), @sql NVARCHAR(4000)
SET @ChangedColumns=COLUMNS_UPDATED()
SET @Size=DATALENGTH(@ChangedColumns)*8
SET @i=0
select * into #tmp from inserted
WHILE @i<@Size BEGIN
    IF ((@ChangedColumns & POWER(2,@i))<>0) BEGIN
             SELECT @ColName=COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME=@tablename AND ORDINAL_POSITION-1=@i
        SET @fieldname=@ColName         
        SET @sql = 'SELECT @value = ' + @ColName + ' FROM #tmp'
        EXEC sp_executesql @sql, N'@value VARCHAR(128) OUT', @value = @value OUT
        IF EXISTS(SELECT * FROM log WHERE table_name = @tablename AND user_name = @username AND datetime = @datetime) UPDATE log SET field_name = field_name + ';' + @fieldname, value = value + ';' + @value WHERE table_name = @tablename AND user_name = @username AND datetime = @datetime
        ELSE INSERT INTO log VALUES (@tablename, @operation, @username, @datetime, @count, @fieldname, @value)
    END
    SET @i=@i+1
END
27 окт 05, 14:23    [2012576]     Ответить | Цитировать Сообщить модератору
 Re: Триггер: имена изменяемых полей  [new]
lazovik
Member

Откуда:
Сообщений: 269
2 Glory
Еще вот какая проблемка, если таблица INSERTED содержит поля типа TEXT то невыполняется
select * into #tmp from inserted
, как можно найти эти поля и создать запрос без них?
27 окт 05, 14:28    [2012610]     Ответить | Цитировать Сообщить модератору
 Re: Триггер: имена изменяемых полей  [new]
Glory
Member

Откуда:
Сообщений: 104760
lazovik
Вроде нормально получилось подскажи как получше сделать и какие проблемы могут возникнуть?


CREATE TRIGGER [tr_469576711_change] ON dbo.pupils FOR INSERT, UPDATE, DELETE AS
...
select * into #tmp from inserted
WHILE @i<@Size BEGIN
    IF ((@ChangedColumns & POWER(2,@i))<>0) BEGIN
             SELECT @ColName=COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME=@tablename AND ORDINAL_POSITION-1=@i
        SET @fieldname=@ColName         
        SET @sql = 'SELECT @value = ' + @ColName + ' FROM #tmp'
        EXEC sp_executesql @sql, N'@value VARCHAR(128) OUT', @value = @value OUT
...
    END
    SET @i=@i+1
END
Это выборка N раз одной случайной строки из таблицы #tmp ???

Если @@ROWCOUNT равно 0 то надо сразу заканчивать триггер. К чему все остальные проверки есди ни одной записи не было затронуто ?
27 окт 05, 15:52    [2013079]     Ответить | Цитировать Сообщить модератору
 Re: Триггер: имена изменяемых полей  [new]
Glory
Member

Откуда:
Сообщений: 104760
lazovik
2 Glory
Еще вот какая проблемка, если таблица INSERTED содержит поля типа TEXT то невыполняется
select * into #tmp from inserted
, как можно найти эти поля и создать запрос без них?

для after триггеров все, что есть в inserted, уже есть и в базовой таблице
27 окт 05, 15:53    [2013090]     Ответить | Цитировать Сообщить модератору
 Re: Триггер: имена изменяемых полей  [new]
Slider_spb
Member

Откуда:
Сообщений: 802
Вот полезно почитать: http://www.infocity.kiev.ua/db/content/db277.phtml
27 окт 05, 15:56    [2013107]     Ответить | Цитировать Сообщить модератору
 Re: Триггер: имена изменяемых полей  [new]
lazovik
Member

Откуда:
Сообщений: 269
Slider_spb
Вот полезно почитать: http://www.infocity.kiev.ua/db/content/db277.phtml

Уже читал, сделал выводы, теперь реализую, вот и возникают вопросы! :-)
28 окт 05, 10:12    [2015358]     Ответить | Цитировать Сообщить модератору
 Re: Триггер: имена изменяемых полей  [new]
lazovik
Member

Откуда:
Сообщений: 269
Glory
Это выборка N раз одной случайной строки из таблицы #tmp ???
...
Если @@ROWCOUNT равно 0 то надо сразу заканчивать триггер. К чему все остальные проверки есди ни одной записи не было затронуто ?


А как правильно должен заканчиваться триггер нормально(Если @@ROWCOUNT болше 0) и досрочно (Если @@ROWCOUNT равно 0), и вобше как должен заканчиваться триггер при разных условиях

Glory

для after триггеров все, что есть в inserted, уже есть и в базовой таблице


Как выдернуть те записи из базовой таблици которые есть в inserted?

P.S. Сильно не пинайте, если поковырялся бы сам, может и нашел, но время поджимает! СПАСИБО ЗА ИНФОРМАЦИЮ!
28 окт 05, 10:34    [2015465]     Ответить | Цитировать Сообщить модератору
 Re: Триггер: имена изменяемых полей  [new]
Glory
Member

Откуда:
Сообщений: 104760
lazovik

А как правильно должен заканчиваться триггер нормально(Если @@ROWCOUNT болше 0) и досрочно (Если @@ROWCOUNT равно 0), и вобше как должен заканчиваться триггер при разных условиях

Если нужно досрочно то RETURN. Или правильным ветвлением с помощью IF-ов


lazovik

Как выдернуть те записи из базовой таблици которые есть в inserted?

При правильном дизайне таблицы имеют ПервиныеКлючи.

Что насчет циклической выборки случайное записи в динамическом запросе ???

ЗЫ
Еще раз советую вам отказаться от идеи универсального триггера.
28 окт 05, 10:46    [2015528]     Ответить | Цитировать Сообщить модератору
 Re: Триггер: имена изменяемых полей  [new]
lazovik
Member

Откуда:
Сообщений: 269
Glory

ЗЫ
Еще раз советую вам отказаться от идеи универсального триггера.

Почему и что предложите?
Структура таблиц и БД в общем полная Ж..., не я разрабатывал, а пересмотреть структуру начальство не очень горит желанием (предется и пользовательское ПО переделывать) вот и мучаюсь!
А одна из главных проблем такая: некий пользователь делает изменения в таблице, а потом эти изменения кудато пропадают, хотя изменения пользователь видит в данный момент, строит отчеты и т.д., а через некоторое время изменения проподают!!!
ПО сервер: MS SQL Server 2000
ПО пользователь: MS Access 2000
Файл Access-а огроменный (по моим меркам!!! 70-90 мегов. УЖАС!!!) похоже что при изменении изменения находятся в Access-е и не всегда отправляются на сервер!!! ЧТО МОЖЕТЕ СКАЗАТЬ ПО ЭТОМУ ПОВОДУ?
28 окт 05, 11:08    [2015654]     Ответить | Цитировать Сообщить модератору
 Re: Триггер: имена изменяемых полей  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37100
автор
Файл Access-а огроменный (по моим меркам!!! 70-90 мегов. УЖАС!!!) похоже что при изменении изменения находятся в Access-е и не всегда отправляются на сервер!!! ЧТО МОЖЕТЕ СКАЗАТЬ ПО ЭТОМУ ПОВОДУ?

Что есть замечательная утилита Profiler.
28 окт 05, 11:11    [2015668]     Ответить | Цитировать Сообщить модератору
 Re: Триггер: имена изменяемых полей  [new]
Glory
Member

Откуда:
Сообщений: 104760
Почему и что предложите?
Написать генератор триггеров

А одна из главных проблем такая: некий пользователь делает изменения в таблице, а потом эти изменения кудато пропадают, хотя изменения пользователь видит в данный момент, строит отчеты и т.д., а через некоторое время изменения проподают!!!
Изменения, призведенные в транзакции могут при желаении быть видны в других коннектах(грязное чтение). И потом "изчезают" вместе с отменой транзакции. В этом случае триггер вам не поможет, т.к. он выполняется в той же транзакции. И значит вместе с отменой транзакции будут отменены и все действия триггера.
28 окт 05, 11:13    [2015680]     Ответить | Цитировать Сообщить модератору
 Re: Триггер: имена изменяемых полей  [new]
lazovik
Member

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

Что есть замечательная утилита Profiler.

Эта хрень (Profiler) не то что надо, мониторит неспорю НО файлы ОГОГО полючаются и инфа в них не та что требуется (значения изменяемых полей нет и т.д.) зато ненужной сколь хош, короче пробовал НЕ ТО! :-)
28 окт 05, 11:18    [2015701]     Ответить | Цитировать Сообщить модератору
 Re: Триггер: имена изменяемых полей  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31596
lazovik
Glory

ЗЫ
Еще раз советую вам отказаться от идеи универсального триггера.

Почему и что предложите?
Структура таблиц и БД в общем полная Ж..., не я разрабатывал, а пересмотреть структуру начальство не очень горит желанием (предется и пользовательское ПО переделывать) вот и мучаюсь!
А одна из главных проблем такая: некий пользователь делает изменения в таблице, а потом эти изменения кудато пропадают, хотя изменения пользователь видит в данный момент, строит отчеты и т.д., а через некоторое время изменения проподают!!!
ПО сервер: MS SQL Server 2000
ПО пользователь: MS Access 2000
Файл Access-а огроменный (по моим меркам!!! 70-90 мегов. УЖАС!!!) похоже что при изменении изменения находятся в Access-е и не всегда отправляются на сервер!!! ЧТО МОЖЕТЕ СКАЗАТЬ ПО ЭТОМУ ПОВОДУ?

Описанное вами не запрещает отказаться от идеи универсального триггера.

Правильные подходы описывались в форуме неоднократно.

Например:

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

Если таблиц огромное количество, можно генерить триггеры.

Для этого можно либо написать генерящую программу (процедуру), либо воспользоваться макросами в каком-нибуть средстве проектирования БД (типа PD, ErWin), если вы их используете.
28 окт 05, 11:18    [2015702]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить