sql%rowcount macro
SQL> SQL> create table myTable2 ( cnt int ); Table created. SQL> SQL> insert into myTable2 values ( 0 ); 1 row created. SQL> SQL> create table t ( x int check ( x>0 ) ); Table created. SQL> SQL> create trigger t_trigger before insert or delete on t for each row 2 begin 3 if ( inserting ) then 4 update myTable2 set cnt = cnt +1; 5 else 6 update myTable2 set cnt = cnt -1; 7 end if; 8 dbms_output.put_line( 'fired and updated ' || sql%rowcount || ' rows' ); 9 end; 10 / Trigger created. SQL> SQL> create or replace procedure p 2 as 3 begin 4 insert into t values ( 1 ); 5 insert into t values (-1 ); 6 end; 7 / Procedure created. SQL> select * from t; no rows selected SQL> select * from myTable2; CNT ---------- 0 SQL> begin 2 p; 3 end; 4 / fired and updated 1 rows fired and updated 1 rows begin * ERROR at line 1: ORA-02290: check constraint (JAVA2S.SYS_C0010265) violated ORA-06512: at "JAVA2S.P", line 5 ORA-06512: at line 2 SQL> select * from t; no rows selected SQL> select * from myTable2; CNT ---------- 0 SQL> SQL> SQL> begin 2 p; 3 exception 4 when others then null; 5 end; 6 / fired and updated 1 rows fired and updated 1 rows PL/SQL procedure successfully completed. SQL> select * from t; X ---------- 1 SQL> select * from myTable2; CNT ---------- 1 SQL> SQL> rollback; Rollback complete. SQL> SQL> begin 2 savepoint sp; 3 p; 4 exception 5 when others then 6 rollback to sp; 7 end; 8 / fired and updated 1 rows fired and updated 1 rows PL/SQL procedure successfully completed. SQL> select * from t; no rows selected SQL> select * from myTable2; CNT ---------- 0 SQL> SQL> SQL> SQL> drop table t; Table dropped. SQL> drop table myTable2; Table dropped.