Триггеры

[dima_9]

Интересно ваше мнение по поводу следующего

На данный момент стандарт SQL-3 (SQL-2 c PSM) предусматривает создание триггеров (событие + действия). В моем понимании это реакция на результат того или иного изменения данных.


Почему разработчики СУБД не позволяют реагировать на конкретную выполненную инструкцию SQL. Ну например, как вариант, если бы была системная таблица, в которую всавлялась бы очередная выполненная инструкция. Понятно, что существует много ситуаций, когда нужно реагировать на результат (ведь к одному и тому же рузультату могут привести различные инструкции), но на мой взгляд существуют и другие ситуации, когда нужна реакция именно на конкретную инструкцию. Другими словами, когда событие - это выполлнение инструкции SQL (например, когда необходимо отличать какая инструкция привела к данному результату).

 

[Barmutik]

Возможно это не реализовано, потому что спрос на данную опцию не высок. С помощью тригеров можно реализовать наверно практически все требуемые действия. Что нельзя с помощью них, то можно с помощью хранимых процедур.
Часть логики можно вынести на клиент, если уж что-то очень специфичное.

 

[dima_9]

Значит решения следующей задачи нет в рамках любой РСУБД ???


Есть 2 совсем разных, предназначенных для решения своих локальных задач приложения, и работающих на MS SQL и DB2 и у которых есть import/export. В первом приложении есть форма и пользователь добавляет данные инструкцией INSERT INTO Sotrudnik (Id,Sotr_FIO) VALUES (1,'Иванов C.И.') (ну то есть есть форма на которой вводятся данные и user нажимает кнопку добавить). В том же приложении иногда делается import документов, которые добавляются инструкцией INSERT INTO Sotrudnik (Id,Sotr_name) SELECT * FROM TEMP1 (ну там создается дополнительная таблица на сервере и потом все одной инструкцией в базу - не знаю для чего, наверно зачем-то нужно). Подвергать изменениям код приложений нельзя и перепроектировать что-то то же.
Далее - надо реализовать следующую логику (она может быть различной - для этого конкретного примера - такой)

1) Если это INSERT INTO Sotrudnik (Id,Sotr_FIO) VALUES (1,'Иванов C.И.') то добавить это ФИО в таблицу на другом сервере DB2 для второго приложения

2) Если это INSERT INTO Sotrudnik (Id,Sotr_FIO) SELECT * FROM TEMP1 то также все их добавить + дополнительно в отдельное поле 'Notes' на втором сервере дописать 'import' для каждой записи.

Если можно отвечайте как-то обосновано

 

[Barmutik]

Ну как вариант эту логику можно вынести на клиент.. если добавляете сотрудника по пункту 1 до добавьте и ручками ФИО в DB2.

Во втором случае аналогично.

Или как вариант .. если во втором случае вы всегда вставляете более одной записи .. то можно в тригере на INSERT это поймать и опять же произвести требуемые действия

Мне лично первый вариант нравится больше чем второй ...

 

[dima_9]

2 Barmutik

"Ну как вариант эту логику можно вынести на клиент.. если добавляете сотрудника по пункту 1 до добавьте и ручками ФИО в DB2."

В том то и дело, что приложения готовы, куплены и модифицировать код нельзя


"Или как вариант .. если во втором случае вы всегда вставляете более одной записи .. то можно в тригере на INSERT это поймать и опять же произвести требуемые действия"

Да об этом я не подумал.
Конечно, можно это сделать, но где гарантия, что будет добавляться больше одной записи ???

Я понял, что это ситуация только со мной

У меня еще один вопрос - почему бы разрабочикам СУБД не реализовать возможность реагировать на сложные события - что-то вроде

CREATE TRIGGER ADDING (ON READERS FOR INSERT) AND (ON DOC FOR UPDATE WHERE DOC.Readers_Id=READERS.Readers_Id) и дальше

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

Я понимаю, что это можно реалтизовать руками самому и что трудно судить почему они там такие тупые

Я просто хочу спросить - что это у меня только такие большие запросы к рсубд
или может быть не особо необходимо большинству ???

 

[Barmutik]

Хмм.. потому что есть стандарт ANSI SQL.. его и так полностью поддержать не может почти никто... просто запрашиваемые Вами задачи требуется в очень малом проценте случаев и требуют большой сложности для разработки...

Как вариант могу посоветовать ещё вот что:

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

 

[Barmutik]

Даа.. Вот только что выянилось что в МС СКЛ есть команда, которая возвращает текст запроса - fn_get_sql

Так что я думаю это поможет решить все Ваши запросы...

Мда уж .. учится никогд не поздно ... Это я про себя...

 

[Guest]

2 Barmutik

