ТЕМА:

Я тоже SY доверяю, несмотря даже на мои познания в англиском
(достаточно часто я его понимаю)

Я просто написал что порядок срабатывания
сильно зависит от версии

У нас не отработал аудит,
начал разбиратся оказалось виновата
on delete cascade

Извеняюсь за трафик

ЗЫ
На всяк случ еще раз,
сделал три уровня

Когдато еще тестировал on delete cascade
саму на себя

могу поправить триггера для более полного тестирования
счас это неактуально нужна девятка,
а мож и десятка

SQL> select * from parent,child1,child2,child3;

ID ID ID_REF ID_REF ID_REF

---

---

---

---

---

1 1 1 1 1
1 1 1 1 2
1 1 1 2 1
1 1 1 2 2
1 2 2 1 1
1 2 2 1 2
1 2 2 2 1
1 2 2 2 2
2 1 1 1 1
2 1 1 1 2
2 1 1 2 1
2 1 1 2 2
2 2 2 1 1
2 2 2 1 2
2 2 2 2 1
2 2 2 2 2

16 rows selected.

SQL> delete parent;
Child_child before DELETE.
Child_child before DELETE.
Child_child before DELETE.
Child_child before DELETE.
Child_child after DELETE.
Child_child after DELETE.
Child_child after DELETE.
Child_child after DELETE.

2 rows deleted.

SQL> select * from parent,child1,child2,child3;

no rows selected

SQL> rollback;

Rollback complete.


TRIGGER_NAME TRIGGER_TYPE TABLE_NAME TRIGGER_BODY

---

---

---

---

CHILD1_AD_TRG AFTER STATEMENT CHILD1 BEGIN
DBMS_OUTPUT.PUT_LINE('Child1 after DELETE.');
END;


CHILD1_BD_TRG BEFORE STATEMENT CHILD1 BEGIN
DBMS_OUTPUT.PUT_LINE('Child1 before DELETE.');
END;


CHILD2_AD_TRG AFTER STATEMENT CHILD2 BEGIN
DBMS_OUTPUT.PUT_LINE('Child2 after DELETE.');
END;


CHILD2_BD_TRG BEFORE STATEMENT CHILD2 BEGIN
DBMS_OUTPUT.PUT_LINE('Child2 before DELETE.');
END;


CHILD3_AD_TRG AFTER STATEMENT CHILD3 BEGIN
DBMS_OUTPUT.PUT_LINE('Child_child after DELETE.');
END;


CHILD3_BD_TRG BEFORE STATEMENT CHILD3 BEGIN
DBMS_OUTPUT.PUT_LINE('Child_child before DELETE.');
END;


PARENT_AD_TRG AFTER STATEMENT PARENT BEGIN
DBMS_OUTPUT.PUT_LINE('Parent after DELETE.');
END;


PARENT_BD_TRG BEFORE STATEMENT PARENT BEGIN
DBMS_OUTPUT.PUT_LINE('Parent before DELETE.');
END;



8 rows selected.

SQL>

I am flattered .

Я просто написал что порядок срабатывания
сильно зависит от версии

Obviously. Docs I was referring to are 9i docs. My test was done in 9.2. Why?

Проверял в 9-ке и в 10-ке


However Concepts, The Execution Model for Triggers and Integrity Constraint Checking rules in 8.1.6 docs are same as in 9i. So unless there were some bugs it should be no different in both versions.

SY.
P.S. I just tested in on 8.1.7.0.0 - works fine. Sorry Stax, I do not have 8.1.6.

Thank you.
Just one more question: if I use your approach I must handle possible exceptions in outer program block, I can not do it within trigger, right? Something like that:
begin
delete from parent_table ...
exeption
when others then v_DELETE_CASCADE := false;
raise;
end;
/


And this should be repeated for each DELETE statement. Or... what?

If we use two BEFORE triggers, there's no need to handle exceptions at all. Consider this testcase:
SQL> create sequence test_seq start with 1 cache 100;

Последовательность создана.

SQL> create table log_table(seq_no integer, what varchar2(1), code integer);

Таблица создана.

SQL> create table pt (x integer primary key);

Таблица создана.

