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 Go to next message](/forum/theme/orafaq/images/down.png) |
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 #488572 is a reply to message #488538] |
Sun, 09 January 2011 13:51 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/72104.gif) |
Littlefoot
Messages: 21818 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 #579066 is a reply to message #489511] |
Thu, 07 March 2013 05:36 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.gravatar.com/avatar/a59247482a11edb9544247f102223e8d?s=64&d=mm&r=g) |
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.
|
|
|
Goto Forum:
Current Time: Thu Sep 12 04:09:25 CDT 2024
|