Home » Developer & Programmer » Forms » pre-insert inserts records into 2 tables
pre-insert inserts records into 2 tables [message #606562] Mon, 27 January 2014 00:20 Go to next message
mist598
Messages: 991
Registered: February 2013
Location: Hyderabad
Senior Member
Hi all,

I want to insert the records into the emp1 table from emp(base table), i created emp block (emp table is the base table)
and create pre-insert trigger at the emp block. When i enter all the columns and click on the "save", transaction completed 1 record saved. Here i am the record into the both emp & emp1 also, why it inserts into the base table(emp).Please help me.

DECLARE 
    v NUMBER := 1; 
BEGIN 
    first_record; 

    LOOP 
        v := v + 1; 

        INSERT INTO emp1 
                    (empno, 
                     ename, 
                     job, 
                     hiredate, 
                     sal, 
                     comm, 
                     deptno) 
        VALUES     (:EMP.empno, 
                    :EMP.ename, 
                    :EMP.job, 
                    :EMP.hiredate, 
                    :EMP.sal, 
                    :EMP.comm, 
                    :EMP.deptno); 

        EXIT WHEN :system.last_record = 'TRUE'; 
    END LOOP; 
END; 
Re: pre-insert inserts records into 2 tables [message #606564 is a reply to message #606562] Mon, 27 January 2014 01:09 Go to previous messageGo to next message
Littlefoot
Messages: 19711
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
If you want to insert records into EMP1, why didn't you simply base that data block on EMP1 table?

The way you did it now, Forms inserts a record into EMP table by default (as it is a data block), as well into EMP1 (as you told it to in PRE-INSERT trigger.
Re: pre-insert inserts records into 2 tables [message #606565 is a reply to message #606564] Mon, 27 January 2014 01:15 Go to previous messageGo to next message
mist598
Messages: 991
Registered: February 2013
Location: Hyderabad
Senior Member
Quote:
The way you did it now, Forms inserts a record into EMP table by default (as it is a data block), as well into EMP1 (as you told it to in PRE-INSERT trigger.


Yes you are right, so,how can i insert only into emp1 table,suggest me please.
Re: pre-insert inserts records into 2 tables [message #606566 is a reply to message #606565] Mon, 27 January 2014 01:18 Go to previous messageGo to next message
Littlefoot
Messages: 19711
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I told you in my first sentence.
Re: pre-insert inserts records into 2 tables [message #606592 is a reply to message #606566] Mon, 27 January 2014 04:03 Go to previous messageGo to next message
mist598
Messages: 991
Registered: February 2013
Location: Hyderabad
Senior Member
Quote:
If you want to insert records into EMP1, why didn't you simply base that data block on EMP1 table?


Yes you are right Littlefoot.

I created pl/sql trigger.

CREATE OR REPLACE TRIGGER APPS.trg_emp
  BEFORE INSERT ON APPS.EMP for each row
BEGIN
    INSERT INTO emp1
                (empno,
                 ename,
                 job,
                 hiredate,
                 sal,
                 comm,
                 deptno)
    VALUES     (:NEW.empno,
                :NEW.ename,
                :NEW.job,
                :NEW.hiredate,
                :NEW.sal,
                :NEW.comm,
                :NEW.deptno);
END;


I ran the form it inserted the both emp & emp1 table records.
Re: pre-insert inserts records into 2 tables [message #606593 is a reply to message #606592] Mon, 27 January 2014 04:08 Go to previous messageGo to next message
Littlefoot
Messages: 19711
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
So, what's the improvement? In the first message, you did that job using a Forms trigger. In the last message, you did that job using a database trigger. No difference.
Re: pre-insert inserts records into 2 tables [message #606594 is a reply to message #606593] Mon, 27 January 2014 04:10 Go to previous messageGo to next message
mist598
Messages: 991
Registered: February 2013
Location: Hyderabad
Senior Member
Quote:
So, what's the improvement? In the first message, you did that job using a Forms trigger. In the last message, you did that job using a database trigger. No difference.


Is there any option to insert another data table? if yes, suggest me..
Re: pre-insert inserts records into 2 tables [message #606598 is a reply to message #606594] Mon, 27 January 2014 04:19 Go to previous messageGo to next message
Littlefoot
Messages: 19711
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Why do you quote my whole previous message? I know what I said, and there were no other messages in between. What's the purpose of doing this? Why don't you simply REPLY?




Your initial question was:
Quote:

why it inserts into the base table(emp)

I told you why.

Your final question (so far) was:
Quote:

Is there any option to insert another data table?

Now I'm confused. Do you want to prevent this form from inserting data into two tables, or are you exploring all possible ways of inserting data into two tables?

What problem are you trying to solve? What is your task?
Re: pre-insert inserts records into 2 tables [message #606600 is a reply to message #606598] Mon, 27 January 2014 04:26 Go to previous messageGo to next message
mist598
Messages: 991
Registered: February 2013
Location: Hyderabad
Senior Member
Quote:
What problem are you trying to solve? What is your task?


I want to insert the emp1 table values from emp(from emp block).
Re: pre-insert inserts records into 2 tables [message #606602 is a reply to message #606600] Mon, 27 January 2014 04:29 Go to previous messageGo to next message
Littlefoot
Messages: 19711
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
You did that already, didn't you? Using two different (but similar) ways, using Forms and database triggers.
Re: pre-insert inserts records into 2 tables [message #606603 is a reply to message #606600] Mon, 27 January 2014 04:32 Go to previous messageGo to next message
cookiemonster
Messages: 11088
Registered: September 2008
Location: Rainy Manchester
Senior Member
@mist - once again you seem to fail to understand that we know nothing about your system. You need to describe your problem in far more detail than you are doing at moment.

You apparently want to copy data from one table to another.
Under what circumstances should data be copied?
Should all emp records be copied, or only some?
Should the user be able to modify the data?
Re: pre-insert inserts records into 2 tables [message #606604 is a reply to message #606602] Mon, 27 January 2014 04:32 Go to previous messageGo to next message
mist598
Messages: 991
Registered: February 2013
Location: Hyderabad
Senior Member
Quote:
You did that already, didn't you? Using two different (but similar) ways, using Forms and database triggers.


Yes.Ok, Thank You Littlefoot, let me try.. Smile
Re: pre-insert inserts records into 2 tables [message #606606 is a reply to message #606603] Mon, 27 January 2014 04:45 Go to previous messageGo to next message
mist598
Messages: 991
Registered: February 2013
Location: Hyderabad
Senior Member
Quote:
Under what circumstances should data be copied?


After insert the records from emp block & click on the save.

Quote:
Should all emp records be copied, or only some?


If i insert the data from the form & click on the save. Check the emp1 table here the record got inserted. But the record also inserted into the emp also.

My requirement is, while i enter the data into emp block , i want to insert into the emp1 table data only.
Re: pre-insert inserts records into 2 tables [message #606609 is a reply to message #606606] Mon, 27 January 2014 04:50 Go to previous messageGo to next message
Littlefoot
Messages: 19711
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
mist598

while i enter the data into emp block , i want to insert into the emp1 table data only.

Once again: what is the purpose of doing ANYTHING with the EMP block, if you don't want to store data into the EMP table? Base that block on EMP1 table and everything should work properly, without you having to write any kind of triggers.
Re: pre-insert inserts records into 2 tables [message #606613 is a reply to message #606609] Mon, 27 January 2014 05:17 Go to previous messageGo to next message
mist598
Messages: 991
Registered: February 2013
Location: Hyderabad
Senior Member
Please find the below sample form.. Smile
Re: pre-insert inserts records into 2 tables [message #606650 is a reply to message #606613] Tue, 28 January 2014 00:12 Go to previous messageGo to next message
malalwadi
Messages: 9
Registered: May 2012
Location: suadi
Junior Member
Try To Add Next_Record into your code.
Re: pre-insert inserts records into 2 tables [message #606657 is a reply to message #606650] Tue, 28 January 2014 02:05 Go to previous messageGo to next message
mist598
Messages: 991
Registered: February 2013
Location: Hyderabad
Senior Member
Quote:
Try To Add Next_Record into your code.


Where to add,have u try to compile & run.
Re: pre-insert inserts records into 2 tables [message #606670 is a reply to message #606657] Tue, 28 January 2014 03:25 Go to previous messageGo to next message
Littlefoot
Messages: 19711
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I believe it is code in your very first message here; you have a LOOP, but you never navigate to the next record (so this is most probably an infinite loop). Therefore, that would be
  ...
  EXIT WHEN :system.last_record = 'TRUE';
  NEXT_RECORD;
END LOOP;
Re: pre-insert inserts records into 2 tables [message #606673 is a reply to message #606670] Tue, 28 January 2014 03:36 Go to previous messageGo to next message
mist598
Messages: 991
Registered: February 2013
Location: Hyderabad
Senior Member
Hi Littlefoot,

After i enter the records into the emp block & click on the find button it insert to the emp1 table.So for this i add find button in the emp block.

BEGIN 
    Go_block('EMP'); 
    first_record; 
    LOOP 
        BEGIN 
            INSERT INTO emp1 
                        (empno, 
                         ename, 
                         job, 
                         hiredate, 
                         sal, 
                         comm, 
                         deptno) 
            VALUES      (:EMP.empno, 
                         :EMP.ename, 
                         :EMP.job, 
                         :EMP.hiredate, 
                         :EMP.sal, 
                         :EMP.comm, 
                         :EMP.deptno); 

            COMMIT; 
        EXCEPTION 
            WHEN OTHERS THEN 
              NULL; 
        END; 
        [color=red]Exit WHEN :system.last_record = 'TRUE'; 
        next_record; [/color]
    END LOOP; 
EXCEPTION 
    WHEN OTHERS THEN 
      NULL; 
END; 

./fa/11645/0/
  • Attachment: image1.png
    (Size: 11.73KB, Downloaded 99 times)
Re: pre-insert inserts records into 2 tables [message #606682 is a reply to message #606673] Tue, 28 January 2014 03:47 Go to previous messageGo to next message
cookiemonster
Messages: 11088
Registered: September 2008
Location: Rainy Manchester
Senior Member
Never write exception handlers like that - read WHEN OTHERS

Does this code solve your problem?
Re: pre-insert inserts records into 2 tables [message #606686 is a reply to message #606682] Tue, 28 January 2014 03:52 Go to previous messageGo to next message
mist598
Messages: 991
Registered: February 2013
Location: Hyderabad
Senior Member
Quote:
Does this code solve your problem?


No cookiemonster, i am not getting any error's , but why the data inserted into the both EMP& EMP1 tables, i want copy the data from EMP to EMP1, Please suggest me.. Smile
Re: pre-insert inserts records into 2 tables [message #606689 is a reply to message #606686] Tue, 28 January 2014 03:56 Go to previous messageGo to next message
cookiemonster
Messages: 11088
Registered: September 2008
Location: Rainy Manchester
Senior Member
So do you have a database datablock based on emp?
And are you populating that block with a cursor/select statement?
Re: pre-insert inserts records into 2 tables [message #606691 is a reply to message #606689] Tue, 28 January 2014 04:00 Go to previous messageGo to next message
mist598
Messages: 991
Registered: February 2013
Location: Hyderabad
Senior Member
Quote:
So do you have a database datablock based on emp?


Yes.

Quote:
And are you populating that block with a cursor/select statement?


With as select statement.
Re: pre-insert inserts records into 2 tables [message #606697 is a reply to message #606691] Tue, 28 January 2014 04:10 Go to previous message
cookiemonster
Messages: 11088
Registered: September 2008
Location: Rainy Manchester
Senior Member
Then the reason it's inserting into emp is the reason LF already specified - you're putting new records in a datablock based on emp.
This is forms default behaviour, it is behaving exactly as it is designed to, and we've pointed this out before.

The only way to populate a database datablock with data and not have forms think that data is new records that need to be inserted is use the default query functionality - execute_query.
As far as forms is concerned, populating a block from a select statement is no different to having the user enter data.

You really, really need to get your head around the basics of forms default behaviour or you will never be any good at writing forms.


As to what you should do instead, we have no idea, because you still haven't answered any of our questions about what the form is supposed to do:

cookiemonster wrote on Mon, 27 January 2014 10:32
@mist - once again you seem to fail to understand that we know nothing about your system. You need to describe your problem in far more detail than you are doing at moment.

You apparently want to copy data from one table to another.
Under what circumstances should data be copied?
Should all emp records be copied, or only some?
Should the user be able to modify the data?

Previous Topic: pll attachment
Next Topic: Oracle Forms Trigger
Goto Forum:
  


Current Time: Thu Oct 30 10:44:26 CDT 2014

Total time taken to generate the page: 0.10447 seconds