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

Откуда: Москва
Сообщений: 7
У меня простейшая задача: при вводе новой строки в таблицу-1, в таблице-2 должен появиться новый столбец, название которого формируется на основании значения поля номера записи введенной строки.
Для начала, пытаюсь создать просто некоторое поле, к примеру «Z» в таблице-2 («SLR»). Пишу триггер:
DECLARE
v_cursor NUMBER;
v_sql VARCHAR2 (200);
v INTEGER;
BEGIN
v_cursor := DBMS_SQL.open_cursor;
v_sql := 'alter table SLR add (Z number)';
DBMS_SQL.parse (v_cursor, v_sql, DBMS_SQL.native);
v := DBMS_SQL.EXECUTE (v_cursor);
DBMS_SQL.close_cursor (v_cursor);
END;

Либо через EXECUTE:
v_sql := 'alter table SLR add (Z number)';
EXECUTE IMMEDIATE (v_sql);


В триггере это не работает, однако в хранимой процедуре срабатывает без проблем. Может есть какие-то ограничения по триггерам?.. (я только недавно работаю с PL/SQL)
25 дек 06, 12:07    [3577062]     Ответить | Цитировать Сообщить модератору
 Re: Динамическое создание столбца по триггеру.  [new]
_spy_
Member

Откуда: Москва
Сообщений: 823
Триггер нужно оформить как автономную транзакцию. Только стандартное замечание для топиков такого характера - хорошенько подумать, а нужна ли такая бизнес-логика?
25 дек 06, 12:16    [3577106]     Ответить | Цитировать Сообщить модератору
 Re: Динамическое создание столбца по триггеру.  [new]
Oleg Afanasiev
Member

Откуда: Киев
Сообщений: 3742
Restrictions on Creating Triggers

-----------------------
Вечны налоги,
Смерть и потеря данных.
Что на этот раз?
Картинка с другого сайта.
25 дек 06, 12:22    [3577142]     Ответить | Цитировать Сообщить модератору
 Re: Динамическое создание столбца по триггеру.  [new]
SQL*Plus
Member

Откуда: Россия, Москва
Сообщений: 7097
Похоже, что вы задумали нечто жуткое...
Описывайте цель, а не отдельный шаг
25 дек 06, 12:48    [3577343]     Ответить | Цитировать Сообщить модератору
 Re: Динамическое создание столбца по триггеру.  [new]
Nira
Member

Откуда: Москва
Сообщений: 7
Ничего жуткого не задумывалось, :) такая задача вместе со структурой БД досталась в "наследство" от предыдущих программистов..
25 дек 06, 12:54    [3577382]     Ответить | Цитировать Сообщить модератору
 Re: Динамическое создание столбца по триггеру.  [new]
SQL*Plus
Member

Откуда: Россия, Москва
Сообщений: 7097
Nira
Ничего жуткого не задумывалось, :) такая задача вместе со структурой БД досталась в "наследство" от предыдущих программистов..
Если несложно и несекретно, расскажите, пожалуйста, зачем может потребоваться:
- "при вводе новой строки в таблицу-1, в таблице-2 должен появиться новый столбец,
название которого формируется на основании значения поля номера записи введенной строки."?
- делать это именно в триггере?

P.S. "Предыдущие программисты" тоже "совсем недавно работали с PL/SQL"
и разрабатывали структуры баз данных? :-)
25 дек 06, 13:02    [3577429]     Ответить | Цитировать Сообщить модератору
 Re: Динамическое создание столбца по триггеру.  [new]
softwarer
Member

Откуда: 127.0.0.1
Сообщений: 42182
Блог
SQL*Plus
расскажите, пожалуйста, зачем может потребоваться:
- "при вводе новой строки в таблицу-1, в таблице-2 должен появиться новый столбец,

В принципе, если есть возможность выполнять это "в технологические часы", это не худший вариант реализации flexfield-ности.
25 дек 06, 13:12    [3577482]     Ответить | Цитировать Сообщить модератору
 Re: Динамическое создание столбца по триггеру.  [new]
contr
Member

Откуда:
Сообщений: 1909
SQL*Plus
P.S. "Предыдущие программисты" тоже "совсем недавно работали с PL/SQL"
и разрабатывали структуры баз данных? :-)

