| update and insert new records in a form [message #276629] |
Thu, 25 October 2007 11:47  |
deahayes3
Messages: 203
Registered: May 2006
|
Senior Member |
|
|
I am having the hardest time figuring this out.
I have a form that has about 4 dates and descriptions for each date, I have two tables in the form : admin_entry_table and calendar_table
The 4 dates and descriptions are saved on the admin_entry_table I would like to update the calendar_table with these dates if they exist, the problem I am running into is if I update one of the dates in the admin_entry_table on the form all the dates are reinserted on the calendar_table(I get duplicate entries) even though I just want to update that one record, I tried several triggers but getting now where. Here is a brief description of my tables:
(any ideas are appreciated)
admin_entry table
entryid number pk;
date1 date;
date2_desc char;
date2 date'
date2_desc;
date3 date;
date3_desc char;
calendar_table
calendarid number pk;
cal_date date;
cal_desc char;
entryid;
P.s. there are no fields that exist in the form for calendar_table its just for inserting and updating only
|
|
|
|
|
|
|
|
|
|
| Re: update and insert new records in a form [message #277301 is a reply to message #277095] |
Mon, 29 October 2007 08:25  |
deahayes3
Messages: 203
Registered: May 2006
|
Senior Member |
|
|
This is what I have in the post insert trigger of datablock called env_calendar_entries
begin
if :env_calendar_entries.oscp_review_desc is not null
and :env_calendar_entries.oscp_review_catg is not null
and :env_calendar_entries.oscp_review_date is not null then
insert into env_calendar
values (null, :env_calendar_entries.oscp_review_Date,
:env_calendar_entries.facility,
:env_calendar_entries.oscp_review_desc,
:env_calendar_entries.oscp_review_Catg,
:env_calendar_entries.entry_id, NULL);
end if;
if :env_calendar_entries.frp_expire_date is not null
and :env_calendar_entries.frp_expire_catg is not null
and :env_calendar_entries.frp_expire_desc is not null then
insert into env_calendar
values (null, :env_calendar_entries.frp_expire_date,
:env_calendar_entries.facility,
:env_calendar_entries.frp_expire_desc,
:env_calendar_entries.frp_expire_catg,
:env_calendar_entries.entry_id, NULL);
end if;
if :env_calendar_entries.vrp_expire_date is not null
and :env_calendar_entries.vrp_expire_catg is not null
and :env_calendar_entries.vrp_expire_desc is not null then
insert into env_calendar
values (null, :env_calendar_entries.vrp_expire_date,
:env_calendar_entries.facility,
:env_calendar_entries.vrp_expire_desc,
:env_calendar_entries.vrp_expire_catg,
:env_calendar_entries.entry_id, NULL);
end if;
end;
And I have the same thing under the post-update trigger but in a different datablock called env_calendar
Upd-mod: Please PLEASE format your code and use 'code' tags.
[Updated on: Mon, 29 October 2007 22:13] by Moderator Report message to a moderator |
|
|
|
| Re: update and insert new records in a form [message #277394 is a reply to message #277301] |
Mon, 29 October 2007 22:16 |
 |
djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia
|
Senior Member
Account Moderator |
|
|
I suggest putting a 'message;pause;' pair into this code to prove that it is being executed.
You may need to add a 'standard.commit' at the end to make the 'insert's stick.
David
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
