Home » Developer & Programmer » Forms » FORMS and AFTER INSERT TRIGGER
FORMS and AFTER INSERT TRIGGER [message #488537] Sun, 09 January 2011 01:03 Go to next message
im99_chs
Messages: 14
Registered: November 2006
Junior Member
Goodmorning to all!!!!!!!!

I created 3 tables A,B,C with relation A->B and A->C.
I created also in database a after insert trigger.
I have two oracle forms (master - detail) A->B and A->C. When i am inserting a new record in form A->B the after insert trigger saves some fieldsof B table into the C table also. But when i am enter-execute query in the second form A->C i am not seeing anything. I am searching into the DB and for my surprise the fields in the C table are there.
Did I have forgot anything to write at forms level or db level. Please help me!

Christos
Re: FORMS and AFTER INSERT TRIGGER [message #488538 is a reply to message #488537] Sun, 09 January 2011 01:34 Go to previous messageGo to next message
ranamirfan
Messages: 535
Registered: January 2006
Location: Pakistan / Saudi Arabia
Senior Member

Could you please provide the table Structure of tables A,B,C.
Re: FORMS and AFTER INSERT TRIGGER [message #488552 is a reply to message #488538] Sun, 09 January 2011 05:51 Go to previous messageGo to next message
Littlefoot
Messages: 21807
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
From what I understood: there's a master-detail relationship between tables A and B, as well as A and C.

As you can see values in the C table (I suppose by SELECT * FROM C), did you correctly set C's foreign key columns' values (that connect C's records with the table A)?
Re: FORMS and AFTER INSERT TRIGGER [message #488572 is a reply to message #488538] Sun, 09 January 2011 13:51 Go to previous messageGo to next message
im99_chs
Messages: 14
Registered: November 2006
Junior Member
Thank you for your response!!

The Table A is A(APOFDATE (PK),APOFCODE (PK), CITY)
The Table B is B(APOFDATE,APOFCODE, APOFAA, TRASNPORT)
The Table C is C(APOFDATE,APOFCODE, APOFAA, PERSCODE, TRASNPORT, TRANSPORT_COST, KM, TOLLS)

THE DATABASE TRIGGER IS
CREATE OR REPLACE TRIGGER INS_INTO_TRAPODOSI_NEW_NEW
AFTER INSERT
ON B
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
v_username varchar2(10);

BEGIN
-- Find username of person performing the INSERT into the table
SELECT user INTO v_username
FROM dual;

-- Insert record into C table
INSERT INTO C
( APOFCODE,
APOFDATE,
APOFAA)
VALUES
( :new.APOFCODE,
:new.APOFDATE,
:new.APOFAA);

END;

When I am creating a new record into oracle forms (master detail A -> B) a message apperas which says that two instances have been saved
Into B and into C.

What i am doing wrong ? In DB or in Forms 6i?
Re: FORMS and AFTER INSERT TRIGGER [message #488583 is a reply to message #488572] Mon, 10 January 2011 00:22 Go to previous messageGo to next message
Littlefoot
Messages: 21807
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Here's a test case, based on information you posted:
SQL> CREATE TABLE a
  2  (
  3     apofdate   DATE,
  4     apofcode   NUMBER,
  5     city       VARCHAR2 (20),
  6     CONSTRAINT pk_a PRIMARY KEY (apofdate, apofcode)
  7  );

Table created.

SQL> CREATE TABLE b
  2  (
  3     apofdate    DATE,
  4     apofcode    NUMBER,
  5     apofaa      NUMBER,
  6     transport   NUMBER,
  7     CONSTRAINT fk_ba FOREIGN KEY
  8        (apofdate, apofcode)
  9         REFERENCES a (apofdate, apofcode)
 10  );

Table created.

SQL> CREATE TABLE c
  2  (
  3     apofdate         DATE,
  4     apofcode         NUMBER,
  5     apofaa           NUMBER,
  6     perscode         NUMBER,
  7     transport        NUMBER,
  8     transport_cost   NUMBER,
  9     km               NUMBER,
 10     tolls            NUMBER,
 11     CONSTRAINT fk_ca FOREIGN KEY
 12        (apofdate, apofcode)
 13         REFERENCES a (apofdate, apofcode)
 14  );

Table created.

A trigger (differes from the one you wrote; why do you select USER and never use it? What's the purpose of the REFERENCING clause? You just repeat the default).
SQL> create or replace trigger trg_ai_b
  2    after insert on b
  3    for each row
  4  begin
  5    insert into c
  6      (apofcode, apofdate, apofaa)
  7    values
  8      (:new.apofcode, :new.apofdate, :new.apofaa);
  9  end;
 10  /

Trigger created.

Insert master (table A) and detail (table B) records:
SQL> insert into a (apofdate, apofcode, city) values
  2    (trunc(sysdate), 1, 'London');

1 row created.

SQL> insert into b (apofdate, apofcode, apofaa, transport) values
  2    (trunc(sysdate), 1, 100, 1000);

1 row created.

OK, so what do we have? Is there anything in table C?
SQL> select * from c;

APOFDATE              APOFCODE     APOFAA   PERSCODE  TRANSPORT TRANSPORT_COST         KM      TOLLS
------------------- ---------- ---------- ---------- ---------- -------------- ---------- ----------
10.01.2011 00:00:00          1        100
Yes, there is! Let's simulate master-detail select:
SQL> select a.apofdate, a.apofcode, c.apofaa
  2  from a, c
  3  where c.apofdate = a.apofdate
  4    and c.apofcode = a.apofcode;

APOFDATE              APOFCODE     APOFAA
------------------- ---------- ----------
10.01.2011 00:00:00          1        100
Seems to be fine.

So, what might be a problem? A form, perhaps? Did you correctly create master-detail relationship in an A-C form?
Re: FORMS and AFTER INSERT TRIGGER [message #489511 is a reply to message #488583] Mon, 17 January 2011 03:56 Go to previous messageGo to next message
im99_chs
Messages: 14
Registered: November 2006
Junior Member
I solve my problem with the help of cursors!
Thanks a lot to all of you!
Re: FORMS and AFTER INSERT TRIGGER [message #579066 is a reply to message #489511] Thu, 07 March 2013 05:36 Go to previous message
mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad
Senior Member
Hello 1m99_chs ,

Here is the same problem---I created 3 tables A,B,C with relation A->B and A->C.
I created also in database a after insert trigger.
I have two oracle forms (master - detail) A->B and A->C. When i am inserting a new record in form A->B the after insert trigger saves some fieldsof B table into the C table also. But when i am enter-execute query in the second form A->C i am not seeing anything. I am searching into the DB and for my surprise the fields in the C table are there.
Did I have forgot anything to write at forms level or db level. Please help me!

I saw you posted like that i solved this using cursors and i am requesting you to can you please send that...

Thanks.
Previous Topic: Display employee details
Next Topic: cannot display sysdate in oracle form
Goto Forum:
  


Current Time: Thu Apr 18 20:56:48 CDT 2024