Есть подозрение, что "Предыдущие программисты" не сумели выпросить у "Предыдущего DBA" select catalog role и сделали свой самостийный словарь ;)
25 дек 06, 13:13    [3577496]     Ответить | Цитировать Сообщить модератору
 Re: Динамическое создание столбца по триггеру.  [new]
Nira
Member

Откуда: Москва
Сообщений: 7
Эта операция по изменению структуры может производится пару раз в году (т. е. времени особо не занимает)и необходима для поддержания таблицы, которая уже устарела. Вместо нее была создана другая, более приемлемого вида, куда и вводятся оперативные данные. Однако ряд пользователей (число их определяется не одним десятком) используют различные расчетные модули и интерфейсы, использующие именно эту старую таблицу, и было бы глупо кидаться на переписывания огромного числа расчетных баз данных из-за одной несчастной таблички.. проще ее поддерживать а актуальном состоянии.
25 дек 06, 13:24    [3577569]     Ответить | Цитировать Сообщить модератору
 Re: Динамическое создание столбца по триггеру.  [new]
Bely
Member

Откуда: Москва
Сообщений: 1903
Nira
Эта операция по изменению структуры может производится пару раз в году (т. е. времени особо не занимает)и необходима для поддержания таблицы, которая уже устарела. Вместо нее была создана другая, более приемлемого вида, куда и вводятся оперативные данные. Однако ряд пользователей (число их определяется не одним десятком) используют различные расчетные модули и интерфейсы, использующие именно эту старую таблицу, и было бы глупо кидаться на переписывания огромного числа расчетных баз данных из-за одной несчастной таблички.. проще ее поддерживать а актуальном состоянии.
Может вам просто создать триггерЮ который будет не колонку вставлять, а отправлять письмо:
"Уважаемые разработчики, вы конечно, умные ребята - но создайте в этой старой всеми забытой таблице ЕЩЕ ОДНО ПОЛЕ!
А то у нас ВСЯ БУХГАЛТЕРИЯ ПОВЕСИТСЯ!!!"

Отправлять можно с копией генеральному директору :)
25 дек 06, 13:30    [3577614]     Ответить | Цитировать Сообщить модератору
 Re: Динамическое создание столбца по триггеру.  [new]
Nira
Member

Откуда: Москва
Сообщений: 7
Спасибо за совет, но все таки хотелось бы создать триггер, который так и не получается… :)

В процедуре все операции проходят отлично, но как только пытаюсь ее запустить из триггера, уже не работает.. загадка какая-то..
25 дек 06, 13:38    [3577671]     Ответить | Цитировать Сообщить модератору
 Re: Динамическое создание столбца по триггеру.  [new]
SQL*Plus
Member

Откуда: Россия, Москва
Сообщений: 7097
Nira
Спасибо за совет, но все таки хотелось бы создать триггер, который так и не получается… :)

В процедуре все операции проходят отлично, но как только пытаюсь ее запустить из триггера, уже не работает.. загадка какая-то..
А два раза в год запустить процедуру никак нельзя?
В том, что работает у вас данная операция выполняется именно триггером?
25 дек 06, 13:40    [3577691]     Ответить | Цитировать Сообщить модератору
 Re: Динамическое создание столбца по триггеру.  [new]
Nira
Member

Откуда: Москва
Сообщений: 7
Из-за того, что столбец добавляется так редко, о нем уже никто не помнит, и происходит такая ситуация, что один из пользователей добавляет новую строку в справочник, соответствующих изменений в устаревшей таблице никто сделал, так как не помнил или не знал об этом, и у всех, кто пользуется этой устаревшей таблицей начинают «глючить» интерфейсы. Пока разберутся, в чем дело, масса времени уходит. Хотелось бы все привести в порядок..

Я пыталась выполнить alter table …. Add … в триггере (самый казалось бы простой вариант), он не работает, вынесла эту же команду в хранимую процедуру – работает.. запустила процедуру из триггера – опять не работает..
Придется пока запускать процедуру из интерфейса, хотя это не лучший вариант..
25 дек 06, 13:52    [3577796]     Ответить | Цитировать Сообщить модератору
 Re: Динамическое создание столбца по триггеру.  [new]
