Check new value before insert and update

  
SQL>
SQL> CREATE TABLE MyTable (
  2    num_col    NUMBER,
  3    char_col   VARCHAR2(60)
  4    );

Table created.

SQL>
SQL>
SQL> CREATE OR REPLACE TRIGGER OnlyPositive
  2    BEFORE INSERT OR UPDATE OF num_col
  3    ON MyTable
  4    FOR EACH ROW
  5  BEGIN
  6    IF :new.num_col < 0 THEN
  7      RAISE_APPLICATION_ERROR(-20100, 'Please insert a positive value');
  8    END IF;
  9  END OnlyPositive;
 10  /

Trigger created.

SQL>
SQL> INSERT INTO MyTable (num_col, char_col)
  2    VALUES (1, 'This is row 1');

1 row created.

SQL>
SQL> INSERT INTO MyTable (num_col, char_col)
  2    VALUES (-1, 'This is row -1');
INSERT INTO MyTable (num_col, char_col)
            *
ERROR at line 1:
ORA-20100: Please insert a positive value
ORA-06512: at "JAVA2S.ONLYPOSITIVE", line 3
ORA-04088: error during execution of trigger 'JAVA2S.ONLYPOSITIVE'


SQL>
SQL> drop table MyTable;

Table dropped.

SQL>
SQL>

Related examples in the same category

1.	Create a 'BEFORE UPDATE' Trigger
2.	Create a BEFORE UPDATE trigger on the emp table

Check new value before insert and update : Before Update Trigger « Trigger « Oracle PL / SQL

Related examples in the same category