Raise application error in a trigger in case of invalid new value : NEW OLD « Trigger « Oracle PL/SQL Tutorial






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>
SQL>
SQL>








28.3.NEW OLD
28.3.1.Old and new value
28.3.2.Reference new value with :NEW in a before insert or update trigger
28.3.3.Refernece an old value in :OLD after update trigger
28.3.4.:old and :new Pseudo-records, Example 1
28.3.5.:old and :new Pseudo-records, Example 2
28.3.6.Raise application error in a trigger in case of invalid new value
28.3.7.REFERENCING OLD AS old NEW AS new
28.3.8.REFERENCING OLD AS old_values NEW AS new_values
28.3.9.Output new and old value in a before update trigger