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

Откуда: Belarus
Сообщений: 81
Есть триггер INSTEAD OF INSERT в MS SQL, который заменяет вставляемые данные:
обновлением для данных со значениями field1, где field1>0;
обновлением для данных со значениями field2, где field2>0 и field1=0;
вставкой данных, которых нет в таблице с такими field1, где field1>0;
вставкой данных, которых нет в таблице с такими field2, где field2>0 и field1=0;


UPDATE tab1 set fields=a.fields, fieldN=1 FROM inserted a WHERE tab1.field1=inserted.field1 AND tab1.field1>0
UPDATE tab1 set fields=a.fields, fieldN=1 FROM inserted a WHERE tab1.field1=inserted.field1 AND tab1.field2=inserted.field2 AND tab1.field2>0 AND tab1.field1=0
INSERT INTO tab1 (fields, fieldN) SELECT a.fields,1 FROM inserted a WHERE a.field1=0 and NOT EXISTS (SELECT * FROM tab1 b WHERE tab1.field1=inserted.field1 AND tab1.field2=inserted.field2 AND tab1.field2>0 AND tab1.field1=0)
INSERT INTO tab1 (fields, fieldN) SELECT a.fields,1 FROM inserted a WHERE a.field1>0 and NOT EXISTS (SELECT * FROM tab1 b WHERE tab1.field1=inserted.field1 AND tab1.field1=0)

Как это можно сделать триггером на Oracle.
В какую сторону смотреть?
16 июн 09, 00:45    [7302930]     Ответить | Цитировать Сообщить модератору
 Re: Триггер, заменяющий Insert на Insert или Update в зависимости от данных  [new]
Вячеслав Любомудров
Member

Откуда: Владивосток
Сообщений: 18486
Так же в сторону триггера INSTEAD OF на представление (view)
16 июн 09, 01:22    [7302988]     Ответить | Цитировать Сообщить модератору
 Re: Триггер, заменяющий Insert на Insert или Update в зависимости от данных  [new]
ShadowZ
Member

Откуда:
Сообщений: 45
Mikalai,
а MERGE не поможет в данном случае, к тому же можно обойтись без триггера?
16 июн 09, 08:10    [7303185]     Ответить | Цитировать Сообщить модератору
 Re: Триггер, заменяющий Insert на Insert или Update в зависимости от данных  [new]
Mikalai
Member

Откуда: Belarus
Сообщений: 81
ShadowZ, MERGE не подойдет.

Данные в таблицу вносятся из нескольких разных источников. Формируется XML, а дальше используется DBMS_XMLSave.insertXML.
field1 - это идентификатор записи у первого источника, а field2 - у второго. Вот и приходится триггером различать, есть уже такие данные и нужно выполнить обновление, или нету, и нужно делать insert.

По поводу триггера INSTEAD OF INSERT - спасибо.
Вписал в него Update tab1 SET fields=:new.fields where ...
Работает.

С Insert пока по аналогии не получилось... Только insert INTO tab1 (fields) values (:new.fields)
Пока не знаю, как прицепить условия.
И почему то вместе с Update выполнился и Insert, хотя в триггере были записаны только 2 команды Update.
16 июн 09, 12:02    [7304183]     Ответить | Цитировать Сообщить модератору
 Re: Триггер, заменяющий Insert на Insert или Update в зависимости от данных  [new]
Jannny
Member

Откуда: Спб
Сообщений: 6424
Mikalai
По поводу триггера INSTEAD OF INSERT - спасибо.
Вписал в него Update tab1 SET fields=:new.fields where ...
Работает.

С Insert пока по аналогии не получилось... Только insert INTO tab1 (fields) values (:new.fields)
Пока не знаю, как прицепить условия.
И почему то вместе с Update выполнился и Insert, хотя в триггере были записаны только 2 команды Update.
Никто не в курсе, что именно Вы неправильно написали... Если Вы, конечно, задаете вопрос, а не просто так моноложите :)
16 июн 09, 14:12    [7305150]     Ответить | Цитировать Сообщить модератору
 Re: Триггер, заменяющий Insert на Insert или Update в зависимости от данных  [new]
suPPLer
Member

Откуда: Харків, Україна
Сообщений: 7794
Блог
Mikalai,

