ReSQL.ru
     
powered by simpleCommunicator - 2.0.61     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Oracle [игнор отключен] [закрыт для гостей] / compound trigger разные результаты при удалении
14 сообщений из 14, страница 1 из 1
compound trigger разные результаты при удалении
    #39682689
j2k
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
j2k
Участник
Не могу понять, что за фича? Есть две таблицы. На дочерней висит compound trigger и вторичный ключ с опцией cascade. Я получаю разные результаты при удалении записи напрямую из дочерней таблицы и удалении из родительской. Сам тест:

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
create table parent_tab (par_col1 number, par_col2 date);
alter table parent_tab add constraint parent_tab_PK primary key (par_col1);

create table child_tab (ch_col1 number, ch_col2 date, par_col1_ref number);
alter table child_tab add constraint child_tab_PK primary key (ch_col1);
alter table child_tab add constraint ref_parent_tab foreign key (par_col1_ref)  references parent_tab (par_col1) on delete cascade;


CREATE OR REPLACE TRIGGER child_tab_after_delete
 for delete on child_tab
compound trigger
  vId number:=0;

  after each row is
  begin
    vId:=:old.ch_col1;
    dbms_output.put_line('ИД построчное = '||vId);
  end after each row;    
  
  after statement is
  begin
    dbms_output.put_line('ИД Глобальное = '||vId);
  end after statement;
end child_tab_after_delete;


Наполняем данными

Код: plsql
1.
2.
3.
4.
5.
6.
7.
begin
  insert into parent_tab values (1, to_date('01.01.2018','dd.mm.yyyy'));
  insert into parent_tab values (2, to_date('01.01.2018','dd.mm.yyyy'));

  insert into child_tab values (10, to_date('01.01.2018','dd.mm.yyyy'),1);
  insert into child_tab values (20, to_date('01.01.2018','dd.mm.yyyy'),2);
end;



удаляем записи:
Код: plsql
1.
2.
3.
4.
5.
6.
begin
  delete from child_tab where par_col1_ref=2;
  rollback;
  delete from parent_tab where par_col1=2;
  rollback;
end;


Результат:
1) при удалении из потомка имеем:
Код: plsql
1.
2.
ИД построчное = 20
ИД Глобальное = 20


2) при удалении из родителя имеем:
Код: plsql
1.
2.
ИД построчное = 20
ИД Глобальное = 0



Версия:
Код: plsql
1.
2.
3.
4.
5.
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE	11.2.0.4.0	Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
...
Рейтинг: 0 / 0
02.08.2018, 16:20
| Ответить | Цитировать | Написать  
compound trigger разные результаты при удалении
    #39682705
Фотография SY
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SY
Участник
Курим доку:

Statement triggers fired due to DELETE CASCADE and DELETE SET NULL are fired before and after the user DELETE statement, not before and after the individual enforcement statements. This prevents those statement triggers from encountering mutating errors.

Т.e. при удалении из parent for each row часть compound триггерa на child выполнится как и положено а вот statement часть compound триггерa на child выполнится после завершения удаления из parent и посему vId = 0.

SY.
...
Рейтинг: 0 / 0
02.08.2018, 16:41
| Ответить | Цитировать | Написать  
compound trigger разные результаты при удалении
    #39682717
Фотография SY
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SY
Участник
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
SQL> CREATE OR REPLACE TRIGGER child_tab_after_delete
  2   for delete on child_tab
  3  compound trigger
  4    vId number:=0;
  5    after each row is
  6    begin
  7      dbms_output.put_line('Child row level trigger.');
  8      vId:=:old.ch_col1;
  9      dbms_output.put_line('ID row level = '||vId);
 10    end after each row;
 11    after statement is
 12    begin
 13      dbms_output.put_line('Child statement level trigger.');
 14      dbms_output.put_line('ID statement level = '||vId);
 15    end after statement;
 16  end child_tab_after_delete;
 17  /

Trigger created.

