Mega Code Archive

Trigger to check inserting value

SQL> -- SQL> SQL> set echo on SQL> SQL> create table t1 ( x int primary key, y int ); Table created. SQL> create table t2 (col1 int references t1, col2 int check (col2>0)); Table created. SQL> create index t2_idx on t2(col2,col1); Index created. SQL> SQL> create trigger t2_trigger before insert or update of col1, col2 on t2 for each row 2 begin 3 if ( :new.col1 < :new.col2 ) then 4 raise_application_error(-20001,'Invalid Operation Col1 cannot be less then Col2'); 5 end if; 6 end; 7 / Trigger created. SQL> SQL> insert into t2(col1, col2) values(1,2); insert into t2(col1, col2) values(1,2) * ERROR at line 1: ORA-20001: Invalid Operation Col1 cannot be less then Col2 ORA-06512: at "RNTSOFT.T2_TRIGGER", line 3 ORA-04088: error during execution of trigger 'RNTSOFT.T2_TRIGGER' SQL> insert into t2(col1, col2) values(2,1); insert into t2(col1, col2) values(2,1) * ERROR at line 1: ORA-02291: integrity constraint (RNTSOFT.SYS_C006395) violated - parent key not found SQL> SQL> SQL> drop table t1 cascade constraints; Table dropped. SQL> SQL> drop table t2 cascade constraints; Table dropped. SQL> SQL> --