приведите, пожалуйста, здесь текст Вашего триггера, укажите, что именно в нём работает и не работает, и что Вы хотите этим триггером сделать. Тогда будет Вам ответ. :)
16 июн 09, 15:41    [7305727]     Ответить | Цитировать Сообщить модератору
 Re: Триггер, заменяющий Insert на Insert или Update в зависимости от данных  [new]
Mikalai
Member

Откуда: Belarus
Сообщений: 81
:) да, сам думал покопаться.
По вчерашнему совету сделал View:

CREATE OR REPLACE VIEW TAB1_INFO AS
SELECT * FROM SHEMA.tab1;
/

А триггер на данный момент выглядит так:

CREATE OR REPLACE TRIGGER "SHEMA"."TAB1_INS" INSTEAD OF
INSERT ON "SHEMA"."TAB1_INFO" FOR EACH ROW
begin
UPDATE tab1 SET fields=:new.fields WHERE tab1.filed1>0 AND tab1.field1=:new.field1;
UPDATE tab1 ...;

А что делать с Insertами пока не знаю. Так неправильно:

--первый insert
--второй insert
IF (:new.field1>0 and NOT EXISTS (SELECT * FROM tab1 b WHERE :new.field1=b.field1 AND :new.field1=0)) THEN
INSERT INTO tab1 (fields) values (:new.fields);
-- а как по другому с INSERT сделать, пока не знаю :)
END IF

end;/

На таблице есть еще один триггер BEFORE INSERT:

CREATE OR REPLACE TRIGGER "SHEMA"."TAB1_INS0" BEFORE
INSERT ON "SHEMA"."TAB1" FOR EACH ROW
begin
if :new.field0 is null then
select sq_tab1.nextval into :new.field0 from dual;
end if;
end;
/
16 июн 09, 21:13    [7307330]     Ответить | Цитировать Сообщить модератору
 Re: Триггер, заменяющий Insert на Insert или Update в зависимости от данных  [new]
Jannny
Member

Откуда: Спб
Сообщений: 6424
Ну что конкретно Вы хотите инсертить, я не понимаю. Но, как минимум, это неправильный Оракловый синтаксис. NOT EXISTS допустим только в select. А select не может быть операндом в if :)
Mikalai
and NOT EXISTS (SELECT * FROM tab1 b WHERE  :new.field1=b.field1 AND :new.field1=0)) 
17 июн 09, 10:11    [7308387]     Ответить | Цитировать Сообщить модератору
 Re: Триггер, заменяющий Insert на Insert или Update в зависимости от данных  [new]
Mikalai
Member

Откуда: Belarus
Сообщений: 81
Jannny
Ну что конкретно Вы хотите инсертить, я не понимаю. Но, как минимум, это неправильный Оракловый синтаксис.

Что с синтаксисом не лады, Oracle мне тоже написал. :)

INSERTить хочу то, что с чем не сделалася UPDATE.
Работа состоит в следующем:
1 - делается вставка через insertXML.

Обычный INSERT заменяю триггером INSTEAD OF INSERT:
2 - для записей, которые по условиям field1, field2 уже есть в таблице, выполняется UPDATE
3 - для записей, которых по условиям field1, field2 нет в таблице, выполняется INSERT

Все.
17 июн 09, 10:37    [7308591]     Ответить | Цитировать Сообщить модератору
 Re: Триггер, заменяющий Insert на Insert или Update в зависимости от данных  [new]
Jannny
Member

Откуда: Спб
Сообщений: 6424
это Вам надо простой оператор MERGE писать. Версия у Вас какая?
17 июн 09, 10:49    [7308677]     Ответить | Цитировать Сообщить модератору
 Re: Триггер, заменяющий Insert на Insert или Update в зависимости от данных  [new]
Jannny
Member

Откуда: Спб
Сообщений: 6424
Jannny
это Вам надо простой оператор MERGE писать. Версия у Вас какая?
На всякий случай, MERGE
PS: Уважая
Mikalai
да, сам думал покопаться.
сам пример не пишу, там не так сложно :)
17 июн 09, 11:24    [7309004]     Ответить | Цитировать Сообщить модератору
 Re: Триггер, заменяющий Insert на Insert или Update в зависимости от данных  [new]
Mikalai
Member

Откуда: Belarus
Сообщений: 81
MJannny, спасибо.

Если не ошибаюсь, вместо моих 4 UPDATE-INSERT можно выполнить 1 MERGE:

