Home » Developer & Programmer » Forms » Insert/Update/Delete single record at a time
Insert/Update/Delete single record at a time [message #132084] Wed, 10 August 2005 14:59 Go to next message
vojinle
Messages: 6
Registered: June 2005
Junior Member
Hi,
I have multirecord block and I want to disable Inserting/Updating/Deleting more then one record at a time.
Any ideas?
Re: Insert/Update/Delete single record at a time [message #132109 is a reply to message #132084] Wed, 10 August 2005 19:21 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Write Insert/Update/Delete triggers to perform their action and then set the block property to 'off', then in the 'commit' trigger, do the commit and then turn the property back 'on'.

By the way, why do you want to do this? Oracle only handles one record at a time anyway.

David
Re: Insert/Update/Delete single record at a time [message #132423 is a reply to message #132109] Fri, 12 August 2005 12:26 Go to previous messageGo to next message
vojinle
Messages: 6
Registered: June 2005
Junior Member
To be more specific:
I need to not allow user to navigate from current record if it was changed and not commited, or if it was inserted and not commited.

Thanks
Re: Insert/Update/Delete single record at a time [message #132457 is a reply to message #132084] Fri, 12 August 2005 18:24 Go to previous messageGo to next message
m_ashtiani
Messages: 27
Registered: August 2005
Location: Reno
Junior Member
you have to trap up,down, next_record, previous_record triggers
if you have tab pages or next/previous block then you have to trap them too. p.s. you may want to trap mouse click if you don't want them to change blocks either


simple example (block level triggers)
Key-down trigger:
execute_trigger('key-nxtRec');


Key-nxtRec trigger:
if get_block_propert('bolck_name', status ) in ('QUERY') then
next_record;
end if;

also beware of 'NEW' status
i am using my memory so i may have syntax errors above.

hope it helps
Re: Insert/Update/Delete single record at a time [message #132515 is a reply to message #132084] Sat, 13 August 2005 14:57 Go to previous messageGo to next message
oraclejo
Messages: 50
Registered: July 2005
Location: Ammar
Member
I would suggest another technique

Oracle maintain Record and block status in SYSTEM vairables.
:SYSTEM.RECORD_STATUS can have the following values:

CHANGED, QUERY, NEW

QUERY MEANS that the RECORD is either already committed or has been queried but never changed. While trying to move out of the record , you can check the value of this SYSTEM vairable.

Another approach would be relying on the fact that the trigger when-validate-record fires if you navigate out of a record when changes are made to the current record. So if it fires, it means that there are changes to be committed, note that you have to handle the fact that when-validate-record also fires when a commit is performed

Ammar Sajdi
www.e-ammar.com

Regs
Re: Insert/Update/Delete single record at a time [message #132566 is a reply to message #132515] Sun, 14 August 2005 19:56 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Another approach is ... in the WVI trigger call a procedure which turns all the items 'OFF' and then turns, just this record's items, back 'ON'. Remember you can't 'enable' the current field, but you also couldn't disable it so test for the item instance status and if it is enabled don't re-enable it.

Of course, in the commit-form trigger you turn them all back 'ON' again.

David

[Updated on: Mon, 15 August 2005 01:33]

Report message to a moderator

Re: Insert/Update/Delete single record at a time [message #132593 is a reply to message #132566] Mon, 15 August 2005 01:30 Go to previous messageGo to next message
oraclejo
Messages: 50
Registered: July 2005
Location: Ammar
Member
Hi

I do not see how truning the item off, would prevent the commit operation from taking place. The question is how to stop navigation outside the record when there are changes to be committed for that particular record. And one record at a time.

Regards

Ammar Sajdi
www.e-ammar.com
Re: Insert/Update/Delete single record at a time [message #132594 is a reply to message #132593] Mon, 15 August 2005 01:36 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
If an item is 'off' you can't navigate into it. Therefore, the user can't get out of the current record. 'OFF' means keyboard and mouse navigation disabled, insert, update, delete disabled. If you are still working in character mode you can disabled the field. Unfortunately, in GUI if you disable the field it decides 'greyed' out. This may or may not be okay.

This means that as soon as the user changes any field, only that record can be modified until the other fields are reactivated as part of the commit process.

I didn't think 'vojinle' wanted to stop the commit, 'vojinle' only wanted the user to modify a single record at a time.

David

[Updated on: Mon, 15 August 2005 01:39]

Report message to a moderator

Re: Insert/Update/Delete single record at a time [message #132597 is a reply to message #132457] Mon, 15 August 2005 01:55 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
I was going to suggest that you could put a block level 'key-others' trigger and check the system.record_status. If it is 'QUERY' then you do a do_key(system.trigger_name) but there doesn't appear to be a system.trigger_name.

Is there any way to find which trigger was fired most recently?

David
Re: Insert/Update/Delete single record at a time [message #132612 is a reply to message #132597] Mon, 15 August 2005 03:18 Go to previous messageGo to next message
oraclejo
Messages: 50
Registered: July 2005
Location: Ammar
Member
Hello

I still do not thinnk that this would solve the problem, the most recently fired trigger could be non-key trigger like for example when-timer-expired.

Ammar Sajdi
Re: Insert/Update/Delete single record at a time [message #132614 is a reply to message #132612] Mon, 15 August 2005 03:21 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Yea ... this is probably why there is no system.trigger_name.

Just turn off all the fields that aren't in this record and stick with that.

David
Re: Insert/Update/Delete single record at a time [message #133393 is a reply to message #132614] Thu, 18 August 2005 18:04 Go to previous messageGo to next message
vojinle
Messages: 6
Registered: June 2005
Junior Member
How to turn off all other fields?
Re: Insert/Update/Delete single record at a time [message #133399 is a reply to message #132593] Thu, 18 August 2005 20:55 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
oraclejo ... my apologies .. the navigation thing does not work ... I thought that I had done it in another place but Ihave been doing some testing and the navigation will not turn off.

David
Re: Insert/Update/Delete single record at a time [message #133400 is a reply to message #133393] Thu, 18 August 2005 20:57 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
vojinle ... my apologies .. the navigation thing does not work ... I thought that I had done it in another place but I have been doing some testing and the navigation will not turn off.

You will have to trap the up down previous next triggers, plus handle the mouse navigation.

David
icon12.gif  Re: Insert/Update/Delete single record at a time [message #134762 is a reply to message #132084] Fri, 26 August 2005 13:34 Go to previous messageGo to next message
vojinle
Messages: 6
Registered: June 2005
Junior Member
Resolved with global variable
in Post_Record and When_New_Rec_Instance

Thanks
Re: Insert/Update/Delete single record at a time [message #134898 is a reply to message #134762] Sun, 28 August 2005 18:39 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
What commands did you put in the When_New_Record_Instance trigger?

David
Re: Insert/Update/Delete single record at a time [message #135067 is a reply to message #134898] Mon, 29 August 2005 14:11 Go to previous messageGo to next message
vojinle
Messages: 6
Registered: June 2005
Junior Member
IF NAME_IN('GLOBAL.OUTLOOK')='Y' THEN
GO_RECORD(NAME_IN('GLOBAL.RECORD_NUMBER'));
MESSAGE('You cannot update more than one record if the Outlook box is checked');
RAISE FORM_TRIGGER_FAILURE;
END IF;

IF NAME_IN('SYSTEM.RECORD_STATUS') IN ('CHANGED','INSERT', 'NEW') THEN
COPY(NAME_IN('SYSTEM.CURSOR_RECORD'),'GLOBAL.RECORD_NUMBER');
END IF;
Re: Insert/Update/Delete single record at a time [message #324587 is a reply to message #135067] Tue, 03 June 2008 04:18 Go to previous messageGo to next message
javed.khan
Messages: 340
Registered: November 2006
Location: Banglore
Senior Member

Thanx vojinle

Helps me alott.


Javed
Re: Insert/Update/Delete single record at a time [message #324588 is a reply to message #324587] Tue, 03 June 2008 04:20 Go to previous messageGo to next message
javed.khan
Messages: 340
Registered: November 2006
Location: Banglore
Senior Member

My Requirement was goes like that
If the Records are Approved User shud not be able to Update it.
else he can make the Changes.I write on form level When-New-Record_Instance Trigger
IF 		:PARAMETER.P_AUTHORIZE = 'N' THEN
IF 		:GM_VEHICLE_JOBS.AUTHORIZE_DATE IS NULL THEN
			SET_ITEM_PROPERTY('GM_VEHICLE_JOBS.MS_JOB_TYPE', UPDATE_ALLOWED, PROPERTY_TRUE);
RAISE FORM_TRIGGER_FAILURE;
END IF;
IF 		:GM_VEHICLE_JOBS.AUTHORIZE_DATE IS NOT NULL THEN
			SET_ITEM_PROPERTY('GM_VEHICLE_JOBS.MS_JOB_TYPE', UPDATE_ALLOWED, PROPERTY_FALSE);
RAISE FORM_TRIGGER_FAILURE;
END IF;
ELSE
			SET_ITEM_PROPERTY('GM_VEHICLE_JOBS.MS_JOB_TYPE', UPDATE_ALLOWED, PROPERTY_FALSE);	
END IF;




Thanx and Regards


Javed A. Khan
Re: Insert/Update/Delete single record at a time [message #410803 is a reply to message #135067] Tue, 30 June 2009 06:10 Go to previous messageGo to next message
adnan19cn
Messages: 7
Registered: June 2009
Location: Karachi
Junior Member


Hi

Please help me, i am also stuck with this requirement.
I also want to restrict insertion/deletion/updation if a record changed or inserted. I want thing like "save changes first".

urgent reply shall be highly appreciated.


Thanks,

Adnan..
Re: Insert/Update/Delete single record at a time [message #410882 is a reply to message #410803] Tue, 30 June 2009 21:12 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
In 'when_new_record_instance' test the current block status. If it is not 'QUERY' then do a 'commit_form'.

David
Re: Insert/Update/Delete single record at a time [message #410886 is a reply to message #410882] Tue, 30 June 2009 22:28 Go to previous messageGo to next message
adnan19cn
Messages: 7
Registered: June 2009
Location: Karachi
Junior Member


Thanks Martin

But I cann't commit by the form. User wants to save by his own.

Any suggestion. I just want to not navigate the cursor from a record if it is update/inserted and not saved.

Thanks.

Adnan..
Re: Insert/Update/Delete single record at a time [message #410931 is a reply to message #410886] Wed, 01 July 2009 01:28 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Then raise an alert and ask them if they want to save.

David
Re: Insert/Update/Delete single record at a time [message #410938 is a reply to message #410931] Wed, 01 July 2009 01:44 Go to previous messageGo to next message
adnan19cn
Messages: 7
Registered: June 2009
Location: Karachi
Junior Member


Hi David,

I am using this on When_Validate_Record at Block Level;

IF :system.Record_Status In ('CHANGED', 'INSERT') Then
MESSAGE('Save Changes First');
MESSAGE('Save Changes First');
RAISE FORM_TRIGGER_FAILURE;
END IF;

It works fine but do not saves when I press save button on my form. Any suggestion?

I cant have it on When_new_form_instance because i want to check for both New or Updated record. I just need a trigger name or any other suggestion that could get me rid from this.


Regards,

Adnan..
Re: Insert/Update/Delete single record at a time [message #410952 is a reply to message #132084] Wed, 01 July 2009 04:22 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
Since when-validate-record has to complete without error before the record can be saved (which is the whole point of that trigger) that was never going to work.

There is no nice way of doing this in a multi-record block, but as djmartin suggested the best bet is to use a when-new-record-instance.
Re: Insert/Update/Delete single record at a time [message #410953 is a reply to message #410952] Wed, 01 July 2009 04:27 Go to previous messageGo to next message
adnan19cn
Messages: 7
Registered: June 2009
Location: Karachi
Junior Member


you may see above Vijoloin did with the combination form Post-Record and New-Record-Instance.


Thanks,

Adnan..
Re: Insert/Update/Delete single record at a time [message #410957 is a reply to message #410953] Wed, 01 July 2009 05:12 Go to previous messageGo to next message
adnan19cn
Messages: 7
Registered: June 2009
Location: Karachi
Junior Member

Well I have done the following but its on item level validation not on record level. When I use this on record level validation then if come up with a error 'Unable to Insert' and allows to navigate on other record without saving.

At Form Level:
1)
When-New-Forms-Instance
:global.ina:=0;

2)
Key-Commit
:global.ina:=1;
commit_form;
:global.ina:=0;

At Block Level:
1)
IF :SYSTEM.RECORD_STATUS IN ('CHANGED','INSERT') and :global.ina=0 THEN
RAISE FORM_TRIGGER_FAILURE;
END IF;

May someone found this helpful. But I am still waiting for help on my scenario.


Thanks,

Adnan..
Re: Insert/Update/Delete single record at a time [message #410991 is a reply to message #132084] Wed, 01 July 2009 07:41 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
Try this in when-new-record-instance:


IF :system.block_status = 'CHANGED' THEN

  IF show_alert('Do you want to save changes?') = ALERT_BUTTON1 THEN

    commit_form;

  ELSE

    --user said no
    go_record(:global.current_record);
    raise form_trigger_failure;
  END IF;

END IF;

:global.current_record := :system.cursor_record;

Re: Insert/Update/Delete single record at a time [message #410995 is a reply to message #410991] Wed, 01 July 2009 08:01 Go to previous messageGo to next message
adnan19cn
Messages: 7
Registered: June 2009
Location: Karachi
Junior Member


and what about when someone updates a record and don't saves? Will it restrict that?


Thanks,


Adnan..
Re: Insert/Update/Delete single record at a time [message #411015 is a reply to message #132084] Wed, 01 July 2009 08:34 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
block status goes to changed when anyone inserts, updates or deletes a record. so why don't you just try it.
Re: Insert/Update/Delete single record at a time [message #411028 is a reply to message #410995] Wed, 01 July 2009 09:41 Go to previous messageGo to next message
adnan19cn
Messages: 7
Registered: June 2009
Location: Karachi
Junior Member


this is working on only when i create new record. is there any event like 'Lost Focus' in VB????

I am really tensed causing this problem.


Thanks,

Adnan..
Re: Insert/Update/Delete single record at a time [message #443261 is a reply to message #411028] Sun, 14 February 2010 07:48 Go to previous messageGo to next message
WildStrik
Messages: 3
Registered: February 2010
Location: Egypt,Alexandria
Junior Member

Hello All,

Is there any way to know what is that changes that happen to change the record current status to 'CHANGED'.

I'm asking that because in my form, every time i leave a rec. it asks me if i wanna "Saving the Changes" although i didn't change any thing !!!.

Thanks in advance.
Re: Insert/Update/Delete single record at a time [message #446929 is a reply to message #443261] Thu, 11 March 2010 00:34 Go to previous message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Sorry that I have been so long in replying. Have you solved your problem?

You THINK that you haven't changed anything! Use the debugger and run through the form line-by-line. Somewhere in the code there is an assignment to a database item.

David
Previous Topic: Mail with OLE2 (using static mail address for Sender)
Next Topic: create database in veitname
Goto Forum:
  


Current Time: Sun Dec 11 08:10:13 CST 2016

Total time taken to generate the page: 0.21817 seconds