[Solved] Preventing Duplicate Record - use of SQL TRIGGER

Creating tables and queries

[Solved] Preventing Duplicate Record - use of SQL TRIGGER

Postby Nocton » Tue Mar 18, 2014 4:30 pm

I have a table with fields:
ID
MemberID
GroupID

The table records the groups to which each member belongs. I want to prevent duplicate entries, i.e. records which have the same MemberID and GroupID. Is there a convenient way to do this in the table or a query, or do I have to use a form procedure to check before updating the record?

Regards

Nocton
Last edited by Nocton on Thu Nov 27, 2014 2:20 pm, edited 3 times in total.
OpenOffice 4.2.0 on Windows 10
Nocton
Volunteer
 
Posts: 512
Joined: Fri Nov 05, 2010 10:27 am
Location: UK

Re: Preventing Duplicate Record

Postby Villeroy » Tue Mar 18, 2014 5:03 pm

Open the table for editing.
Tools>Index Design...
Add a new index, give it a name, mark it as unique and add the 2 fields.
Save the index.

This does the same as Tools>SQL...
Code: Select all   Expand viewCollapse view
CREATE UNIQUE INDEX "UniqueGroupMembers" ON "Table_Name" ("MemberID","GroupID");


Indices accellerate all lookups on the fields involved. Unique indices enforce uniqueness. Duplicates won't be stored.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28365
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Preventing Duplicate Record

Postby Nocton » Tue Mar 18, 2014 9:24 pm

Thank you, Villeroy. That is just what I was looking for. Elegant and simple!

Regards,
Nocton
OpenOffice 4.2.0 on Windows 10
Nocton
Volunteer
 
Posts: 512
Joined: Fri Nov 05, 2010 10:27 am
Location: UK

Re: [Solved] Preventing Duplicate Record

Postby Nocton » Thu Apr 03, 2014 9:33 am

Further to this, is there any way to trap the SQL error that occurs if the user tries to enter a duplicate record so that the user receives a simple, helpful, message? E.g a TRY - CATCH type option?
OpenOffice 4.2.0 on Windows 10
Nocton
Volunteer
 
Posts: 512
Joined: Fri Nov 05, 2010 10:27 am
Location: UK

Re: [Solved] Preventing Duplicate Record

Postby Villeroy » Thu Apr 03, 2014 10:12 am

I don't know. Last time I tried the script event "Error occured", these errors where not catchable. I find the standard messages descriptive enough.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28365
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Preventing Duplicate Record

Postby papijo » Mon Nov 10, 2014 5:59 pm

Villeroy wrote:I don't know. Last time I tried the script event "Error occured", these errors where not catchable. I find the standard messages descriptive enough.

I beg to disagree. I am not finding that "Violation of unique constraint SYS_PK_51 etc. etc." error message very user-friendly.

Here my scenario for a very simple test.
1.- Create one "Colors" table with only one field of Text[VARCHAR] type, named "color", set it to Primary Key.
2.- Create a "Colors " Form based on that table.
3.- Edit the form. In the MainForm->Properties-> Events -> Error occurred -> Assigned Action, link to a "DuplicateEntryError" macro that you previously created.
4.-
Code: Select all   Expand viewCollapse view
Sub DuplicateEntryError
    MsgBox "Sorry, you cannot have more than one color with the same name! "
End Sub

5.- Enter data in the "Colors" Table: red, blue, etc.
6.- Enter a duplicate value e.g. "red".
a. If you use the tab key after entering that duplicate value, my customized message is displayed.
b. If you click any of the Navigation buttons, my customized message is displayed and, when dismissed, then the standard "Violation of unique constraint SYS_PK_51 etc. etc." error message is displayed.

The behaviour I would like is to simply display the customized error message in my macro, NOT the standard error message. Any suggestion?
LO: LibreOffice 6.4.0.3 (x64) on Windows 10 64bits. Split database HSQL 2.3.4.
papijo
 
Posts: 88
Joined: Sat Nov 08, 2014 5:46 pm
Location: Brittany, West of France

Re: [Solved] Preventing Duplicate Record

Postby Villeroy » Mon Nov 10, 2014 6:36 pm