SQL> CREATE OR REPLACE TRIGGER parent_tab_after_delete
  2   for delete on parent_tab
  3  compound trigger
  4    after each row is
  5    begin
  6        dbms_output.put_line('Parent row level trigger.');
  7     end after each row;
  8     after statement is
  9     begin
 10       dbms_output.put_line('Parent statement level trigger.');
 11    end after statement;
 12  end;
 13  /

Trigger created.

SQL> begin
  2    dbms_output.put_line('---------- Deleting child ----------');
  3    delete from child_tab where par_col1_ref=2;
  4    rollback;
  5    dbms_output.put_line('---------- Deleting parent ----------');
  6    delete from parent_tab where par_col1=2;
  7    rollback;
  8  end;
  9  /
---------- Deleting child ----------
Child row level trigger.
ID row level = 20
Child statement level trigger.
ID statement level = 20
---------- Deleting parent ----------
Child row level trigger.
ID row level = 20
Parent row level trigger.
Child statement level trigger.
ID statement level = 0
Parent statement level trigger.

PL/SQL procedure successfully completed.

SQL>



SY.
...
Рейтинг: 0 / 0
02.08.2018, 16:55
| Ответить | Цитировать | Написать  
compound trigger разные результаты при удалении
    #39682724
роман23t
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
роман23t
Участник
Это да, но я вот пока не могу подобрать объяснение, что было бы плохого, если бы значение переменной было сохранено и использовано в statement секции дочернего триггера во втором примере? Ведь по сути в этом и есть смысл compound триггера. А так выходит что простым росчерком пера добавлением каскадного удаления мы может поломать реализованную ранее логику. Есть мысли?
...
Рейтинг: 0 / 0
02.08.2018, 17:13
| Ответить | Цитировать | Написать  
compound trigger разные результаты при удалении
    #39682732
j2k
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
j2k
Участник
То, что написал SY - понятно. Но IMHO это действительно не прозрачно, что при добавлении ключа, ломается существующая логика
PS Нарыл еще вот такое: https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:9523633800346769343
Там вроде такой же случай, и написано что баг (там есть номер ноты, но у меня доступа на металинк нету, чтоб посмотреть)
...
Рейтинг: 0 / 0
02.08.2018, 17:36
| Ответить | Цитировать | Написать  
compound trigger разные результаты при удалении
    #39682743
Фотография SY
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SY
Участник
роман23tА так выходит что простым росчерком пера добавлением каскадного удаления мы может поломать реализованную ранее логику. Есть мысли?

Есть твоя логика и есть логика . Логика Oracle обойти никому ненужную "table is mutating". И эта логика документирована. Так-что базируй свою логику соответственно. Если удалeние из таблицы может быть вызвано через on delete cascade, то переменные используемые как for each row так и statement level пихаем в пакет:

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
SQL> create or replace
  2    package pkg1
  3      is
  4        vID number;
  5  end;
  6  /

Package created.

SQL> CREATE OR REPLACE TRIGGER child_tab_after_delete
  2   for delete on child_tab
  3  compound trigger
  4  --  vId number:=0;
  5    after each row is
  6    begin
  7      dbms_output.put_line('Child row level trigger.');
  8      pkg1.vId:=:old.ch_col1;
  9      dbms_output.put_line('ID row level = '||pkg1.vId);
 10    end after each row;
 11    after statement is
 12    begin
 13      dbms_output.put_line('Child statement level trigger.');
 14      dbms_output.put_line('ID statement level = '||pkg1.vId);
 15    end after statement;
 16  end child_tab_after_delete;
 17  /

Trigger created.

SQL> CREATE OR REPLACE TRIGGER parent_tab_after_delete
  2   for delete on parent_tab
  3  compound trigger
  4    after each row is
  5    begin
  6        dbms_output.put_line('Parent row level trigger.');
  7     end after each row;
  8     after statement is
  9     begin
 10       dbms_output.put_line('Parent statement level trigger.');
 11    end after statement;
 12  end;
 13  /

