|
SQL>
SQL> CREATE TABLE master_table
2 (master_id NUMBER NOT NULL PRIMARY KEY);
Table created.
SQL>
SQL> CREATE TABLE detail_table
2 (detail_id NUMBER NOT NULL,
3 master_id NUMBER NOT NULL,
4 CONSTRAINT detail_to_emp
5 FOREIGN KEY (master_id)
6 REFERENCES master_table (master_id)
7 ON DELETE CASCADE);
Table created.
SQL>
SQL> CREATE OR REPLACE TRIGGER after_delete_master
2 AFTER DELETE ON master_table
3 FOR EACH ROW
4 DECLARE
5 CURSOR curs_count_detail IS
6 SELECT COUNT(*)
7 FROM detail_table;
8 v_detail_count NUMBER;
9 BEGIN
10 OPEN curs_count_detail;
11 FETCH curs_count_detail INTO v_detail_count;
12 CLOSE curs_count_detail;
13 END;
14 /
Trigger created.
SQL>
SQL> BEGIN
2 FOR master_counter IN 1..10 LOOP
3 INSERT INTO master_table
4 VALUES(master_counter);
5 FOR detail_counter IN 1..2 LOOP
6 INSERT INTO detail_table
7 VALUES(detail_counter,
8 master_counter);
9 END LOOP;
10 END LOOP;
11 END;
12 /
PL/SQL procedure successfully completed.
SQL>
SQL> SELECT * FROM master_table;
MASTER_ID
----------
1
2
3
4
5
6
7
8
9
10
10 rows selected.
SQL>
SQL> SELECT * FROM detail_table;
DETAIL_ID MASTER_ID
---------- ----------
1 1
2 1
1 2
2 2
1 3
2 3
1 4
2 4
1 5
2 5
1 6
DETAIL_ID MASTER_ID
---------- ----------
2 6
1 7
2 7
1 8
2 8
1 9
2 9
1 10
2 10
20 rows selected.
SQL>
SQL> DELETE master_table;
DELETE master_table
*
ERROR at line 1:
ORA-04091: table JAVA2S.DETAIL_TABLE is mutating, trigger/function may not see
it
ORA-06512: at "JAVA2S.AFTER_DELETE_MASTER", line 3
ORA-06512: at "JAVA2S.AFTER_DELETE_MASTER", line 7
ORA-04088: error during execution of trigger 'JAVA2S.AFTER_DELETE_MASTER'
SQL>
SQL> SELECT * FROM master_table;
MASTER_ID
----------
1
2
3
4
5
6
7
8
9
10
10 rows selected.
SQL>
SQL> SELECT * FROM detail_table;
DETAIL_ID MASTER_ID
---------- ----------
1 1
2 1
1 2
2 2
1 3
2 3
1 4
2 4
1 5
2 5
1 6
DETAIL_ID MASTER_ID
---------- ----------
2 6
1 7
2 7
1 8
2 8
1 9
2 9
1 10
2 10
20 rows selected.
SQL>
SQL> DROP TABLE detail_table;
Table dropped.
SQL> DROP TABLE master_table;
Table dropped.
SQL>
SQL>
SQL>
|