That insert into matches should work. I can't spot anything wrong. As indicated, it could be a side effect of SQL Developer ...
However the select that follows will not work. You cannot use
:new that way - not in a select statement.
:new represents the values of the columns you used in the original insert into the view.
In addition you cannot write a singleton select (that is what you do) without including an
into to specify the variables that receive the results.
If you want to find out whether anything exists in MATCHES with a given value for a column, then you can do something like that:
where comp_name = :new.sub_list;
p_count as one more variable. If it is 0, then no rows with that value exists.
But the question is: why do you do this ? In your code you do not use the result of that select ... Or do you want to do this check before the insert ?
Just to confirm. I tried your scenario and everything works fine. I did the following:
- Defined an external table over a CSV file
- Defined a view on that external table
- Defined an "instead of insert" trigger on that view
Everything works just fine. The insert on the view is correctly handled by the trigger.
Here is the complete example:
The CSV file is like this:
Here is the external table definition:
CREATE TABLE us_cities_ext (
ORGANIZATION EXTERNAL (
DEFAULT DIRECTORY data_files
ACCESS PARAMETERS (
FIELDS TERMINATED BY "|" (
The view on the external table:
create or replace view us_cities_v as
select * from us_cities_ext;
create or replace trigger us_cities_v instead of insert on us_cities_v
for each row
insert into us_cities (id, city, state_abrv, rank90, pop90)
values (:new.id, :new.city, :new.state_abrv, :new.rank90, :new.pop90);
This trigger inserts into another table (US_CITIES) with a similar structure to the external table.
Trying it out. Insert into the view:
SQL> insert into us_cities_v values (1001,'Bismarck','ND', 68981, 231, -100.74869, 46.766667);
1 row created.
The row was correctly inserted in the target table:
SQL> select * from us_cities where id >= 1000;
ID CITY STATE_ POP90 RANK90
------ -------------- ------ -------- -------
1001 Bismarck ND 68981 231
1 row selected.