Trigger created.

SQL> begin
  2    dbms_output.put_line('---------- Deleting child ----------');
  3    delete from child_tab where par_col1_ref=2;
  4    rollback;
  5    dbms_output.put_line('---------- Deleting parent ----------');
  6    delete from parent_tab where par_col1=2;
  7    rollback;
  8  end;
  9  /
---------- Deleting child ----------
Child row level trigger.
ID row level = 20
Child statement level trigger.
ID statement level = 20
---------- Deleting parent ----------
Child row level trigger.
ID row level = 20
Parent row level trigger.
Child statement level trigger.
ID statement level = 20
Parent statement level trigger.

PL/SQL procedure successfully completed.

SQL>



SY.
...
Рейтинг: 0 / 0
02.08.2018, 18:25
| Ответить | Цитировать | Написать  
compound trigger разные результаты при удалении
    #39682747
Фотография Elic
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Elic
Участник
SYто переменные используемые как for each row так и statement level пихаем в пакет:Соломон, ты забыл, что compound триггера были придуманы как раз во избежание этого?
...
Рейтинг: 0 / 0
02.08.2018, 18:42
| Ответить | Цитировать | Написать  
compound trigger разные результаты при удалении
    #39682754
Фотография SY
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SY
Участник
ElicSYто переменные используемые как for each row так и statement level пихаем в пакет:Соломон, ты забыл, что compound триггера были придуманы как раз во избежание этого?

Не забыл. Просто eсть исключения и on cascade delete одно из них.

SY.
...
Рейтинг: 0 / 0
02.08.2018, 18:51
| Ответить | Цитировать | Написать  
compound trigger разные результаты при удалении
    #39682760
Фотография SY
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SY
Участник
И вообще, on delete cascade/set null есть, IMHO, зло и своих девелперов за это бью иногда и материально для доходчивости.

SY.
...
Рейтинг: 0 / 0
02.08.2018, 18:55
| Ответить | Цитировать | Написать  
compound trigger разные результаты при удалении
    #39682792
Фотография -2-
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
-2-
Участник
SYКурим доку:

Statement triggers fired due to DELETE CASCADE and DELETE SET NULL are fired before and after the user DELETE statement, not before and after the individual enforcement statements. This prevents those statement triggers from encountering mutating errors.Срабатывание чилд-триггера один раз на парент-стейтмент, а не на каждую парент-строку вполне ожидаемое поведение. Данная фраза из доки не объясняет влияние несброса состояния пакета триггера на появление mutating errors.
...
Рейтинг: 0 / 0
02.08.2018, 20:00
| Ответить | Цитировать | Написать  
compound trigger разные результаты при удалении
    #39682817
Фотография andrey_anonymous
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
andrey_anonymous
Участник
SYElicпропущено...
Соломон, ты забыл, что compound триггера были придуманы как раз во избежание этого?
Не забыл. Просто eсть исключения и on cascade delete одно из них.

Контекст хранить обещали в пределах одного firing statement.
Но при каскадном удалении у строчного и statement-level чилд-триггера firing statement, похоже, различается.
Пользуясь наработками SY:
 
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
clear
create table dropme_parent_tab (par_col1 number, par_col2 date);
alter table dropme_parent_tab add constraint dropme_parent_tab_PK primary key (par_col1);

create table dropme_child_tab (ch_col1 number, ch_col2 date, par_col1_ref number);
alter table dropme_child_tab add constraint dropme_child_tab_PK primary key (ch_col1);
alter table dropme_child_tab add constraint ref_dropme_parent_tab foreign key (par_col1_ref)  references dropme_parent_tab (par_col1) on delete cascade;

begin
  insert into dropme_parent_tab values (1, to_date('01.01.2018','dd.mm.yyyy'));
  insert into dropme_parent_tab values (2, to_date('01.01.2018','dd.mm.yyyy'));

  insert into dropme_child_tab values (10, to_date('01.01.2018','dd.mm.yyyy'),1);
  insert into dropme_child_tab values (20, to_date('01.01.2018','dd.mm.yyyy'),2);
