Link to home
Create AccountLog in
Avatar of g_currier
g_currierFlag for Germany

asked on

Triggers not working with Foreign Key

Hello,

I am seeking some help with something that must be obvious to you but has eluded me.  I have created a rather simple set of tables, two of which are related via a foreign key.  The problem I am having is getting the triggers to work in such a way that I do not violate referential integrity.  I have tried the triggers without the Fk and have found that all works as it should (a couple of autoincrement triggers and another that inserts data to another table).  There's a bit of redundancy in the triggers and that was more to see if I could do it than anything else.

In any case, once the FK is enabled, the fun begins.  I am trying to develop an app in APEX and I need a Master Detail report.  Without a child table, that ain't happening.  Would someone please have a look over the following code and spotcheck me?  I appreciate the extra set of eyes...

Thanks
 
/*
GCurrier
Contact DB
3 Jan 2011 v5

v1: 11 Dec 2010 - original with only simple tables, autosequencing triggers and sequences
v2: 27 Dec 2010 - added trigger for autopop on notes table after creation of agency record
v3: 29 dec 2010 - added FK from notes table to agency table on PK
v3.5: 29 Dec 2010 - added Agency/Notes view
v4: 30 Dec 2010 - revamped table structure; moved FK to agency table - link to notes table;
                  restructured data import through views and then export to csv matching new table structure.
v5: 3 Jan 2011 - restructured again; revised agency table to add last_visit_date to table; added cascade to the fk;
                still unable to insert with FK enabled.  
*/

--DB Maintenance---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
drop table agency purge;
drop table notes purge;
drop table events_ purge;
drop sequence seq_agency;
drop sequence seq_notes;
drop sequence seq_events;

--Agency Table--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
CREATE TABLE agency 
    ( 
     id_number INTEGER not null primary key,
     agency_id integer not null,
     agency_name VARCHAR2 (60) , 
     agency_phone varchar2 (25) , 
     agency_fax varchar2 (25) , 
     street VARCHAR2 (75) , 
     City VARCHAR2 (40) , 
     PLZ INTEGER , 
     Country VARCHAR2 (25) , 
     Web_Page VARCHAR2 (50) , 
     IATA VARCHAR2 (10) , 
     PCC VARCHAR2 (10),
     Last_visit_date DATE
    ) 
;
--Call Notes Table---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
CREATE TABLE Notes 
    ( 
     id_number INTEGER not null primary key, 
     note_id integer not null,
     call_date DATE , 
     contact_fname VARCHAR2 (35) , 
     contact_lname varchar2 (35) , 
     job_title varchar2(60) ,
     contact_email VARCHAR2 (50) , 
     contact_Phone varchar2 (25),
     call_details varchar2(4000)      
    ) 
;
--Events Table--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
CREATE TABLE Events_ 
    ( 
     id_number INTEGER not null primary key, 
     event_id integer not null,
     event_name VARCHAR2 (50) , 
     event_date date , 
     event_location varchar2 (50)  ,
     event_detail varchar2(2500)     
    ) 
;
--Agency sequence and trigger----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
CREATE Sequence seq_agency;
/
CREATE TRIGGER trig_agency Before
  INSERT ON agency FOR EACH Row
    BEGIN
      IF inserting
        THEN
          IF :New.id_number IS NULL
            THEN
              select seq_agency.nextval into :new.id_number from dual;
          END IF;
          if :New.agency_id is null
            then
              select seq_agency.currval into :new.agency_id from dual;
          end if;
       END IF;
END;
/
--Only run this after sample data is installed
--alter table agency add constraint fk_agency_notes foreign key(agency_id) references notes(id_number)on delete cascade enable;
--
drop trigger trig_agency_notes;
CREATE TRIGGER trig_agency_notes before
  insert on agency
    begin
      if inserting then
        insert into notes (call_details) 
          values('Insert Notes Here');
      end if;
  end;
/
--Notes sequence and triggers----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
CREATE Sequence seq_notes;
/
CREATE TRIGGER trig_notes before 
  insert on notes for each Row
    BEGIN
      IF inserting THEN
        IF :New.id_number IS NULL
          THEN
            select seq_notes.nextval into :new.id_number from dual;
        END IF;
        if :New.note_id is null
          then
            select seq_notes.currval into :new.note_id from dual;
        end if;
      END IF;
END;
/
--events_ sequence and trigger---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
CREATE Sequence seq_events;
/
CREATE TRIGGER trig_events Before
  insert on events_ for each row 
    BEGIN 
    IF inserting THEN 
      IF :New.id_number IS NULL 
        THEN
          select seq_events.nextval into :new.id_number from dual;
      END IF;
      if :new.event_id is null
        then
          select seq_events.currval into :new.event_id from dual;
      end if;
    END IF;
END;
/

commit;

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of jaiminpsoni
jaiminpsoni
Flag of India image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of g_currier

ASKER

This is the error when I insert using this:

insert into agency (agency_name,agency_phone,agency_fax,last_visit_date)
  values('sample','918273645','564738291','03-jan-2011');
 
Error starting at line 1 in command:
insert into agency (agency_name,agency_phone,agency_fax,last_visit_date)
  values('sample','918273645','564738291','03-jan-2011')
Error report:
SQL Error: ORA-00001: unique constraint (GPDB_USER1.SYS_C0014724) violated
00001. 00000 -  "unique constraint (%s.%s) violated"
*Cause:    An UPDATE or INSERT statement attempted to insert a duplicate key.
           For Trusted Oracle configured in DBMS MAC mode, you may see
           this message if a duplicate entry exists at a different level.
*Action:   Either remove the unique restriction or do not insert the key.

Open in new window


I am not sure what is going on at this point...it worked before with this trigger:
CREATE TRIGGER trig_agency Before
  INSERT ON agency FOR EACH Row
    BEGIN
      IF inserting
        THEN
          IF :New.id_number IS NULL
            THEN
              select seq_agency.nextval into :new.id_number from dual;
          END IF;
          if :New.agency_id is null
            then
              select seq_agency.currval into :new.agency_id from dual;
          end if;
       END IF;
END;
/

Open in new window

...whereby the PK is auto incremented along with a separate value that uses the currval.

The insert statement reflects from the lack of id_number and agency_id values (allwoing the trigger to handle it.  I know that trigger use should be minimized but this, to me is the simplest way.

Also, I altered the fk to match your example,  I need only to get it working with the triggers in place.
Does this mean that the issue with Foreigh key is resolved?

OK, one simple suggestion is to modify insert to use seq_agency.nextval for id_number rather than using trigger.

insert into table name (column1, ....) values (seqname.nextval,....)

Please send the output of...

Send the output of
select seq_agency.nextval from dual;

and select max(id_number) from agency;

I found the problem...It had nothing to do with the triggers.  It was the sequence.  The sample data I entered already had the PK and ID info inserted.  As the PK is unique, you can't insert another ok with the same number (from the sequence).  I recreated the sequence to start at the value after the last record and I have no probs.  Thanks for helping me think about it!