| commit updated date problem??? [message #153466] |
Mon, 02 January 2006 11:40  |
ramisy2k
Messages: 150
Registered: April 2005
|
Senior Member |
|
|
Hello,
I have db block where I execute 14 records at a time (using a where clause in the blocks property pallette) and then update/delete the data..
The block has five coilumns
A,B,C,D
A,B,C,D are jointly primary key..(i.e. they cannot be repeated combined)
the problem is that when I execute the query and then update the values and then press F10 to save, the values are not updated
suppose i execute query and the following data is displayed in the form
A B C D
12 11 1201 1
12 11 1202 2
12 11 1203 3
12 11 1204 4
12 11 1205 5
now suppose I change all executed values in the block like that
A B C D
12 11 1205 1
12 11 1204 2
12 11 1203 3
12 11 1202 4
12 11 1201 5
and now to update when i press the F10 key to save all the changes the form displays an error saying
ORA-00001: unique constraint (TSW.IB) violated
I am unable to find why it is happening..which constarnt for which record is being violated..
please help me to solve this..
regards..
|
|
|
|
| Re: commit updated date problem??? [message #153470 is a reply to message #153466] |
Mon, 02 January 2006 12:56  |
RJ.Zijlstra
Messages: 104
Registered: December 2005
Location: Netherlands - IJmuiden
|
Senior Member |
|
|
If you press F10, what will happen?
Forms will update a row to ( 1th row in (example) updated block)
'12 11 1205 1 '
But when I look at your original data, you now will violate the PK.
Possible solution:
First delete original row ( in pre-update trigger) and then insert.
NOTE: I do not know if you can delete! (Parent-child rel. etc)
HTH,
|
|
|
|
| Re: commit updated date problem??? [message #153472 is a reply to message #153470] |
Mon, 02 January 2006 13:25 |
ramisy2k
Messages: 150
Registered: April 2005
|
Senior Member |
|
|
RJ.Zijlstra
thanks for ur reply...
how can i delete the original row in the pre-update trigger..
suppose i have made chanages to all the 14 records in the same way as in my original question and now i want to save the data..
would u please tell me the code i need to wqrite in pre-update trigger??
regards
|
|
|
|
| Re: commit updated date problem??? [message #153473 is a reply to message #153472] |
Mon, 02 January 2006 14:20 |
 |
Littlefoot
Messages: 21818
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
A default form, written upon sample data you provided, will NOT result in any error - it will allow you to make changes as you wanted to.
As you didn't provide table description nor its constraints and indexes, it isn't easy to guess what might be wrong. Obviously, unique constraint was violated. What is the result of the following query (connect to the "TSW" schema):
SELECT table_name, column_name
FROM user_cons_columns
WHERE constraint_name = 'IB';
This should give you column names whose values, when combined, must be unique in order not to violate the "IB" constraint.
Is it, therefore, possible that changes you made in the form violate this constraint? Perhaps you didn't show all columns on the form and this fact was out of the sight?
|
|
|
|
| Re: commit updated date problem??? [message #153474 is a reply to message #153472] |
Mon, 02 January 2006 14:23 |
RJ.Zijlstra
Messages: 104
Registered: December 2005
Location: Netherlands - IJmuiden
|
Senior Member |
|
|
Block table = 'TABLE'
in pre-update trigger:
Declare
cursor C_EX is
select 'x'
from TABLE
where a=12
and b=11
and c=1201
and d=5;
R_Ex C_EX%Rowtype;
BEGIN
open C_EX;
fetch C_EX into R_EX;
if C_EX%Found then
delete from TABLE where a=12 andb=11 and c=1201 and d=5;
end if;
close C_Ex;
--
Insert into table TABLE( a,b,c,d)
values(....);
commit;
END;
In the cursor replace the hardcoded values 12, 11 etc with
:BLOCKNAME.FIELDA, etc ( your names!)
Explanation:
With the open and fetch cursor we check if a row is already in the database. If it is, we delete it.
Then we Insert the new values.
BUT BEWARE:
You van only do this if there are no child records dependent on this row.
If not, it will work fine.
If there are children, you must think of something else...
HTH,
Regards,
RJ Zijlstra
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