--  insert into dropme_child_tab values (30, to_date('01.01.2018','dd.mm.yyyy'),2);
end;
/

CREATE OR REPLACE TRIGGER dropme_child_tab_after_delete
 for delete on dropme_child_tab
compound trigger
  vId number:=0;
  after each row is
  begin
    dbms_output.put_line('Child row level trigger.');
    DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_CALL_STACK);
      for r in (select status,curno,flag,child_handle from v$sql_cursor where status = 'CURBOUND')
      loop
        for i in (select substr(sql_text,1,40) l_text from v$sql where child_address = r.child_handle)
          loop
            dbms_output.put_line(r.status||'*'||r.curno||'*'||r.flag||'====> '||i.l_text);
        end loop;
      end loop;
  end after each row;
  after statement is
  begin
    dbms_output.put_line('Child statement level trigger.');
    DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_CALL_STACK);
      for r in (select status,curno,flag,child_handle from v$sql_cursor where status = 'CURBOUND')
      loop
        for i in (select substr(sql_text,1,40) l_text from v$sql where child_address = r.child_handle)
          loop
            dbms_output.put_line(r.status||'*'||r.curno||'*'||r.flag||'====> '||i.l_text);
        end loop;
      end loop;
  end after statement;
end dropme_child_tab_after_delete;
/
sho err

CREATE OR REPLACE TRIGGER dropme_parent_tab_after_delete
 for delete on dropme_parent_tab
compound trigger
  after each row is
  begin
      dbms_output.put_line('Parent row level trigger.');
    DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_CALL_STACK);
      for r in (select status,curno,flag,child_handle from v$sql_cursor where status = 'CURBOUND')
      loop
        for i in (select substr(sql_text,1,40) l_text from v$sql where child_address = r.child_handle)
          loop
            dbms_output.put_line(r.status||'*'||r.curno||'*'||r.flag||'====> '||i.l_text);
        end loop;
      end loop;
   end after each row;
   after statement is
   begin
     dbms_output.put_line('Parent statement level trigger.');
    DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_CALL_STACK);
      for r in (select status,curno,flag,child_handle from v$sql_cursor where status = 'CURBOUND')
      loop
        for i in (select substr(sql_text,1,40) l_text from v$sql where child_address = r.child_handle)
          loop
            dbms_output.put_line(r.status||'*'||r.curno||'*'||r.flag||'====> '||i.l_text);
        end loop;
      end loop;
  end after statement;
end;
/
sho err

set serveroutput on size 10000 
begin
  dbms_output.put_line('------------------------------------');
  dbms_output.put_line('---------- Deleting child ----------');
  dbms_output.put_line('------------------------------------');
  delete from dropme_child_tab where par_col1_ref=2;
  rollback;
  dbms_output.put_line('------------------------------------');
  dbms_output.put_line('---------- Deleting parent ----------');
  dbms_output.put_line('------------------------------------');
  delete from dropme_parent_tab where par_col1=2;
  rollback;
end;
/


drop table dropme_child_tab purge;
drop table dropme_parent_tab purge;


нашел, что:
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
------------------------------------
---------- Deleting parent ----------
------------------------------------
Child row level trigger.

----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
0xfef1e2060         6  DROPME_CHILD_TAB_AFTER_DELETE
0x10aaf543c8        10  anonymous block

...
CURBOUND*7*10317====>  delete from "DROPME_CHILD_TAB" -- Вот он, delete по child.
CURBOUND*8*205====> DELETE FROM DROPME_PARENT_TAB WHERE PAR_
...

Parent row level trigger.

----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
0x10ff272318         5  DROPME_PARENT_TAB_AFTER_DELETE
0x10aaf543c8        10  anonymous block

...
CURBOUND*8*205====> DELETE FROM DROPME_PARENT_TAB WHERE PAR_
...

Child statement level trigger.

