OraFAQ Forum: Forms » Avioding inserts when recors already exists [merged 2]

Home » Developer & Programmer » Forms » Avioding inserts when recors already exists [merged 2]

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Avioding inserts when recors already exists [merged 2] [message #641517]

Mon, 17 August 2015 07:18

ORA2015
Messages: 49
Registered: March 2015

Member

Hello,

I need help with a piece of code that will prevent the insert of a new record when a record already exists in that block. So...for example when the insert record button is pressed the user will get a response that a record already exists.
What type of trigger will need to be created and how will the code look like?

Report message to a moderator

Re: Avioding inserts when recors already exists [message #641518 is a reply to message #641517]

Mon, 17 August 2015 07:25

cookiemonster
Messages: 13975
Registered: September 2008
Location: Rainy Manchester

Senior Member

Is this a forms question?

Report message to a moderator

Re: Avioding inserts when recors already exists [message #641520 is a reply to message #641517]

Mon, 17 August 2015 09:00

ORA2015
Messages: 49
Registered: March 2015

Member

Its pertaining to forms. But any idea of the code?

Report message to a moderator

Re: Prevent Inserts when data already exists [message #641522 is a reply to message #641517]

Mon, 17 August 2015 09:09

cookiemonster
Messages: 13975
Registered: September 2008
Location: Rainy Manchester

Senior Member

Then you should have posted in the forms forum and not created a duplicate topic (I've merged them).

Assuming there's a primary/unique key on the table forms will prevent inserts without you having to do anything.
If you want the users to see a different error message then you can trap the error in the on-error trigger and use the message builtin to display a message you want them to see.
Alternatively you could write a select in pre-insert to check for duplicate records and if you find one display a message and raise form_trigger_failure to stop the insert.

Report message to a moderator

Re: Prevent Inserts when data already exists [message #641523 is a reply to message #641517]

Mon, 17 August 2015 09:10

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

ORA2015 wrote on Mon, 17 August 2015 07:01

Hello,

I need help with a piece of code that will prevent the insert of a new record when a record already exists in that block. So...for example when the insert record button is pressed the user will get a response that a record already exists.
What type of trigger will need to be created and how will the code look like?

Trigger is NOT needed.
just make Primary Key for this table.

What specifically determines that "record already exists"?

Report message to a moderator

Re: Prevent Inserts when data already exists [message #641524 is a reply to message #641523]

Mon, 17 August 2015 09:26

CraigB
Messages: 386
Registered: August 2014
Location: Utah, USA

Senior Member

If you are unable to alter your table to add a Primary or Unique Key, then you can check for a duplicate in either the When-Validate-Record, When-Validate-Item or On-Insert triggers. Your code will basically SELECT a Count(*) of records from your table where the Key Value equals the Key Value in your Form. If the COUNT(*) is greater than Zero (0) then you have a duplicate and you can halt processing and display a message to your user.

Craig...

Report message to a moderator

Re: Prevent Inserts when data already exists [message #641525 is a reply to message #641523]

Mon, 17 August 2015 09:28

cookiemonster
Messages: 13975
Registered: September 2008
Location: Rainy Manchester

Senior Member

BlackSwan wrote on Mon, 17 August 2015 15:10

ORA2015 wrote on Mon, 17 August 2015 07:01
Hello,

I need help with a piece of code that will prevent the insert of a new record when a record already exists in that block. So...for example when the insert record button is pressed the user will get a response that a record already exists.
What type of trigger will need to be created and how will the code look like?

Trigger is NOT needed.
just make Primary Key for this table.

What specifically determines that "record already exists"?

We're talking forms triggers here not DB ones.

Report message to a moderator

Re: Prevent Inserts when data already exists [message #641527 is a reply to message #641525]

Mon, 17 August 2015 09:34

CraigB
Messages: 386
Registered: August 2014
Location: Utah, USA

Senior Member

Yes, the three triggers I mentioned, WVR, WVI, and OI, are all Forms event triggers.

Report message to a moderator

Re: Prevent Inserts when data already exists [message #641528 is a reply to message #641524]

Mon, 17 August 2015 09:43

ORA2015
Messages: 49
Registered: March 2015

Member

CraigB wrote on Mon, 17 August 2015 09:26

If you are unable to alter your table to add a Primary or Unique Key, then you can check for a duplicate in either the When-Validate-Record, When-Validate-Item or On-Insert triggers. Your code will basically SELECT a Count(*) of records from your table where the Key Value equals the Key Value in your Form. If the COUNT(*) is greater than Zero (0) then you have a duplicate and you can halt processing and display a message to your user.

Craig...

Yes i have that on the when validate items. Its handled on the forms but how do i halt the processing? I do not want the user when pressed the add new record button that the fields become blank for a new record. I want the message to happen as soon as they try to.

Report message to a moderator

Re: Prevent Inserts when data already exists [message #641530 is a reply to message #641528]

Mon, 17 August 2015 09:48

cookiemonster
Messages: 13975
Registered: September 2008
Location: Rainy Manchester

Senior Member

Why are you trying to stop the user pressing add new record?
You can't know it's a duplicate before they've entered any values.

Report message to a moderator

Re: Prevent Inserts when data already exists [message #641532 is a reply to message #641530]

Mon, 17 August 2015 10:04

ORA2015
Messages: 49
Registered: March 2015

Member

well actually the case is for a master detail. So when they are seeing master information, although its handled at the db level, would like to handle it at the forms level.

Report message to a moderator

Re: Prevent Inserts when data already exists [message #641533 is a reply to message #641530]

Mon, 17 August 2015 10:10

CraigB
Messages: 386
Registered: August 2014
Location: Utah, USA

Senior Member

Quote:

Yes i have that on the when validate items. Its handled on the forms but how do i halt the processing? I do not want the user when pressed the add new record button that the fields become blank for a new record. I want the message to happen as soon as they try to.

Unless you are querying all of the records in the table into your Form, the only way to check to see if there is a duplicate is to Query the database. The most immediate way to do this is in the When-Validate-Item (WVI) trigger on the Key Value column in your Form.

Craig...

Report message to a moderator

Re: Prevent Inserts when data already exists [message #641539 is a reply to message #641533]

Mon, 17 August 2015 10:55

ORA2015
Messages: 49
Registered: March 2015

Member

I'm little confused. I tried but its still allows data entry in the field. Didn't want that

Report message to a moderator

Re: Prevent Inserts when data already exists [message #641582 is a reply to message #641539]

Tue, 18 August 2015 08:22

CraigB
Messages: 386
Registered: August 2014
Location: Utah, USA

Senior Member

ORA2015 wrote on Mon, 17 August 2015 09:55

I'm little confused. I tried but its still allows data entry in the field. Didn't want that

What did you try??? You can't expect us to be able to help you if you don't specifically state what you tried and what the outcome (error, etc) was!

Also, you need to explain in more detail what exactly you are trying to accomplish. So far, you have given us very little details. For example, you state:
Quote:

But any idea of the code?

What "Code" are you talking about? You haven't listed any code... You have to remember, that we can't see your Form, we don't know your situation so you have to give us very detailed explanations of what you need if you want help...

Craig...

Report message to a moderator

Re: Avioding inserts when recors already exists [merged 2] [message #642793 is a reply to message #641517]

Sat, 19 September 2015 07:01

dude4084
Messages: 222
Registered: March 2005
Location: Mux

Senior Member

http://sheikyerbouti.developpez.com/duplicates/duplicates.htm

http://sheikyerbouti.developpez.com/duplicates/duplicates.htm

Kindly visit above two links to get the idea about the solution of your problem

Report message to a moderator

Previous Topic:	HI
Next Topic:	FORMS AND REPORTS 11G

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Apr 26 20:48:08 CDT 2026