"Хмм.. потому что есть стандарт ANSI SQL.. его и так полностью поддержать не может почти никто... просто запрашиваемые Вами задачи требуется в очень малом проценте случаев и требуют большой сложности для разработки..."

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


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


Хорошо, добавить поле в таблицу с сотрудниками я могу - но не уверен что приложение потом будет всегда корректно работать. Только как добавить в Temp1 - оно создается динамически create table ... самим приложением. Изменить эту инструкцию я не могу


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

 

[dima_9]

fn_get_sql


А можно поподробнее ???

Может где поискать ??

 

[Barmutik]

Эта команда появилась только начиная с MSQ SQL Server 2000 SP3.

Почитайте .. ну в хелпе или MSDN ...

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

 

[????]

<!--QuoteBegin-dima_9+22:08:2005, 12:39 -->
<span class="vbquote">(dima_9 @ 22:08:2005, 12:39 )</span><!--QuoteEBegin-->Значит решения следующей задачи нет в рамках любой РСУБД ???

Далее - надо реализовать следующую логику (она может быть различной - для этого конкретного примера - такой)

1) Если это INSERT INTO Sotrudnik (Id,Sotr_FIO) VALUES (1,'Иванов C.И.') то добавить это ФИО в таблицу на другом сервере DB2 для второго приложения

2) Если это INSERT INTO Sotrudnik (Id,Sotr_FIO) SELECT * FROM TEMP1 то также все их добавить + дополнительно в отдельное поле 'Notes' на втором сервере дописать 'import' для каждой записи.

[snapback]23844" rel="nofollow" target="_blank[/snapback]​

[/quote]

Есть. Но это решение не 100% проверенное и больше похоже на хак.

CREATE TABLE SOTRUDNIK
(
ID    NUMBER,
SOTR_FIO VARCHAR2(200)
);


CREATE OR REPLACE TRIGGER sotrudnik_biu
 BEFORE INSERT OR UPDATE
 ON sotrudnik
 FOR EACH ROW
DECLARE
 l_tmp  NUMBER;
 import  EXCEPTION;
 PRAGMA EXCEPTION_INIT (import, -4091);
BEGIN
 -- Вот этот код вызовет мутацию таблиц
 -- при вставке более одной строки (код ошибки -04091)
 SELECT COUNT (*)
  INTO l_tmp
  FROM sotrudnik;
EXCEPTION
 WHEN import
 THEN
  -- Просто игнорируем исключение и делаем то, что надо
  DBMS_OUTPUT.put_line ('Импорт!!!!'); -- заменить на необходимое действие                     
END sotrudnik_biu;
/
SHOW ERRORS;

SQL> insert into sotrudnik values(1, 'Вася');

1 строка создана.

SQL> insert into sotrudnik values(2, 'Петя');

1 строка создана.

SQL> insert into sotrudnik values(3, 'Коля');

1 строка создана.

SQL> select * from sotrudnik;

   ID SOTR_FIO
---------- ---------------
    1 Вася
    2 Петя
    3 Коля

SQL> insert into sotrudnik select id+10, sotr_fio from sotrudnik;
Импорт!!!!
Импорт!!!!
Импорт!!!!

3 строк создано.

SQL> select * from sotrudnik;

   ID SOTR_FIO
---------- ---------------
    1 Вася
    2 Петя
    3 Коля
   11 Вася
   12 Петя
   13 Коля

6 строк выбрано.

Как видишь, это прокатывает.

Можно получать текущий текст запроса через динамические представления (так делают все утилиты мониторинга). Только всё это не очень правильное решение.