This is what we all want. But nobody found out how to catch any errors in a macro language. I don't even know any list of possible errors. Some years ago I tried the form*s script event and it was deaf. I don't recall if I tried http://www.openoffice.org/api/docs/comm ... aster.html
And now I see that there is a http://www.openoffice.org/api/docs/comm ... ialog.html
Go ahead, check this out and don't forget to tell us about the results (if any).
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28365
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Preventing Duplicate Record

Postby Sliderule » Mon Nov 10, 2014 6:47 pm

Just as an FYI . . . yes, a user CAN elect to supply their own user-friendly error message(s) . . . IF . . . using HSQL as an EXTERNAL database, NOT the internal Embedded Database ( HSQL Version 1.8.1.10 ) as the date I am writing this.

HSQL Version 2.3 . . . and later . . . has the ability . . . at the database level ( NOT internal to OpenOffice / LibreOffice Base ) and issue warning/error messages using INSERT and/or UPDATE TRIGGERs. The reason this is superior ( in my opinion ) is . . . that means, whether managing your database from within Base Forms, OR, using any other External database front-end, or, even when adding/updating tables within Base, from the EDIT table window, you will get the same error message(s) :super: .

Please see the links below for examples:

  1. https://forum.openoffice.org/en/forum/viewtopic.php?f=13&t=70701&p=316678
  2. https://forum.openoffice.org/en/forum/viewtopic.php?f=13&t=60703&p=269445
  3. https://forum.openoffice.org/en/forum/viewtopic.php?f=39&t=70023&p=312867
NOTE: you can use this forum, and, search on the keyword: CREATE TRIGGER SQLSTATE to find additional postings. :)

Sliderule
User avatar
Sliderule
Volunteer
 
Posts: 1239
Joined: Thu Nov 29, 2007 9:46 am

Re: [Solved] Preventing Duplicate Record

Postby papijo » Tue Nov 25, 2014 7:49 pm

Hi Sliderule,
I have read http://hsqldb.org/doc/guide/triggers-chapt.html but when I try to create a trigger using the example below I get an error: 1: user lacks privilege or object not found: NEW.LASTNAME
I am using a split database with latest HSQLDB version 2.3.2.
I have a test db with only one table named CUSTOMER and one field LASTNAME, indexed as unique.
Here is the sql command I try to execute in Tools->SQL:
Code: Select all   Expand viewCollapse view
/* the trigger throws an exception if a customer with the given last name already exists */
CREATE TRIGGER trigone BEFORE INSERT ON customer
   REFERENCING NEW ROW AS newrow
   FOR EACH ROW WHEN (newrow.id > 100)
   BEGIN ATOMIC
     IF EXISTS (SELECT * FROM CUSTOMER WHERE CUSTOMER.LASTNAME = NEW.LASTNAME) THEN
       SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'already exists';
     END IF;
   END
LO: LibreOffice 6.4.0.3 (x64) on Windows 10 64bits. Split database HSQL 2.3.4.
papijo
 
Posts: 88
Joined: Sat Nov 08, 2014 5:46 pm
Location: Brittany, West of France

Re: [Solved] Preventing Duplicate Record

Postby Sliderule » Tue Nov 25, 2014 8:28 pm

Please try the code below ( From the Menu: Tools -> SQL... ) :

Code: Select all   Expand viewCollapse view
/* the trigger throws an exception if a customer with the given last name already exists */
CREATE TRIGGER trigone BEFORE INSERT ON customer
   REFERENCING NEW ROW AS NEWROW
   FOR EACH ROW WHEN (NEWROW.ID > 100)
   BEGIN ATOMIC
     IF EXISTS (SELECT * FROM CUSTOMER WHERE CUSTOMER.LASTNAME = NEWROW.LASTNAME) THEN
       SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'already exists';
     END IF;
   END

Explanation: The difference . . . since you are going to REFERENCE the ROW you want to INSERT, as, NEWROW ( per the statement: REFERENCING NEW ROW ROW AS NEWROW . . . in your SELECT statement - yes, I know, HSQL documentation error ) . . . it should be qualified from the contents you want to insert as: NEWROW.LASTNAME . And, just to be certain . . . since . . . I assume from the way you wrote it . . . your table name and column names are all UPPER CASE . . . ( which in my opinion/experience is perfect ) . . . you will NOT have to surround the statement table/column names with quotes. :)

