Creating trigger which will insert into table from view

oracleplsql

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.

Best Answer

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.

Related Solutions

Sql-server – Oracle GoldenGate add trandata errors

I found out what the problem is, it seems that GoldenGate doesn't work with SQL Express. The server I was connecting to is SQL Express, I'll need to use the Enterprise Edition.

Trigger that records values for 5 columns from 3 tables when a value for column Z in table 3 is of a certain value

If you want to insert data into a different table instead of the original table from the INSERT/UPDATE statement, you need an "Instead Of" trigger.

You find examples on how to handle Instead Of triggers here: http://psoug.org/reference/instead_of_trigger.html and http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/triggers.htm#LNPLS020

In the body of your trigger you first need to get the additional information like fname, lname, etc. With a query like this one:

SELECT si.fname into l_fname, si.lname into l_lname, c.phone into l_phone, c.email into l_email
  FROM si_table si, contact_table c, bookinglog_table b
 WHERE si.student_id=c.student_id AND c.event_id=b.event_id, AND si.test=b.test
   AND <add a clause to uniquely identify a single record>;

Remember to declare the cariables l_fname, l_lname, etc. in the DECLARE block of the trigger:

DECLARE
  l_fname varchar2;
  l_lname varchar2;
  l_phone varchar2;
  l_email varchar2;
BEGIN
...

Even though this solution might work, it is actually not correct. You better create the table grade_table as follows. According to the database normalization form 4 you should not store detailed data multiple times.

CREATE TABLE grade_table (
  student_id number,
  event_id number,
  test number,
  grade NUMBER
);

When you query the table grade_test and need the details on the student, etc. you simply join the detail tables.