SQL> create table ct (y integer primary key, x integer references pt(x) on delete cascade);

Таблица создана.

SQL> create or replace package for_trig is
2 Flag integer;
3 end;
4 /

Пакет создан.

SQL> create or replace trigger DeleteParentBefore before delete on pt
2 begin
3 For_Trig.Flag := 1;
4 end;
5 /

Триггер создан.

SQL> create or replace trigger DeleteChildBefore before delete on ct
2 begin
3 For_Trig.Flag := 0;
4 end;
5 /

Триггер создан.

SQL> create or replace trigger RowDeleteParentAfter after delete on pt for each row
2 begin
3 insert into Log_Table values(test_seq.nextval, 'P', :old.x);
4 end;
5 /

Триггер создан.

SQL> create or replace trigger RowDeleteChildAfter after delete on ct for each row
2 begin
3 if For_Trig.Flag = 0 then
4 insert into Log_Table values(test_seq.nextval, 'C', :old.y);
5 end if;
6 end;
7 /

Триггер создан.


Now, if we know exactly that BEFORE trigger for child table is always fired first, we don't need to handle possible exceptions: if child row trigger is fired not due DELETE CASCADE, then For_Trig.Flag is 0, otherwise, it is set to 1 during execution of parent BEFORE-trigger.

Here is a test run:SQL> begin
2 insert into pt values (1);
3 insert into pt values (2);
4 insert into ct values (1, 1);
5 insert into ct values (2, 1);
6 insert into ct values (3, 1);
7 insert into ct values (4, 1);
8 commit;
9 end;
10 /

Процедура PL/SQL успешно завершена.

SQL> delete from pt where x = 1;

1 строка удалена.

SQL> select * from log_table;

SEQ_NO W CODE

---

-

---

1 P 1

SQL> rollback;

Откат завершен.

SQL> delete from pt where x = 1/0;
delete from pt where x = 1/0
*
ошибка в строке 1:
ORA-01476: делитель равен нулю


SQL> delete from ct where y = 3;

1 строка удалена.

SQL> select * from log_table;

SEQ_NO W CODE

---

-

---

2 C 3


Do you think, however, that solution with BEFORE and AFTER trigger for parent table is better?

Regards.

No. All you need is an exception handler in each trigger (not just parent, but both parent and child - I stand corrected) where v_DELETE_CASCADE is reset back to FALSE. That way in case of a triiger error v_DELETE_CASCADE will be reset and error reraised. Keep in mind you mignt have more than one level of ON DELETE CASCADE, e.g. parent -child - grandchild. In such case boolean will not work. You would have to use either level on highest level delete table name. Parent before delete trigger will:

v_DELETE_CASCADE := 'PARENT';

while child before delete trigger will have to:

v_DELETE_CASCADE := 'CHILD';

and grandchild before delete trigger will have to:

v_DELETE_CASCADE := 'GRANDCHILD';

Since, as we already established, Oracle will execute before delete triggers in reversed order (grandchild, child, parent), v_DELETE_CASCADE will have highest level delete table name. Then child table row level trigger will have to:

IF v_DELETE_CASCADE = 'CHILD'
THEN
log delete
END IF;

and grandchild table row level trigger will have to:

IF v_DELETE_CASCADE = 'GRANDCHILD'
THEN
log delete
END IF;

Obviously, v_DELETE_CASCADE will have to be reset to NULL instead of FALSE.
The downside of the above method is relying on specific trigger execution rules. If Oracle decides to change them, migrating will be more painful. Other method could be setting v_DELETE_CASCADE in app rather than in triggers, however in such case logging deletes done not through the app will not work properly.

That way in case of a trigger error v_DELETE_CASCADE will be reset and error reraised.


Just guessing: I probably made you sick with my annoying questions...
Sorry.

You say about "trigger error", but what about DELETE statement errors?

Please, consider this, for example:
SQL> create or replace trigger DeleteParentBefore before delete on pt
2 begin
3 For_Trig.Flag := 1;
4 exception
5 when others then For_Trig.Flag := 0;
6 raise;
7 end;
8 /

Триггер создан.