Note 1: I know you did not ask, but, just as an FYI, I would suggest a TRIGGER name, that is more descriptive. For example, in this instance . . . TRIGONE_BEFORE_INSERT . . . the reason . . . the name that includes the optional description . . . BEFORE_INSERT might make it easier in defining/maintaining the database.

Note 2: Just as another FYI, once you get the syntax correct ( yes, you are almost there ) . . . you can have it, the code, check for various ( more than one ) errors, and, have an error message return all ( as you defined them ) errors, rather than just one error at a time. :)

Note 3: Again, I want to congratulate you on reading the HSQL documentation . . . much appreciated. :bravo:

I hope this helps, please be sure to let me / us know.

Sliderule
User avatar
Sliderule
Volunteer
 
Posts: 1239
Joined: Thu Nov 29, 2007 9:46 am

Re: [Solved] Preventing Duplicate Record

Postby papijo » Wed Nov 26, 2014 7:13 pm

@Sliderule,
Thanks for your detailed answer. I am--slowly--beginning to understand the TRIGGER feature. The following SQL command works in the AOO or LO Tools->SQL... window. Just what I needed!
Code: Select all   Expand viewCollapse view
CREATE TRIGGER TRIGONE_BEFORE_INSERT
BEFORE INSERT
   ON CUSTOMER
   REFERENCING NEW ROW AS NEWROW
   FOR EACH ROW
   BEGIN ATOMIC
     IF EXISTS (SELECT * FROM CUSTOMER WHERE CUSTOMER.LASTNAME = NEWROW.LASTNAME) THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'This customer name already exists, please enter a different name!';
     END IF;
   END;

1.- You are right, for this test I have used UPPERCASE only for my table and fields names.
2.- That is why I find it strange that the above SQL command works in line 3 with "ON customer" as well as "ON CUSTOMER"!
3.- I do not understand what "WHEN (NEWROW.ID > 100)" stands for in the original code on line 7. My trigger works without it.
4.- Now that my application uses a split HSQL DB, from time to time I use SQLWorkbench64 or SQuirreL SQL Client to view/edit my DB. It is much more user-friendly than issuing SQL queries/commands from the AOO/LO interface. So I tried to issue that same CREATE TRIGGER command (see above) in the SQL windows of those front-ends. But I got the error:
unexpected end of statement: required: ; : line: 7 [SQL State=42590, DB Errorcode=-5590]
So it's telling me that line 7 should end with a (required) semicolon ";" when it facts that line does end with a semi-colon! This weird bug is reported in a few places on the Web, but I have not found a convincing solution. Any idea? See e.g. http://stackoverflow.com/questions/1150 ... b-expected
5.- Since I need the TRIGGER to issue a customized error message for both INSERT and UPDATE operations in my DB, I thought it would be nice to have a single TRIGGER working for both. Something like
Code: Select all   Expand viewCollapse view
BEFORE INSERT||UPDATE
(where || would mean OR, as in PHP syntax). Obviously this is not possible and I have to have 2 separate TRIGGERS. Fine.
6.- The MySQL Workbench doc. says
Object Editor - Easily edit, create or delete database objects (tables, views, triggers, etc.)
but it seems impossible to edit or add triggers from that front-end. Has anyone managed to do it?
7.- When I have a trigger (or 2 triggers) installed on my CUSTOMER table, adding/editing a field with a duplicate entry does trigger the TRIGGER as expected if I click on the "Save record" button (in a form). However, if, after entering a duplicate entry I use the TAB key, nothing happens. Is there a way to trap that TAB key in the TRIGGER?

That's all for the moment. ;)
LO: LibreOffice 6.4.0.3 (x64) on Windows 10 64bits. Split database HSQL 2.3.4.
papijo
 
Posts: 88
Joined: Sat Nov 08, 2014 5:46 pm
Location: Brittany, West of France

Re: [Solved] Preventing Duplicate Record

Postby Sliderule » Wed Nov 26, 2014 7:50 pm

