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
|
create table table1 ( id integer );
create table table2 ( id integer, table1_id integer );
create table table_log ( id integer, action varchar2(30), table_name varchar2(30) );
-- trigger principal --
create or replace trigger tg1 before insert or update or delete
on table1
for each row
declare
begin
if inserting then
insert into table_log ( table_name, action, id ) values ( 'table1', 'insert', :new.id ) ;
elsif updating then
insert into table_log ( table_name, action, id ) values ( 'table1', 'update', :new.id ) ; else
insert into table_log ( table_name, action, id ) values ( 'table1', 'delete', :old.id ) ;
end if ;
end ;
/
-- trigger realisant la mise a jour dans la table etrangere (insert ou delete selon les cas)
create or replace trigger tg2 after insert or update or delete
on table1
declare
err integer ;
text varchar2 ( 100 ) ;
cursor c1 is
select a.*, a.rowid
from table_log a
where a.table_name = 'table1'
order by a.rowid ;
function tester_table2(id integer) return integer
as
cursor c1 is
select table1_id
from table2
where table1_id = id ;
rec1 c1%rowtype ;
begin
open c1 ;
fetch c1 into rec1 ;
if c1%found then
return 1 ;
else
return 0 ;
end if ;
end ;
begin
/* partie a ecrire en C */
for rec1 in c1 loop
if rec1.action='delete' then
if rec1.id = 7 then
err := -20021 ;
else
delete table2 where table1_id=rec1.id ;
end if ;
elsif tester_table2(rec1.id)=0 then
insert into table2 (table1_id) values ( rec1.id ) ;
end if ;
delete table_log where rowid=rec1.rowid ;
end loop ;
/* fin de la partie a ecrire en C */
if err < 0 then
if err = -20021 then
text := 'suppression impossible dans ce cas ...' ;
end if ;
raise_application_error ( err, text ) ;
end if ;
end ; |
Partager