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.

• Michael Kutz: It appears you are using SQL*Developer. Please confirm.
• civesuas_sine: yes that is true..

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.

SQL*Developer asks for BIND value for "new"

Workaround

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

A simple "recompile" works. Editing the TRIGGER code

• civesuas_sine: I run trigger by toad so it went without that problem... but still it does not insert into table I changed the trigger a little bit
• Michael Kutz: :old does not exist for INSERT statements. See section Triggers for Building Complex Updatable Views for example. docs.oracle.com/database/121/LNPLS/triggers.htm#LNPLS770

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.

• civesuas_sine: i edited code, but still not inserting into table

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.

• civesuas_sine: I do this because I have another external table but i cant do triggers on it. I must do it via view of that external table and to insert into matches..
• Albert Godfrind: I was not questioning the use of a trigger. I meant specifically: what is the purpose of the select after the insert into the table ? What is the point of that since you do not use the result of that select ? Now I see you replaced your INSERT VALUES() with an INSERT ... SELECT and that makes more sense.
• civesuas_sine: after insert into table I use function which will get all sub_list from MATCHES table
• Albert Godfrind: So is the trigger working now ?
• civesuas_sine: I searched and what i read is that you cant create trigger on view or external table ... i created job and created procedure which will insert into table