MERGE INTO tab1 D
USING (SELECT :new.fields from ??? )
ON ((D.field1=:new.field1 AND new.field1>0 ) OR (D.field2=:new.field2 AND :nef.field1=0)
WHEN MATCHED THEN UPDATE SET D.fields=:new.fields
WHEN NOT MATCHED THEN INSERT (D.fields) VALUES (:new.fields);

Указываю таблицу, куда помещать результат,
указываю откуда что брать (пока не знаю, как поступающие в триггер данные описать, SELECT :new.fields FROM откуда?)
ON - мой фильтр наличия или отсутствия данных в таблице
WHEN MATCHED - команда, которую выполняю, если условие фильтра выполняется
WHEN NOT MATCHED - команда, которую выполняю, если условие фильтра не выполняется
17 июн 09, 12:10    [7309401]     Ответить | Цитировать Сообщить модератору
 Re: Триггер, заменяющий Insert на Insert или Update в зависимости от данных  [new]
Mikalai
Member

Откуда: Belarus
Сообщений: 81
Да, версия Oracle 9.
17 июн 09, 12:13    [7309431]     Ответить | Цитировать Сообщить модератору
 Re: Триггер, заменяющий Insert на Insert или Update в зависимости от данных  [new]
Jannny
Member

Откуда: Спб
Сообщений: 6424
Mikalai
пока не знаю, как поступающие в триггер данные описать, SELECT :new.fields FROM откуда?
from dual
17 июн 09, 12:18    [7309473]     Ответить | Цитировать Сообщить модератору
 Re: Триггер, заменяющий Insert на Insert или Update в зависимости от данных  [new]
Elic
Member

Откуда:
Сообщений: 29991
Jannny
Mikalai
пока не знаю, как поступающие в триггер данные описать, SELECT :new.fields FROM откуда?
from dual
Нехорошему учишь? :)
17 июн 09, 12:19    [7309484]     Ответить | Цитировать Сообщить модератору
 Re: Триггер, заменяющий Insert на Insert или Update в зависимости от данных  [new]
Mikalai
Member

Откуда: Belarus
Сообщений: 81
Elic
Нехорошему учишь? :)

Очепятался человек :)

На данный момент мой триггер INSTEAD OF INSERT ошибок не дает, но записи не обновляются, только добавляются. Буду дальше копаться.

begin
MERGE INTO tab1
USING dual
ON ((tab1.field1=:new.field1 AND new.field1>0 ) OR (tab1.field2=:new.field2 AND :new.field1=0)
WHEN MATCHED THEN UPDATE SET fields=:new.fields
WHEN NOT MATCHED THEN INSERT (fields) VALUES (:new.fields);
end;
17 июн 09, 21:04    [7312909]     Ответить | Цитировать Сообщить модератору
 Re: Триггер, заменяющий Insert на Insert или Update в зависимости от данных  [new]
Mikalai
Member

Откуда: Belarus
Сообщений: 81
В общем на данный момент не совсем понимаю что и как работает...

На таблице есть два триггера:
1. INSTEAD OF INSERT (с MERGE).
2. BEFORE INSERT (в нем проставляется значение последовательности)

Если в INSTEAD OF INSERT записана команда MERGE, то записи просто добавляются (никакого влияния этот MERGE не оказывает).

Если триггер INSTEAD OF INSERT убрать, а команду MERGE поместить во второй триггер (BEFORE INSERT), то опять же добавляется новая запись вне зависимости от условия в MERGE плюс правильно выполняется UPDATE из MERGE (если условие MERGE выполняется).

Вопрос... В Oracle триггер INSTEAD OF INSERT не отменяет выполнения INSERT на то, что в нем записано?
17 июн 09, 22:51    [7313110]     Ответить | Цитировать Сообщить модератору
 Re: Триггер, заменяющий Insert на Insert или Update в зависимости от данных  [new]
Вячеслав Любомудров
Member

Откуда: Владивосток
Сообщений: 18486
Есть большое подозрение, что триннер INSTEAD OF у тебя на представление (view), а вставляешь ты в таблицу напрямую, минуя представление :)
18 июн 09, 02:01    [7313522]     Ответить | Цитировать Сообщить модератору
 Re: Триггер, заменяющий Insert на Insert или Update в зависимости от данных  [new]
Mikalai
Member

Откуда: Belarus
Сообщений: 81
Вячеслав Любомудров
Есть большое подозрение, что триннер INSTEAD OF у тебя на представление (view), а вставляешь ты в таблицу напрямую, минуя представление :)

