Home » Developer & Programmer » Forms » update and insert new records in a form
update and insert new records in a form [message #276629] Thu, 25 October 2007 11:47 Go to next message
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 #276650 is a reply to message #276629] Thu, 25 October 2007 17:54 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
I'll skip the usual diatribe about 'read the sticky', 'use code tags', 'post table creation scripts not just some bits and pieces about the table'. However, we DO need to know WHICH triggers you ARE using. Especially the one that does the insert.

I suggest that you do inserts into your second table in the post-insert trigger of your main block, and do inserts or updates (I don't know which you need from the details you have provided) in the post-update trigger of the main block.

David
Re: update and insert new records in a form [message #276868 is a reply to message #276650] Fri, 26 October 2007 09:30 Go to previous messageGo to next message
deahayes3
Messages: 203
Registered: May 2006
Senior Member

Thanks so much for responding,
but I have a question about the post-update trigger. If I change a value in one field, How does it to know which field to update in the second block, how would I begin writing the procedure on the mainblock?
Re: update and insert new records in a form [message #277095 is a reply to message #276868] Sun, 28 October 2007 20:13 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
How about YOU post what you HAVE written and then we can use YOUR block and item names.

David
Re: update and insert new records in a form [message #277301 is a reply to message #277095] Mon, 29 October 2007 08:25 Go to previous messageGo to next message
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 Go to previous message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT 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
Previous Topic: disable a database trigger from the form
Next Topic: how to know which record or records are changed in a form
Goto Forum:
  


Current Time: Sun Dec 04 08:29:23 CST 2016

Total time taken to generate the page: 0.08388 seconds