Oracle insert and replace value with it's primary key value from another table

1
votes

I have two tables.

TABLE2 has fields CODE_NM and DESCRIPTION. CODE_NM is the primary key in this table and a foreign key in TABLE1 to this table.

TABLE2:

|---------------------|------------------|
|      CODE_NM        |     DESCRIPTION  |
|---------------------|------------------|
|          001        |description 1 text|
|---------------------|------------------|
|          002        |description 2 text|
|---------------------|------------------|

TABLE1:

|---------------------|----------------------|------------------|
|      CODE_NM        |  DESCRIPTION_DETAIL  |      USER        |
|---------------------|----------------------|------------------|
|          001        |  some text in here   |      USERID      |
|---------------------|----------------------|------------------|

Every time a row is inserted to TABLE1, a user will input the DESCRIPTION, DESCRIPTION_DETAIL, and USER. With each insert, I want to replace the DESCRIPTION with CODE_NM instead. It is certain that for any DESCRIPTION inserted, there will be a value for it with its associated primary key in TABLE2.

So I should be able to insert:

INSERT INTO TABLE1 (CODE_NM, DESCRIPTION_DETAIL, USER)
VALUES ('description 1 text','this it the situation','USERID');

and instead of 'description 1 text', I want to display the primary key, which is '001':

|---------------------|----------------------|------------------|
|      CODE_NM        |  DESCRIPTION_DETAIL  |      USER        |
|---------------------|----------------------|------------------|
|          001        |  some text in here   |      USERID      |
|---------------------|----------------------|------------------|

Is this possible to do using a trigger?

3
oracleplsqldatabase-trigger

3 Answers

3
votes

You can create such a before insert trigger for table1 :

SQL> create or replace trigger trg_tbl1_bi
  before insert on table1   
  for each row    
declare
  begin
     select code_nm
       into :new.code_nm 
       from table2 
      where trim(description) = trim(:new.code_nm);
   exception when no_data_found then 
      raise_application_error(-20001,'No matching record found!');  
  end;  
end;
/

but you need an exact match among those strings(the values of table2.description column and :new.code_nm of table1 )

0
votes

You can create trigger

create or replace trigger tbi_table2
before insert on table2
on each row
declare
begin
  :new.code_nm := select code_nm from table1 where description=:new.code_nm;
end;
0
votes

Here's what ended up working:

create or replace trigger trigger_name
before insert on TABLE1
FOR EACH ROW
DECLARE
   v_code_nm table2.code_nm%type;
begin
 SELECT code_nm INTO v_code_nm FROM TABLE2 WHERE DESCRIPTION=:new.code_nm;
  :new.code_nm := v_code_nm;
end;