Mega Code Archive
Raise application error in a trigger in case of invalid new value
<![CDATA[
SQL>
SQL> create table t ( x int );
Table created.
SQL>
SQL> create trigger t_trigger before insert on t for each row
2 begin
3 for x in ( select *
4 from dual
5 where :new.x > 10)
6 loop
7 raise_application_error( -20001, 'check failed' );
8 end loop;
9 end;
10 /
Trigger created.
SQL> show errors
No errors.
SQL>
SQL> insert into t select 1 from all_users;
15 rows created.
SQL>
SQL> set autotrace traceonly statistics
SQL>
SQL> insert into t select 1 from all_users;
15 rows created.
Statistics
----------------------------------------------------------
16 recursive calls
15 db block gets
73 consistent gets
0 physical reads
0 redo size
924 bytes sent via SQL*Net to client
947 bytes received via SQL*Net from client
6 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
15 rows processed
SQL>
SQL> set autotrace off
SQL>
SQL> drop table t;
Table dropped.
SQL>
]]>