Jannny
Member

Откуда: Спб
Сообщений: 6424
Nira
запустила процедуру из триггера – опять не работает..
Ставите в триггер джоб, который создаст поле.

Но подход у Вас, конечно, своеобразный... :)
25 дек 06, 14:19    [3578032]     Ответить | Цитировать Сообщить модератору
 Re: Динамическое создание столбца по триггеру.  [new]
SQL*Plus
Member

Откуда: Россия, Москва
Сообщений: 7097
Читайте, что выше предложено _spy_ и решайте делать вам это в триггере или нет...
25 дек 06, 14:22    [3578053]     Ответить | Цитировать Сообщить модератору
 Re: Динамическое создание столбца по триггеру.  [new]
softwarer
Member

Откуда: 127.0.0.1
Сообщений: 42182
Блог
Nira
В процедуре все операции проходят отлично, но как только пытаюсь ее запустить из триггера, уже не работает.. загадка какая-то..

Если Вы не будете читать того, что написано выше, то так навсегда и останется загадкой. Прямой ответ был дан дважды.
25 дек 06, 14:24    [3578073]     Ответить | Цитировать Сообщить модератору
 Re: Динамическое создание столбца по триггеру.  [new]
Jannny
Member

Откуда: Спб
Сообщений: 6424
SQL*Plus
Читайте, что выше предложено _spy_ и решайте делать вам это в триггере или нет...
Угу, а главное насколько интересно, чтобы столбец не был создан, если запись так и не вставится...
25 дек 06, 14:25    [3578081]     Ответить | Цитировать Сообщить модератору
 Re: Динамическое создание столбца по триггеру.  [new]
RA\/EN
Member

Откуда:
Сообщений: 3530
SQL> create table atab(id number);

Table created

SQL> create table atab_ff(dummy number);

Table created

SQL> create procedure add_ff(f_name in varchar2) is
  2  pragma autonomous_transaction;
  3  begin
  4  execute immediate 'alter table atab_ff add ('||f_name||' number)';
  5  commit;
  6  end;
  7  /

Procedure created

SQL> create or replace trigger bi_atab before insert on atab for each row
  2  begin
  3  add_ff('C'||:new.id);
  4  end;
  5  /

Trigger created

SQL> insert into atab(id) values (2006);

1 row inserted

SQL> commit;

Commit complete

SQL> desc atab_ff
Name  Type   Nullable Default Comments 
----- ------ -------- ------- -------- 
DUMMY NUMBER Y                         
C2006 NUMBER Y                         

SQL> 

Вот только подумайте, что будет, если ROLLBACK после добавления записи...
25 дек 06, 14:27    [3578100]     Ответить | Цитировать Сообщить модератору
 Re: Динамическое создание столбца по триггеру.  [new]
softwarer
Member

Откуда: 127.0.0.1
Сообщений: 42182
Блог
RA\/EN
Вот только подумайте, что будет, если ROLLBACK после добавления записи...

Это как раз тот случай, когда очень уместно использование ХП для вставки данных. Вставляем запись, делаем alter - и в случае успешного создания колонки запись автоматом закоммичена.
25 дек 06, 14:34    [3578163]     Ответить | Цитировать Сообщить модератору
 Re: Динамическое создание столбца по триггеру.  [new]
RA\/EN
Member

Откуда:
Сообщений: 3530
softwarer
RA\/EN
Вот только подумайте, что будет, если ROLLBACK после добавления записи...

Это как раз тот случай, когда очень уместно использование ХП для вставки данных. Вставляем запись, делаем alter - и в случае успешного создания колонки запись автоматом закоммичена.

Здесь есть и другая сторона - если в дальнейшей обработке возникает ROLLBACK, то он "недороллбэчит". Поэтому DDL надо встраивать не просто в процедру, а в весть "процесс", частью которого является процедура, в заключительную стадию непосредственно перед COMMIT, если он server-side. И все равно в автономной транзакции, чтобы откатить операцию при невозможности добавить поле.
Частично это можно обойти джобом, который синхронизирует содержимое таблички-1 и структуру таблицчки-2.
Но исходя из текущего положения дел (пару раз в год, рассинхронизация еще никому не стоила рбаоты) можно делать как угодно (хоть никак )
25 дек 06, 15:00    [3578400]     Ответить | Цитировать Сообщить модератору
 Re: Динамическое создание столбца по триггеру.  [new]