SQL> create or replace trigger DeleteParentAfter after delete on pt
2 begin
3 For_Trig.Flag := 0;
4 exception
5 when others then For_Trig.Flag := 0;
6 raise;
7 end;
8 /

Триггер создан.

SQL> create or replace trigger RowDeleteParentAfter after delete on pt for each row
2 begin
3 insert into Log_Table values(test_seq.nextval, 'P', :old.x);
4 exception
5 when others then For_Trig.Flag := 0;
6 raise;
7 end;
8 /

Триггер создан.

SQL> create or replace trigger RowDeleteChildAfter after delete on ct for each row
2 begin
3 if For_Trig.Flag = 0 then
4 insert into Log_Table values(test_seq.nextval, 'C', :old.y);
5 end if;
6 exception
7 when others then For_Trig.Flag := 0;
8 raise;
9 end;
10 /

Триггер создан.

SQL> exec For_Trig.Flag := 0;

Процедура PL/SQL успешно завершена.

SQL> delete from pt where x = 1/0;
delete from pt where x = 1/0
*
ошибка в строке 1:
ORA-01476: делитель равен нулю


SQL> exec dbms_output.put_line(for_trig.Flag);
1

Процедура PL/SQL успешно завершена.


So... what should we do next??? Our flag is not reset, as you can see.

Regards.

Well, looks like you are correct, flag will not work in your case. You should use highest level delete table name. And then, by the way, you do not need any exception handlers and you do not need AFTER DELETE statement level triggers to reset pacakge variable (the following example uses them simply to show trigger execution sequence):
SQL> CREATE OR REPLACE
2 PACKAGE PKG
3 AS
4 v_DELETE_TABLE_NAME VARCHAR2(30);
5 END;
6 /

Package created.

SQL> CREATE TABLE PARENT(
2 ID NUMBER
3 )
4 /

Table created.

SQL> ALTER TABLE PARENT
2 ADD CONSTRAINT PARENT_PK
3 PRIMARY KEY(
4 ID
5 )
6 /

Table altered.

SQL> CREATE TRIGGER PARENT_BD_TRG
2 BEFORE DELETE
3 ON PARENT
4 BEGIN
5 PKG.v_DELETE_TABLE_NAME := 'PARENT';
6 DBMS_OUTPUT.PUT_LINE(
7 'Parent before DELETE.'
8 );
9 END;
10 /

Trigger created.

SQL> CREATE TRIGGER PARENT_AD_TRG
2 AFTER DELETE
3 ON PARENT
4 BEGIN
5 DBMS_OUTPUT.PUT_LINE(
6 'Parent after DELETE.'
7 );
8 END;
9 /

Trigger created.

SQL> INSERT
2 INTO PARENT
3 VALUES(
4 1
5 )
6 /

1 row created.

SQL> INSERT
2 INTO PARENT
3 VALUES(
4 2
5 )
6 /

1 row created.

SQL> CREATE TABLE CHILD(
2 ID NUMBER,
3 PARENT_ID NUMBER
4 )
5 /

Table created.

SQL> ALTER TABLE CHILD
2 ADD CONSTRAINT CHILD_PK
3 PRIMARY KEY(
4 ID
5 )
6 /

Table altered.

SQL> ALTER TABLE CHILD
2 ADD CONSTRAINT CHILD_PARENT_FK
3 FOREIGN KEY(
4 PARENT_ID
5 )
6 REFERENCES PARENT(
7 ID
8 )
9 ON DELETE CASCADE
10 /

Table altered.

SQL> CREATE TRIGGER CHILD_BDR_TRG
2 BEFORE DELETE
3 ON CHILD
4 FOR EACH ROW
5 BEGIN
6 DBMS_OUTPUT.PUT_LINE(
7 'Child before row DELETE ID = ' || :OLD.PARENT_ID || ' DELETE TABLE NAME IS ' ||
8 PKG.v_DELETE_TABLE_NAME
9
10 );
11 END;
12 /

Trigger created.

SQL> CREATE TRIGGER CHILD_ADR_TRG
2 AFTER DELETE
3 ON CHILD
4 FOR EACH ROW
5 BEGIN
6 DBMS_OUTPUT.PUT_LINE(
7 'Child after row DELETE ID = ' || :OLD.PARENT_ID || ' DELETE TABLE NAME IS ' ||
8 PKG.v_DELETE_TABLE_NAME
9 );
10 END;
11 /