papijo wrote: That is why I find it strange that the above SQL command works in line 3 with "ON customer" as well as "ON CUSTOMER"!

Yes, please keep in mind, that, if a TABLE, VIEW, or, COLUMN NAME is NOT surrounded by double quotes, it ( database back-end, HSQL ) will follow the SQL standard, and, change them to all UPPER CASE. I just prefer to enter the 'code' . . . TABLE, VIEW, COLUMN names the same way it is defined ( UPPER CASE ) . . . but . . . YMMV . . . Your Mileage/Database May Vary. :super:

papijo wrote:I do not understand what "WHEN (NEWROW.ID > 100)" stands for in the original code on line 7. My trigger works without it.

Just an additional capability . . . of HSQL . . . that is it ( database back-end ) would ONLY process the TRIGGER, when the condition is met . . . truly . . . just an example of showing some capabilities. :bravo:

papijo wrote:Now that my application uses a split HSQL DB, from time to time I use SQLWorkbench64 or SQuirreL SQL Client to view/edit my DB. It is much more user-friendly than issuing SQL queries/commands from the AOO/LO interface. So I tried to issue that same CREATE TRIGGER command (see above) in the SQL windows of those front-ends. But I got the error:
unexpected end of statement: required: ; : line: 7 [SQL State=42590, DB Errorcode=-5590]
So it's telling me that line 7 should end with a (required) semicolon ";" when it facts that line does end with a semi-colon! This weird bug is reported in a few places on the Web, but I have not found a convincing solution. Any idea? See e.g. http://stackoverflow.com/questions/1150 ... b-expected

Yes, those database back-end tools . . . use the standard of a semi-colon to indicate the end of each SQL/DDL ( Structured Query Language / Data Definition Language ) statement. But, since, part of trigger . . . includes some semi-colons . . . it ( for example Squirrel, needs to know how to process the statement to send the FULL statement as a command.

Therefore, there is an option . . . to set an ALTERNATE termination delimiter for a command . . . check the OPTIONS of your database front-end ( SQuirrel , or, SQL Workbench/J etc ) . . . most use a divide sign . . . / . . . and include that special character as the LAST command in the CREATE TRIGGER statement ( because it includes semi-colons in the middle of the statement ) . :super:

papijo wrote:Since I need the TRIGGER to issue a customized error message for both INSERT and UPDATE operations in my DB, I thought it would be nice to have a single TRIGGER working for both.

Yes, I understand what you are saying, but, no, even though you may want the same code to be issued, for INSERT and UPDATE operations, you still need to write/execute the TRIGGER separately. Remember, a TRIGGER is also available for a DELETE command . . . so . . . it ( the code ) must be written for each separate operation, INSERT vs UPDATE vs DELETE . :D

papijo wrote:When I have a trigger (or 2 triggers) installed on my CUSTOMER table, adding/editing a field with a duplicate entry does trigger the TRIGGER as expected if I click on the "Save record" button (in a form). However, if, after entering a duplicate entry I use the TAB key, nothing happens. Is there a way to trap that TAB key in the TRIGGER?

Not to my knowledge, BUT, perhaps someone else on this forum can help you with this.

I think ( warning, Sliderule thinking or attempting to think, may be hazardous to your health ) . . . we should terminate the posts here on the topic . . . since . . . they really have nothing to do with the original topic. What I mean is . . . perhaps you can start a NEW forum topic, if you like.

Also, if you send me a PM ( Private Message ) . . . if you want, perhaps we can TALK privately . . . and I can help you privately to show you some tips, tricks using HSQL SqlTool.jar, HSQL Database Manager ( you already have these tools installed on your computer 8-) ) , tricks and options for your HSQL 2.3.2 database etc.

I would also like to say, how impressed I am with the work you have accomplished, reading the manuals, and, asking good questions. Keep up the good work. :super:

Sliderule
User avatar
Sliderule
Volunteer
 
Posts: 1239
Joined: Thu Nov 29, 2007 9:46 am

Re: [Solved] Preventing Duplicate Record

Postby papijo » Wed Nov 26, 2014 11:38 pm

