demo01.sql

来自「Expert Oracle Database Architecture 9i a」· SQL 代码 · 共 66 行

create table t2 ( cnt int );
insert into t2 values ( 0 );
commit;
create table t ( x int check ( x>0 ) );
create trigger t_trigger
before insert or delete on t for each row
begin
   if ( inserting ) then
        update t2 set cnt = cnt +1;
   else
        update t2 set cnt = cnt -1;
   end if;
   dbms_output.put_line( 'I fired and updated '  ||
                                   sql%rowcount || ' rows' );
end;
/
insert into t values (1);
insert into t values(-1);
select * from t2;
 

rollback;


create or replace procedure p
as
begin
        insert into t values ( 1 );
        insert into t values (-1 );
end;
/
select * from t;
select * from t2;
begin
        p;
end;
/
select * from t;
select * from t2;


begin
    p;
exception
    when others then null;
end;
/
select * from t;
select * from t2;

rollback;

 

begin
    savepoint sp;
    p;
exception
    when others then
        rollback to sp;
end;
/
select * from t;
select * from t2;