Да, сижу, ковыряюсь в инете, тоже к такому выводу пришел. :)
Триггер INSTEAD OF INSERT на view TAB1_INFO
а триггер BEFORE INSERT на таблицу tab1.

Только с решением пока слабо :)
Заменил начало:
MERGE INTO TAB1_INFO D
USING dual
ON ((D.field1=:new.field1 AND new.field1>0 ) OR (D.field2=:new.field2 AND :new.field1=0)

Все равно все что в INSTEAD OF INSERT игнорируется...
18 июн 09, 02:16    [7313532]     Ответить | Цитировать Сообщить модератору
 Re: Триггер, заменяющий Insert на Insert или Update в зависимости от данных  [new]
Вячеслав Любомудров
Member

Откуда: Владивосток
Сообщений: 18486
До тех пор, пока ты не надумаешь показать вывод (например из sqlplus) команд (и результата их выполнения) создания объектов, вставки данных, проверки, что все плохо, вряд ли кто сможет тебе помочь.
И еще одно важно -- никто ломать глаза неформатировынным текстом не будет, используй тег [ SRC ] (как использовать -- почитай ФАК форума)
18 июн 09, 02:36    [7313544]     Ответить | Цитировать Сообщить модератору
 Re: Триггер, заменяющий Insert на Insert или Update в зависимости от данных  [new]
Jannny
Member

Откуда: Спб
Сообщений: 6424
Mikalai
Вячеслав Любомудров
Есть большое подозрение, что триннер INSTEAD OF у тебя на представление (view), а вставляешь ты в таблицу напрямую, минуя представление :)

Да, сижу, ковыряюсь в инете, тоже к такому выводу пришел. :)
Триггер INSTEAD OF INSERT на view TAB1_INFO
а триггер BEFORE INSERT на таблицу tab1.

Только с решением пока слабо :)
Заменил начало:
MERGE INTO TAB1_INFO D
USING dual
ON ((D.field1=:new.field1 AND new.field1>0 ) OR (D.field2=:new.field2 AND :new.field1=0)

Все равно все что в INSTEAD OF INSERT игнорируется...
Надо не в триггере(на вью) обращаться к вью :) А во всем остальном коде(в обычных инсертах и т.д.) никогда больше не обращаться напрямую к таблице, только ко вью. Чтобы не менять весь код, это можно реализовать с помощью синонимов.
18 июн 09, 11:17    [7314312]     Ответить | Цитировать Сообщить модератору
 Re: Триггер, заменяющий Insert на Insert или Update в зависимости от данных  [new]
Mikalai
Member

Откуда: Belarus
Сообщений: 81
В общем вот что я "химичу":
CREATE TABLE TAB1 ( 
  FIELD0      NUMBER (19)   NOT NULL,
  FIELD1      NUMBER (5)    NOT NULL, 
  FIELD2      VARCHAR2 (20) NOT NULL, 
  FIELD3      VARCHAR2 (20)    NOT NULL,
  FIELD4      NUMBER (5)    NOT NULL);
/

CREATE SEQUENCE SQ_TAB1 INCREMENT BY 1 NOCACHE NOCYCLE;
/
CREATE OR REPLACE TRIGGER "TAB1_INS1" BEFORE
INSERT ON "TAB1" FOR EACH ROW 
begin
   if :new.field0 is null then
     select sq_tab1.nextval into :new.field0 from dual;
   end if;
end;
/

CREATE OR REPLACE VIEW TAB1_INFO AS
SELECT * FROM TAB1;
/

CREATE OR REPLACE TRIGGER "TAB1_INS0" INSTEAD OF
INSERT ON "TAB1_INFO" FOR EACH ROW 
begin
MERGE INTO tab1 D
USING dual
ON ((D.field1>0 AND D.field1=:new.field1) OR (D.field2=:new.field2 AND D.field1=0))
WHEN MATCHED THEN UPDATE SET field1=:new.field1,field2=:new.field2,field3=:new.field3,field4=1
WHEN NOT MATCHED THEN INSERT (field1, field2, field3, field4) VALUES (:new.field1,:new.field2,:new.field3,1);
end;
/

