I had the below requirement

`

I had 2 tables, while before inserting or updating on table 1 we need to check a trigger with below conditions where id should not be null and id needs to exist in table 2 qid column and length of id should be greater than length 6

If the above cases fails need to print an exception in the same trigger ..

please help me in above trigger

CodePudding user response：

That's not a trigger issue, but various constraints:

• foreign key will check whether ID exists
• NOT NULL will make sure it isn't empty
• check constraint will check its length (but that's irrelevant as foreign key constraint handles that anyway)

So:

SQL> create table table2
  2    (qid     number constraint pk_2 primary key,
  3     name    varchar2(10));

Table created.

SQL> create table table1
  2    (id1     number constraint pk_1 primary key,
  3     id      number constraint fk_12 references table2 (qid) not null,
  4     name    varchar2(10),
  5     address varchar2(10),
  6     --
  7     constraint ch_len_id check (length(id) > 6)  --> you can skip that
  8    );

Table created.

SQL>

If it must be a trigger, then:

SQL> create or replace trigger trg_biu_t1
  2    before insert or update on table1
  3    for each row
  4  declare
  5    l_cnt number;
  6  begin
  7    if :new.id is null then
  8       raise_application_error(-20000, 'ID can not be NULL');
  9
 10    elsif length(:new.id) <= 6 then
 11       raise_application_error(-20001, 'ID length must be greater than 6');
 12
 13    else
 14       select count(*)
 15         into l_cnt
 16         from table2
 17         where qid = :new.id;
 18
 19       if l_cnt = 0 then
 20          raise_application_error(-20002, 'ID does not exist in table2');
 21       end if;
 22    end if;
 23  end trg_biu_t1;
 24  /

Trigger created.

SQL>