Trigger created.

SQL> CREATE TRIGGER CHILD_BD_TRG
2 BEFORE DELETE
3 ON CHILD
4 BEGIN
5 PKG.v_DELETE_TABLE_NAME := 'CHILD';
6 DBMS_OUTPUT.PUT_LINE(
7 'Child before DELETE.'
8 );
9 END;
10 /

Trigger created.

SQL> CREATE TRIGGER CHILD_AD_TRG
2 AFTER DELETE
3 ON CHILD
4 BEGIN
5 DBMS_OUTPUT.PUT_LINE(
6 'Child after DELETE.'
7 );
8 END;
9 /

Trigger created.

SQL> INSERT
2 INTO CHILD
3 SELECT ROWNUM,
4 1
5 FROM DBA_USERS
6 WHERE ROWNUM < 3
7 /

2 rows created.

SQL> INSERT
2 INTO CHILD
3 SELECT ROWNUM + 2,
4 2
5 FROM DBA_USERS
6 WHERE ROWNUM < 3
7 /

2 rows created.

SQL> CREATE TABLE GRANDCHILD(
2 ID NUMBER
3 )
4 /

Table created.

SQL> ALTER TABLE GRANDCHILD
2 ADD CONSTRAINT GRANDCHILD_CHILD_FK
3 FOREIGN KEY(
4 ID
5 )
6 REFERENCES CHILD(
7 ID
8 )
9 ON DELETE CASCADE
10 /

Table altered.

SQL> CREATE TRIGGER GRANDCHILD_BDR_TRG
2 BEFORE DELETE
3 ON GRANDCHILD
4 FOR EACH ROW
5 BEGIN
6 DBMS_OUTPUT.PUT_LINE(
7 'Grandchild before row DELETE ID = ' || :OLD.ID || ' DELETE TABLE NAME IS ' ||
8 PKG.v_DELETE_TABLE_NAME
9 );
10 END;
11 /

Trigger created.

SQL> CREATE TRIGGER GRANDCHILD_ADR_TRG
2 AFTER DELETE
3 ON GRANDCHILD
4 FOR EACH ROW
5 BEGIN
6 DBMS_OUTPUT.PUT_LINE(
7 'Grandchild after row DELETE ID = ' || :OLD.ID || ' DELETE TABLE NAME IS ' ||
8 PKG.v_DELETE_TABLE_NAME
9 );
10 END;
11 /

Trigger created.

SQL> CREATE TRIGGER GRANDCHILD_BD_TRG
2 BEFORE DELETE
3 ON GRANDCHILD
4 BEGIN
5 PKG.v_DELETE_TABLE_NAME := 'GRANDCHILD';
6 DBMS_OUTPUT.PUT_LINE(
7 'Grandchild before DELETE.'
8 );
9 END;
10 /

Trigger created.

SQL> CREATE TRIGGER GRANDCHILD_AD_TRG
2 AFTER DELETE
3 ON GRANDCHILD
4 BEGIN
5 DBMS_OUTPUT.PUT_LINE(
6 'Grandchild after DELETE.'
7 );
8 END;
9 /

Trigger created.

SQL> INSERT
2 INTO GRANDCHILD
3 SELECT 1
4 FROM DBA_USERS
5 WHERE ROWNUM < 3;

2 rows created.

SQL> /

2 rows created.

SQL> INSERT
2 INTO GRANDCHILD
3 SELECT 2
4 FROM DBA_USERS
5 WHERE ROWNUM < 3;

2 rows created.

SQL> /

2 rows created.

SQL> COMMIT
2 /

Commit complete.

