Trigger to check inserting value : Business Logic Trigger « Trigger « Oracle PL / SQL

1. Oracle PL / SQL
2. Trigger
3. Business Logic Trigger

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 "JAVA2S.T2_TRIGGER", line 3
ORA-04088: error during execution of trigger
'JAVA2S.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
(JAVA2S.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> --

 

Related examples in the same category