@Sliderule,
Many thanks again for taking the time to answer all of my questions in detail, much appreciated. :D
Yes, I agree that this is now taking a direction away from the OP's question, so I will open a new discussion for any further questions in this matter. ;)
papijo
LO: LibreOffice 6.4.0.3 (x64) on Windows 10 64bits. Split database HSQL 2.3.4.
papijo
 
Posts: 88
Joined: Sat Nov 08, 2014 5:46 pm
Location: Brittany, West of France

Re: [Solved] Preventing Duplicate Record

Postby Nocton » Thu Nov 27, 2014 2:19 pm

Yes, I agree that this is now taking a direction away from the OP's question

That's true, but you've developed a good thread on the use of TRIGGER, so I have added that to the header to help future searches on this topic.

Regards,

Nocton (OP)
OpenOffice 4.2.0 on Windows 10
Nocton
Volunteer
 
Posts: 512
Joined: Fri Nov 05, 2010 10:27 am
Location: UK

Re: [Solved] Preventing Duplicate Record

Postby papijo » Thu Nov 27, 2014 3:52 pm

Nocton wrote:
Yes, I agree that this is now taking a direction away from the OP's question

That's true, but you've developed a good thread on the use of TRIGGER, so I have added that to the header to help future searches on this topic.
Regards,
Nocton (OP)

Good idea, thanks, Nocton.
LO: LibreOffice 6.4.0.3 (x64) on Windows 10 64bits. Split database HSQL 2.3.4.
papijo
 
Posts: 88
Joined: Sat Nov 08, 2014 5:46 pm
Location: Brittany, West of France

Re: [Solved] Preventing Duplicate Record - use of SQL TRIGGE

Postby papijo » Mon Dec 01, 2014 7:49 pm

Just noticed what looks like a bug in this "preventing duplicate record" matter.

If a table field is indexed as unique, when entering a duplicate entry into a form, clicking in the "standard" navigation toolbar at the bottom of the form window on either the Next record or Save record button, we get an error message as expected. OK.

Now, I have tried adding a Navigation bar control to my form (and also tested with a Push button control with action set to "Save record"). After entering a duplicate entry into a form, clicking on the Save record button in that added Navigation bar control object OR on the Push button, nothing happens.

It looks as if those actions are not "analyzed" by the software in the same way as clicking on the default navigation toolbar. That's a pity. Can anyone confirm my finding and possibly provide a workaround or post a bug report?

Or maybe this is linked to my earlier observation that tabbing away from a duplicate entry to another field does not trigger any error?
See https://forum.openoffice.org/en/forum/v ... 34#p333097
point #7 ... Is there a way to trap that TAB key in the TRIGGER?
LO: LibreOffice 6.4.0.3 (x64) on Windows 10 64bits. Split database HSQL 2.3.4.
papijo
 
Posts: 88
Joined: Sat Nov 08, 2014 5:46 pm
Location: Brittany, West of France

Re: [Solved] Preventing Duplicate Record - use of SQL TRIGGE

Postby papijo » Mon Dec 01, 2014 8:22 pm

Ah, just got a solution. There's nothing like explaining your problem on a forum to make you think and--sometimes--find the solution yourself. One of those "rubber-duck moments" I guess. :idea:

Create a macro
Code: Select all   Expand viewCollapse view
Sub RecSave
Dim document   as Object
Dim dispatcher as Object
  document   = ThisComponent.CurrentController.Frame
  dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
  dispatcher.executeDispatch(document, ".uno:RecSave", "", 0, Array())
End Sub


For that table field which is indexed as unique, in the form, Events->When losing focus, put a link to that RecSave macro. Immediately upon tabbing away from a field after entering a duplicate entry you get the error message (which you can modify by using a TRIGGER). :super:

EDIT.- You can try linking the RecSave macro to other events of the "no duplicates" field, according to desired result.
LO: LibreOffice 6.4.0.3 (x64) on Windows 10 64bits. Split database HSQL 2.3.4.
papijo
 
Posts: 88
Joined: Sat Nov 08, 2014 5:46 pm
Location: Brittany, West of France


Return to Tables & Queries

Who is online

Users browsing this forum: No registered users and 3 guests