Лично я думаю, что можно обойтись и без этого (главное правильно описать и поставить задачу). Например по определенному событию (или job'ом) помечать одинаковые записи на двух серверах как import. Надо просто хорошо разобраться в решаемой задаче - решение само придёт.

 

[Barmutik]

А можно для личного обучения.. по подробнее про получения текста запроса через динамические представления...

 

[????]

<!--QuoteBegin-Barmutik+23:08:2005, 09:22 -->
<span class="vbquote">(Barmutik @ 23:08:2005, 09:22 )</span><!--QuoteEBegin-->А можно для личного обучения.. по подробнее про получения текста запроса через динамические представления...

[snapback]23920" rel="nofollow" target="_blank[/snapback]​

[/quote]
Что-то типа

SELECT  d.sql_text
 FROM v$session c, v$sqltext_with_newlines d
 WHERE c.username = USER
  AND c.sql_hash_value = d.hash_value
  AND c.sql_address = d.address
ORDER BY c.SID, d.piece

Ссылка скрыта от гостей

 

[dima_9]

Сразу извиняюсь - тут были проблемы интернет

2 ????

"CREATE OR REPLACE TRIGGER"
"PRAGMA EXCEPTION_INIT (import, -4091)"

Мне не очень понятен синтаксис - это вы на чем ???


"Можно получать текущий текст запроса через динамические представления (так делают все утилиты мониторинга). Только всё это не очень правильное решение."


Модете привести простой пример ?
Почему не очень правильное решение ?

На Transact SQL можно использовать dbcc inputbuffer - только одно существенное ограничение 255 символов текста - это то же неправильное решение - почему ?


"Например по определенному событию (или job'ом) помечать одинаковые записи на двух серверах как import. "

Я не совсем понял про одинаковые записи на двух серверах


Надо просто хорошо разобраться в решаемой задаче - решение само придёт."


Задача - определить по инструкциям посылаемым приложением серверу и по изменениям данных определять - что же там произошло или какая операция выполнилысь (добавление записи через форму или import)

Хочется услышать ваше мнение по этому поводу

 

[Guest]

Да и если не сложно пример для моей ситуации с insert с динамическими представлениями (желательно на Transact SQL - просто кроме MS SQL 2000 ничего нет пока) - может быть это то что надо

 

[????]

<!--QuoteBegin-Guest+26:08:2005, 13:15 -->
<span class="vbquote">(Guest @ 26:08:2005, 13:15 )</span><!--QuoteEBegin-->просто кроме MS SQL 2000 ничего нет пока

[snapback]24130" rel="nofollow" target="_blank[/snapback]​

[/quote]
а у меня кроме Oracle'а ничего нет пока
<!--QuoteBegin-dima_9+26:08:2005, 13:07 -->
<span class="vbquote">(dima_9 @ 26:08:2005, 13:07 )</span><!--QuoteEBegin-->Задача - определить по инструкциям посылаемым приложением серверу и по изменениям данных определять - что же там произошло или какая операция выполнилысь (добавление записи через форму или import)

[snapback]24129" rel="nofollow" target="_blank[/snapback]​

[/quote]
а зачем определять? Ну вставили данные и хорошо. А кто когда и как по идее не важно. А если важно, то объясните почему.<!--QuoteBegin-dima_9+26:08:2005, 13:07 -->
<span class="vbquote">(dima_9 @ 26:08:2005, 13:07 )</span><!--QuoteEBegin-->"CREATE OR REPLACE TRIGGER"
"PRAGMA EXCEPTION_INIT (import, -4091)"

[snapback]24129" rel="nofollow" target="_blank[/snapback]​

[/quote]
PL/SQL (Oracle)

 

[????]

<!--QuoteBegin-Guest+26:08:2005, 13:15 -->
<span class="vbquote">(Guest @ 26:08:2005, 13:15 )</span><!--QuoteEBegin-->MS SQL 2000

[snapback]24130" rel="nofollow" target="_blank[/snapback]​

[/quote]
если доставить sp3, то можеш просто использовать fn_get_sql как и предлогал Barmutik

Ссылка скрыта от гостей

Ссылка скрыта от гостей

и т.д.

 

[Guest]

Проблема в том, что я не совсем понимаю как использовать fn_get_sql, когда код приложения нельзя изменить... Я вставляю вызов этой функции в триггер к примеру на insert и вместо текста инструкции получаю код триггера. Мне тут объяснили что так и должно быть, так как при использовании fn_get_sql
не обойтись без модификации кода приложения, посылающего insert - нужно чтобы в бэтче с insert-ом было считано и запомнено значение sql_handle из в master.dbo.sysprocesses. Куда-нибудь во временную таблицу, чтобы его можно было прочитать в триггере.


Еще вопрос по поводу определения выполненной инструкции (текста) или транзакции раз у вас oracle - есть что-нибудь в вашем oracle для решения этой задачи определения текста ???

"а зачем определять? Ну вставили данные и хорошо. А кто когда и как по идее не важно. А если важно, то объясните почему"


Есть приложение, выполняются две операции (смотрите 3 сообщение с начала) добавление из формы и импорт документов - надо отделить одну операцию от другой. Не стоит предлагать мне решения для конкретного примера - понятно что для каждого конкретного примера можно как-нибудь выкрутится. На мой взгляд - достаточно универсальное решение в определении текста выполненной инструкции. Теперь надеюсь понятно почему важно. Еще могу сказать что это разделение не ннужно этому приложению - оно нужно другому приложению, пользователю


Да и к сожалению не могу проверить ваш код триггера и остального, а поскольку вы наверно представляете что мне надо - то можете сказать этот ваш код сможет решать подобные задачи ?

 

[????]

<!--QuoteBegin-Guest+26:08:2005, 17:42 -->
<span class="vbquote">(Guest @ 26:08:2005, 17:42 )</span><!--QuoteEBegin-->Да и к сожалению не могу проверить ваш код триггера

[snapback]24159" rel="nofollow" target="_blank[/snapback]​

[/quote]
Он работает. Смотрите мой первый пост.