Creating ON INSERT trigger on Oracle table

Question

I have to create this funky ON INSERT trigger on Oracle database.

I have been given this old project that is missing part of the source code so now I have to work some magic using database only. The trigger should update the row on insert with missing values from another view.

Here is some made up sample data:

I have this table, lets call it TABLE1. Then I have this view, lets call it VIEW2. In view there could be more than one record with same CODE but they are exact duplicates, so any row is valid. There is only a few of this duplicates, but the customer has not filtered them out yet.

TABLE1 and VIEW1 have the following fields (CODE, PARTNER, COUNTRY).
TABLE1 also has field COMMENT.

All fields are varchar type.

In this field COMMENT is of varchar type and has a suffix added to its value like so: "-XXX-CODE", for example "some comment-XXX-123".

Here is my scenarion:

This gets inserted in TABLE1:

**CODE;PARTNER;COUNTRY;COMMENT** 

 - 123;TEST;USA;SOME COMMENT
 - 145;TEST2;GB;SOME COMMENT
 - NULL;NULL;NULL;SOME COMMENT-XXX-154

This is where the trigger should kick in. - IF field CODE IS NULL, the trigger shuld fire and - parse out the code based on the -XXX- prefix (take value 154 in this example) - make a select on the VIEW (select code, partner, country from VIEW1 where code = 154 - if record is found it should update the TABLE1 with code, partner and country value

Well, i hope that the situation is clear. Since I have no experience with oracle a solution to my problem is much appreciated.

Gratitude.

knagaev knagaev · Accepted Answer · 2013-03-20T09:04:38

I don't know what is a type of the column CODE so I guessed it is INTEGER. Here you are

CREATE OR REPLACE TRIGGER tri_check_nulls
  BEFORE INSERT ON TABLE1
  FOR EACH ROW WHEN (new.CODE is NULL)
DECLARE
    VIEW_CODE   INTEGER;
BEGIN

    VIEW_CODE := TO_NUMBER(SUBSTR(new.COMMENT, INSTR(new.COMMENT, '-', -1, 1) + 1));

    select distinct v.CODE, v.PARTNER, v.COUNTRY
        into  :new.CODE, :new.PARTNER, :new.COUNTRY
        from VIEW1 v
        where v.CODE = VIEW_CODE;

END;
/

Of course, you have to add some error handlers. And please read the Oracle docs CREATE TRIGGER

Creating ON INSERT trigger on Oracle table

1 Answers