----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
0xfef1e2060        18  DROPME_CHILD_TAB_AFTER_DELETE
0x10aaf543c8        10  anonymous block

...
-- А вот тут не вижу курсора по child_tab
CURBOUND*8*205====> DELETE FROM DROPME_PARENT_TAB WHERE PAR_
...

Parent statement level trigger.

----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
0x10ff272318        17  DROPME_PARENT_TAB_AFTER_DELETE
0x10aaf543c8        10  anonymous block

...
CURBOUND*8*205====> DELETE FROM DROPME_PARENT_TAB WHERE PAR_
...

PL/SQL procedure successfully completed



Видимо, нет курсора - нет и контекста, связанного с firing statement строчного child-триггера.
...
Рейтинг: 0 / 0
02.08.2018, 20:40
| Ответить | Цитировать | Написать  
compound trigger разные результаты при удалении
    #39682827
Фотография SY
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SY
Участник
-2-Данная фраза из доки не объясняет влияние несброса состояния пакета триггера на появление mutating errors.

Elic намекaет на бабушкин мeтод борьбы с мутацией где for each row собирает PK/ROWID в пакетную коллекцию а statement level лезет с ними в таблицу. Compound триггер позволяет держать коллекцию в триггере тем самым избегая издержек типа инициализации коллекции. Однако порядок выполнения on cascade delete/set null рвет compound триггер на statement level и for each row каждый из которых выполняется в собственном контексте. Бало бы нeплохо если бы compound триггер выполнялся что-то наподобие пакетa с serially_reusable pragma. Но вызов serially_reusable из SQL запрещен.

SY.
...
Рейтинг: 0 / 0
02.08.2018, 21:15
| Ответить | Цитировать | Написать  
compound trigger разные результаты при удалении
    #39682829
Фотография andrey_anonymous
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
andrey_anonymous
Участник
Ну и до кучи еще одно косвенное подтверждение: если
Код: plsql
1.
vId number:=0;


оригинала заменить на инициализацию функцией вида
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
create function init_zero return integer 
deterministic
as 
begin
  dbms_output.put_line('****** Init *******');
  dbms_output.put_line(dbms_utility.format_call_stack);
  dbms_output.put_line('*******************');
  return 0;
end;


то можно видеть, что при каскадном удалении функция зовется дважды - в контексте парента (перед Child Before-statement level trigger) и чилда (перед первым вызовом строчного child-триггера).
Для наглядности можно предикат удаления изменить на <=2, функция вызвана трижды: один раз в контексте парента (перед Child Before-statement level trigger) и по одному разу в контексте каждой отдельной операции удаления чилдов (перед первым вызовом строчного child-триггера с новым ключом).

Таким образом:
- statement-level часть compound триггера при каскадном удалении работает в контексте parent statement, а не "родного" для себя child.
- контекст row-level части compound триггера переинициализируется для каждой отдельной рекурсивной операции удаления из чилда.
...
Рейтинг: 0 / 0
02.08.2018, 21:24
| Ответить | Цитировать | Написать  
compound trigger разные результаты при удалении
    #39683608
Фотография Stax
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Stax
Участник
SY,

імхо ето баг

merge и compound тож фичи есть

18813057

....
stax
...
Рейтинг: 0 / 0
06.08.2018, 10:37
| Ответить | Цитировать | Написать  
14 сообщений из 14, страница 1 из 1
Форумы / Oracle [игнор отключен] [закрыт для гостей] / compound trigger разные результаты при удалении
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение   Полит. конфиден.  
NoSQL.ru       Кролег: Проекты, Новости, Чат       РЕД ФОРУМ       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=52&msg=39682743&tid=1883641]:
 0ms
get settings:
 7ms
get forum list:
 10ms
check forum access:
 2ms
check topic access:
 2ms
track hit:
 203ms
get topic data:
 9ms
get forum data:
 3ms
get page messages:
 55ms
get tp. blocked users:
 2ms
others: 219ms
total:  512ms
созд. / загр.: 512ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]