INSERT INTO tab1 (field1, field2, field3, field4) VALUES (10,'qwerty','aaa',0);
INSERT INTO tab1 (field1, field2, field3, field4) VALUES (10,'ytrewq','bbb',0);
INSERT INTO tab1 (field1, field2, field3, field4) VALUES (11,'asdf','ghj',0);
INSERT INTO tab1 (field1, field2, field3, field4) VALUES (0,'abc','ccc',0);
INSERT INTO tab1 (field1, field2, field3, field4) VALUES (0,'abc','cccddd',0);
INSERT INTO tab1 (field1, field2, field3, field4) VALUES (0,'ghi','vbn',0);

SELECT * FROM tab1;

DROP TABLE TAB1;
DROP SEQUENCE SQ_TAB1;
DROP VIEW TAB1_INFO;

в результате INSERTов:
    FIELD0     FIELD1 FIELD2               FIELD3                   FIELD4
---------- ---------- -------------------- -------------------- ----------
         1         10 qwerty               aaa                           0
         2         10 ytrewq               bbb                           0
         3         11 asdf                 ghj                           0
         4          0 abc                  ccc                           0
         5          0 abc                  cccddd                        0
         6          0 ghi                  vbn                           0

6 строк выбрано.
Хотя строк должно быть 4.
Попробую переосмыслить триггер INSTEAD в соответствии с советом...
18 июн 09, 11:38    [7314466]     Ответить | Цитировать Сообщить модератору
 Re: Триггер, заменяющий Insert на Insert или Update в зависимости от данных  [new]
Elic
Member

Откуда:
Сообщений: 29991
Mikalai
В общем вот что я "химичу":
Ты видишь хоть один DML над TAB1_INFO?
Это намёк :)
18 июн 09, 11:43    [7314503]     Ответить | Цитировать Сообщить модератору
 Re: Триггер, заменяющий Insert на Insert или Update в зависимости от данных  [new]
Mikalai
Member

Откуда: Belarus
Сообщений: 81
Понял очередной раз, что Oracle и MS SQL отличаются :)
Триггер INSTEAD OF в MS SQL делается на таблицу, в Oracle на View.
Соотвественно чтобы вызвать этот триггер в Oracle, нужно вставлять данные во View (то, что мне сказали прямым текстом :) )
И мои INSERT INTO tab1 не вызывают триггер INSTEAD OF INSERT.

Не понимаю, почему во view INSERT INTO tab1_info данные в таблицу не вносит...
INSERT INTO tab1_info (field1, field2, field3, field4) VALUES (10,'qwerty','aaa',0);
INSERT INTO tab1_info (field1, field2, field3, field4) VALUES (10,'ytrewq','bbb',0);
INSERT INTO tab1_info (field1, field2, field3, field4) VALUES (11,'asdf','ghj',0);
INSERT INTO tab1_info (field1, field2, field3, field4) VALUES (0,'abc','ccc',0);
INSERT INTO tab1_info (field1, field2, field3, field4) VALUES (0,'abc','cccddd',0);
INSERT INTO tab1_info (field1, field2, field3, field4) VALUES (0,'ghi','vbn',0);

1 строка создана.
...
1 строка создана.
строки не выбраны

В тоже время удаление через view работает:
INSERT INTO tab1 (field1, field2, field3, field4) VALUES (10,'qwerty','aaa',0);
INSERT INTO tab1 (field1, field2, field3, field4) VALUES (10,'ytrewq','bbb',0);
INSERT INTO tab1 (field1, field2, field3, field4) VALUES (11,'asdf','ghj',0);
INSERT INTO tab1 (field1, field2, field3, field4) VALUES (0,'abc','ccc',0);
INSERT INTO tab1 (field1, field2, field3, field4) VALUES (0,'abc','cccddd',0);
INSERT INTO tab1 (field1, field2, field3, field4) VALUES (0,'ghi','vbn',0);

delete from tab1_info where field3!='cccddd';
SELECT * FROM tab1;

    FIELD0     FIELD1 FIELD2               FIELD3                   FIELD4
---------- ---------- -------------------- -------------------- ----------
         5          0 abc                  cccddd                        0

1 строка выбрана.
19 июн 09, 01:23    [7318394]     Ответить | Цитировать Сообщить модератору
 Re: Триггер, заменяющий Insert на Insert или Update в зависимости от данных  [new]
Вячеслав Любомудров
Member

Откуда: Владивосток
Сообщений: 18486
Mikalai
Не понимаю, почему во view INSERT INTO tab1_info данные в таблицу не вносит...
Вносишь в одной сессии, а проверяешь в другой ? :)
Как насчет коммита?
19 июн 09, 02:00    [7318440]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Oracle Ответить