37rJ7WY8

1--  Script: Automatically manage a companion-entry in the same table 
2
3create table test ( 
4  role_name varchar2(200) not null primary key 
5)
6;
7
8create or replace package global_state as 
9  type t_stringlist is table of varchar2(200); 
10 
11  g_inserted_roles t_stringlist := t_stringlist(); 
12  g_deleted_roles t_stringlist := t_stringlist(); 
13 
14  procedure role_inserted ( i_role_name in varchar2 ); 
15 
16  procedure role_deleted ( i_role_name in varchar2 ); 
17 
18  procedure cleanup_roles; 
19end; 
20
21/
22
23create or replace package body global_state as 
24 
25  procedure role_inserted ( i_role_name in varchar2 ) 
26  as 
27    begin 
28      g_inserted_roles.extend; 
29      g_inserted_roles(g_inserted_roles.last) := i_role_name; 
30    end; 
31 
32  procedure role_deleted ( i_role_name in varchar2 ) 
33  as 
34    begin 
35      g_deleted_roles.extend; 
36      g_deleted_roles(g_deleted_roles.last) := i_role_name; 
37    end; 
38 
39  function copy( i_stringlist in t_stringlist ) return t_stringlist 
40  as 
41    l_result t_stringlist := t_stringlist(); 
42    begin 
43      if i_stringlist.count > 0 then 
44        for i in i_stringlist.first .. i_stringlist.last loop 
45          l_result.extend; 
46          l_result(l_result.last) := i_stringlist(i); 
47        end loop; 
48      end if; 
49      return l_result; 
50    end; 
51 
52  procedure cleanup_roles 
53  as 
54    l_worklist t_stringlist; 
55    begin 
56      -- do we have inserted specific role? 
57      l_worklist := copy( g_inserted_roles ); 
58      g_inserted_roles.delete(); 
59      if l_worklist.count > 0 then 
60        for i in l_worklist.first .. l_worklist.last loop 
61          if l_worklist(i) = 'test1' then 
62            insert into test select 'test1_companion' from dual where not exists (select 1 from test where role_name = 'test1_companion'); 
63          end if; 
64        end loop; 
65      end if; 
66 
67      -- handle deletes 
68      l_worklist := copy( g_deleted_roles ); 
69      g_deleted_roles.delete(); 
70      if l_worklist.count > 0 then 
71        for i in l_worklist.first .. l_worklist.last loop 
72          if l_worklist(i) = 'test1' then 
73            delete from test where role_name = 'test1_companion'; 
74          end if; 
75        end loop; 
76      end if; 
77    end; 
78end; 
79
80/
81
82create or replace trigger test_trig_row after insert or delete on test 
83  for each row 
84  begin 
85    if inserting then 
86      global_state.role_inserted(:NEW.role_name); 
87    elsif deleting then 
88      global_state.role_deleted(:OLD.role_name); 
89    end if; 
90  end; 
91
92/
93
94create or replace trigger test_trig after insert or delete on test 
95  begin 
96    global_state.cleanup_roles(); 
97  end; 
98
99/
100
101insert into test values ('test1')
102;
103
104commit
105
106
107
108select * from test
109;
110
111delete from test where role_name = 'test1';
112commit;
113select * from test;