SQL> SET SERVEROUTPUT ON SIZE 1000000
SQL> DELETE PARENT
2 /
Grandchild before DELETE.
Child before DELETE.
Parent before DELETE.
Child before row DELETE ID = 1 DELETE TABLE NAME IS PARENT
Child after row DELETE ID = 1 DELETE TABLE NAME IS PARENT
Child before row DELETE ID = 1 DELETE TABLE NAME IS PARENT
Child after row DELETE ID = 1 DELETE TABLE NAME IS PARENT
Grandchild before row DELETE ID = 1 DELETE TABLE NAME IS PARENT
Grandchild after row DELETE ID = 1 DELETE TABLE NAME IS PARENT
Grandchild before row DELETE ID = 1 DELETE TABLE NAME IS PARENT
Grandchild after row DELETE ID = 1 DELETE TABLE NAME IS PARENT
Grandchild before row DELETE ID = 1 DELETE TABLE NAME IS PARENT
Grandchild after row DELETE ID = 1 DELETE TABLE NAME IS PARENT
Grandchild before row DELETE ID = 1 DELETE TABLE NAME IS PARENT
Grandchild after row DELETE ID = 1 DELETE TABLE NAME IS PARENT
Grandchild before row DELETE ID = 2 DELETE TABLE NAME IS PARENT
Grandchild after row DELETE ID = 2 DELETE TABLE NAME IS PARENT
Grandchild before row DELETE ID = 2 DELETE TABLE NAME IS PARENT
Grandchild after row DELETE ID = 2 DELETE TABLE NAME IS PARENT
Grandchild before row DELETE ID = 2 DELETE TABLE NAME IS PARENT
Grandchild after row DELETE ID = 2 DELETE TABLE NAME IS PARENT
Grandchild before row DELETE ID = 2 DELETE TABLE NAME IS PARENT
Grandchild after row DELETE ID = 2 DELETE TABLE NAME IS PARENT
Child before row DELETE ID = 2 DELETE TABLE NAME IS PARENT
Child after row DELETE ID = 2 DELETE TABLE NAME IS PARENT
Child before row DELETE ID = 2 DELETE TABLE NAME IS PARENT
Child after row DELETE ID = 2 DELETE TABLE NAME IS PARENT
Grandchild after DELETE.
Child after DELETE.
Parent after DELETE.

2 rows deleted.

SQL> ROLLBACK
2 /

Rollback complete.

SQL> DELETE CHILD
2 /
Grandchild before DELETE.
Child before DELETE.
Child before row DELETE ID = 1 DELETE TABLE NAME IS CHILD
Grandchild before row DELETE ID = 1 DELETE TABLE NAME IS CHILD
Grandchild after row DELETE ID = 1 DELETE TABLE NAME IS CHILD
Grandchild before row DELETE ID = 1 DELETE TABLE NAME IS CHILD
Grandchild after row DELETE ID = 1 DELETE TABLE NAME IS CHILD
Grandchild before row DELETE ID = 1 DELETE TABLE NAME IS CHILD
Grandchild after row DELETE ID = 1 DELETE TABLE NAME IS CHILD
Grandchild before row DELETE ID = 1 DELETE TABLE NAME IS CHILD
Grandchild after row DELETE ID = 1 DELETE TABLE NAME IS CHILD
Child after row DELETE ID = 1 DELETE TABLE NAME IS CHILD
Child before row DELETE ID = 1 DELETE TABLE NAME IS CHILD
Grandchild before row DELETE ID = 2 DELETE TABLE NAME IS CHILD
Grandchild after row DELETE ID = 2 DELETE TABLE NAME IS CHILD
Grandchild before row DELETE ID = 2 DELETE TABLE NAME IS CHILD
Grandchild after row DELETE ID = 2 DELETE TABLE NAME IS CHILD
Grandchild before row DELETE ID = 2 DELETE TABLE NAME IS CHILD
Grandchild after row DELETE ID = 2 DELETE TABLE NAME IS CHILD
Grandchild before row DELETE ID = 2 DELETE TABLE NAME IS CHILD
Grandchild after row DELETE ID = 2 DELETE TABLE NAME IS CHILD
Child after row DELETE ID = 1 DELETE TABLE NAME IS CHILD
Child before row DELETE ID = 2 DELETE TABLE NAME IS CHILD
Child after row DELETE ID = 2 DELETE TABLE NAME IS CHILD
Child before row DELETE ID = 2 DELETE TABLE NAME IS CHILD
Child after row DELETE ID = 2 DELETE TABLE NAME IS CHILD
Grandchild after DELETE.
Child after DELETE.

