| pre-insert inserts records into 2 tables [message #606562] |
Mon, 27 January 2014 00:20  |
 |
mist598
Messages: 1195
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 #606592 is a reply to message #606566] |
Mon, 27 January 2014 04:03  |
|
mist598
Messages: 1195
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 #606603 is a reply to message #606600] |
Mon, 27 January 2014 04:32 |
cookiemonster
Messages: 13923
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 #606673 is a reply to message #606670] |
Tue, 28 January 2014 03:36 |
|
mist598
Messages: 1195
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;
-
Attachment: image1.png
(Size: 11.73KB, Downloaded 1251 times)
|
|
|
|
|
|
|
|
|
|
|
|
| Re: pre-insert inserts records into 2 tables [message #606697 is a reply to message #606691] |
Tue, 28 January 2014 04:10 |
cookiemonster
Messages: 13923
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?
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