softwarer
Member

Откуда: 127.0.0.1
Сообщений: 42182
Блог
RA\/EN
Здесь есть и другая сторона - если в дальнейшей обработке возникает ROLLBACK, то он "недороллбэчит".

Есть. Но это уже можно обработать в той же процедуре.

RA\/EN
Частично это можно обойти джобом, который синхронизирует содержимое таблички-1 и структуру таблицчки-2.

Я бы советовал избегать подобных job-ов там, где это возможно. Иначе неизбежен геморрой на тему "в таблице есть, job еще не успел отработать", "в таблице есть, попытка создания завершилась с ошибкой, а на запись уже успели повесить внешний ключ" и так далее.
25 дек 06, 15:09    [3578475]     Ответить | Цитировать Сообщить модератору
 Re: Динамическое создание столбца по триггеру.  [new]
Nira
Member

Откуда: Москва
Сообщений: 7
Спасибо огромное, все работает отлично.. не хватало pragma autonomous_transaction..

С ROLLBACK конечно надо будет что-то делать, но можно даже без него некоторое время обойтись..
25 дек 06, 15:15    [3578541]     Ответить | Цитировать Сообщить модератору
 Re: Динамическое создание столбца по триггеру.  [new]
RA\/EN
Member

Откуда:
Сообщений: 3530
softwarer
Есть. Но это уже можно обработать в той же процедуре.

Це как? DLL = commit+DDL(здесь может быть exception)+commit, если не знаешь, что вызывалось ранее - облом. Разве только DLL делать в автономке - тогда да. Но, кстати, в рабочее время можно получить "объект используется".
softwarer
Я бы советовал избегать подобных job-ов там, где это возможно. Иначе неизбежен геморрой на тему "в таблице есть, job еще не успел отработать", "в таблице есть, попытка создания завершилась с ошибкой, а на запись уже успели повесить внешний ключ" и так далее.
Да, есть такой недостаток.
--
В общем, я считаю, что все это, при ПРАВИЛЬНОМ дизайне, должен делать не триггер, а сервисная процедура, выполняемая админом в maintenance time.
25 дек 06, 15:41    [3578751]     Ответить | Цитировать Сообщить модератору
 Re: Динамическое создание столбца по триггеру.  [new]
dmidek
Member

Откуда: Киев - Дортмунд
Сообщений: 66810
Nira

С ROLLBACK конечно надо будет что-то делать, но можно даже без него некоторое время обойтись..

25 дек 06, 15:46    [3578804]     Ответить | Цитировать Сообщить модератору
 Re: Динамическое создание столбца по триггеру.  [new]
softwarer
Member

Откуда: 127.0.0.1
Сообщений: 42182
Блог
RA\/EN
softwarer
Есть. Но это уже можно обработать в той же процедуре.

Це как?

Ээ.... А на что у нас блок exception? Ловим неудачу создания и удаляем добавленную-закоммиченную строчку.

Насчет правильного дизайна - можно наверное долго предлагать варианты. Я полагаю необходимым условием правильного дизайна - выполнение ключевых требований пользователя; в частности, если он говорит "я не хочу никакого админа и хочу иметь интерфейс добавления мемо-полей, которые буду заполнять и выводить в отчеты" - можно думать над вариантами, но прежде всего нужно ответить "да, мы сделаем это, и постараемся сделать так, чтобы это не мешало другим функциям системы". Как именно сделаем - таблицы там, триггеры итп - уже можно смотреть, но сейчас это будет уже "ради искусства".
25 дек 06, 15:48    [3578828]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить
 
Лучший учебный центр Microsoft!
Новейшие курсы Microsoft SQL Server 2014!
Статус Academy Oracle. Очень привлекательные цены на курсы Oracle!
Отсрочка платежа или скидка 5% на комплексные программы!