4 rows deleted.

SQL> ROLLBACK
2 /

Rollback complete.

SQL> DELETE GRANDCHILD
2 /
Grandchild before DELETE.
Grandchild before row DELETE ID = 1 DELETE TABLE NAME IS GRANDCHILD
Grandchild after row DELETE ID = 1 DELETE TABLE NAME IS GRANDCHILD
Grandchild before row DELETE ID = 1 DELETE TABLE NAME IS GRANDCHILD
Grandchild after row DELETE ID = 1 DELETE TABLE NAME IS GRANDCHILD
Grandchild before row DELETE ID = 1 DELETE TABLE NAME IS GRANDCHILD
Grandchild after row DELETE ID = 1 DELETE TABLE NAME IS GRANDCHILD
Grandchild before row DELETE ID = 1 DELETE TABLE NAME IS GRANDCHILD
Grandchild after row DELETE ID = 1 DELETE TABLE NAME IS GRANDCHILD
Grandchild before row DELETE ID = 2 DELETE TABLE NAME IS GRANDCHILD
Grandchild after row DELETE ID = 2 DELETE TABLE NAME IS GRANDCHILD
Grandchild before row DELETE ID = 2 DELETE TABLE NAME IS GRANDCHILD
Grandchild after row DELETE ID = 2 DELETE TABLE NAME IS GRANDCHILD
Grandchild before row DELETE ID = 2 DELETE TABLE NAME IS GRANDCHILD
Grandchild after row DELETE ID = 2 DELETE TABLE NAME IS GRANDCHILD
Grandchild before row DELETE ID = 2 DELETE TABLE NAME IS GRANDCHILD
Grandchild after row DELETE ID = 2 DELETE TABLE NAME IS GRANDCHILD
Grandchild after DELETE.

8 rows deleted.

SQL> ROLLBACK
2 /

Rollback complete.

SQL> DELETE PARENT WHERE ID = 1/0
2 /
DELETE PARENT WHERE ID = 1/0
*
ERROR at line 1:
ORA-01476: divisor is equal to zero


SQL> EXEC DBMS_OUTPUT.PUT_LINE(PKG.v_DELETE_TABLE_NAME)
Grandchild before DELETE.
Child before DELETE.
Parent before DELETE.
PARENT

PL/SQL procedure successfully completed.

SQL> DELETE GRANDCHILD
2 /
Grandchild before DELETE.
Grandchild before row DELETE ID = 1 DELETE TABLE NAME IS GRANDCHILD
Grandchild after row DELETE ID = 1 DELETE TABLE NAME IS GRANDCHILD
Grandchild before row DELETE ID = 1 DELETE TABLE NAME IS GRANDCHILD
Grandchild after row DELETE ID = 1 DELETE TABLE NAME IS GRANDCHILD
Grandchild before row DELETE ID = 1 DELETE TABLE NAME IS GRANDCHILD
Grandchild after row DELETE ID = 1 DELETE TABLE NAME IS GRANDCHILD
Grandchild before row DELETE ID = 1 DELETE TABLE NAME IS GRANDCHILD
Grandchild after row DELETE ID = 1 DELETE TABLE NAME IS GRANDCHILD
Grandchild before row DELETE ID = 2 DELETE TABLE NAME IS GRANDCHILD
Grandchild after row DELETE ID = 2 DELETE TABLE NAME IS GRANDCHILD
Grandchild before row DELETE ID = 2 DELETE TABLE NAME IS GRANDCHILD
Grandchild after row DELETE ID = 2 DELETE TABLE NAME IS GRANDCHILD
Grandchild before row DELETE ID = 2 DELETE TABLE NAME IS GRANDCHILD
Grandchild after row DELETE ID = 2 DELETE TABLE NAME IS GRANDCHILD
Grandchild before row DELETE ID = 2 DELETE TABLE NAME IS GRANDCHILD
Grandchild after row DELETE ID = 2 DELETE TABLE NAME IS GRANDCHILD
Grandchild after DELETE.

8 rows deleted.

SQL>


SY.