creating trigger which will insert into table from view

Question

I have view LIVE_MATCHES (home,away,comp_name) and table MATCHES (priority ,sport,sub_list)

I need to create a trigger that inserts in TABLE from VIEW after the view is updated or inserted (doesn't matter) and to check that unique column (sub_list) in table Matches isn't already inserted. Only need to insert from view is comp_name, sport and priority are fixed variables.

 CREATE OR REPLACE TRIGGER LIVE_MATCHES_TO_MATCHES 
        instead of insert ON LIVE_MATCHES 
       for each row
              declare
       p_priority number:= 1;
       p_sport number:=0;
       begin
         insert into MATCHES (sub_list , priority , sport, created) 
         select :old.comp_name , p_priority, p_sport,sysdate
         from dual
          where not exists (      
         select 1 from MATCHES 
        where sub_list = :old.comp_name);
       end;

I edited trigger so it is compiled now without errors but it does not insert into table MATCHES.

What am I doing wrong in this trigger.

Answer 1

SQL*Developer

SQL*Developer has a bad habit of interpreting the :new and :old identifiers of a trigger as run-time BIND variables. Usually, a window will pop up asking for a value for the BIND variable new and/or old.

Workaround

One workaround is to edit the TRIGGER directly by going trough Connections.

A simple "recompile" works.

Answer 2

Your new version works for me if I just change the :old to :new, as Michael Kutz pointed out. When I insert something into LIVE_MATCHES with a value for comp_name that does not exist in MATCHES in the column sub_list, a new row is inserted into matches with the value of comp_name in sub_list.

Answer 3

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:

select count(*)
into p_count
from matches
where comp_name = :new.sub_list;

and define 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 ?

EDIT:

Just to confirm. I tried your scenario and everything works fine. I did the following:

1. Defined an external table over a CSV file
2. Defined a view on that external table
3. 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:

 2|Los Angeles|CA|3485398|2|-118.411201000|34.112101000|
 3|Chicago|IL|2783726|3|-87.684965000|41.837050000|
 4|Houston|TX|1630553|4|-95.386728000|29.768700000|
 5|Philadelphia|PA|1585577|5|-75.134678000|40.006817000|
 6|San Diego|CA|1110549|6|-117.135770000|32.814950000|
 7|Detroit|MI|1027974|7|-83.102198000|42.383100000|
 8|Dallas|TX|1006877|8|-96.765249000|32.794151000|

Here is the external table definition:

CREATE TABLE us_cities_ext (
  id              NUMBER,
  city            VARCHAR2(42),
  state_abrv      VARCHAR2(2),
  pop90           NUMBER,
  rank90          NUMBER,
  longitude       NUMBER,
  latitude        NUMBER
)
ORGANIZATION EXTERNAL (
  TYPE ORACLE_LOADER
  DEFAULT DIRECTORY data_files
  ACCESS PARAMETERS (
    FIELDS TERMINATED BY "|" (
      id,
      city,
      state_abrv,
      pop90,
      rank90,
      longitude,
      latitude
    )
  )
  LOCATION ('us_cities.dat')
);

The view on the external table:

create or replace view us_cities_v as
select * from us_cities_ext;

The trigger:

create or replace trigger us_cities_v instead of insert on us_cities_v
for each row
begin
  insert into us_cities (id, city, state_abrv, rank90, pop90)
  values (:new.id, :new.city, :new.state_abrv, :new.rank90, :new.pop90);
end;
/

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.