TRIGGER BEFORE UPDATE affected by Macro to reset 'BOOLEAN'

Discuss the database features
Post Reply
dreamquartz
Posts: 862
Joined: Mon May 30, 2011 4:02 am

TRIGGER BEFORE UPDATE affected by Macro to reset 'BOOLEAN'

Post by dreamquartz »

oActiveConnection = e.Source.Model.Parent.ActiveConnectionreference: https://forum.openoffice.org/en/forum/v ... 44#p415798

Hi All,

A macro the User is running is throwing an error, and we seem not understand why.

Code: Select all

Sub Main(e)
Const cResetPersonCheck = "UPDATE ""tPerson"" SET ""Check""=False"
Const cResetPhoneFaxCheck = "UPDATE ""tPhoneFax"" SET ""Check""=False"
Const cResetEmailAddressCheck = "UPDATE ""tEmailAddress"" SET ""Check""=False"
Const cResetAddressBareCheck = "UPDATE ""tAddressBare"" SET ""Check""=False"
  oActiveConnection = e.Source.Model.Parent.ActiveConnection
  oPrepPersonCheck = oActiveConnection.prepareStatement(cResetPersonCheck)
n = oPrepPersonCheck.executeUpdate()
  oPrepPhoneFaxCheck = oActiveConnection.prepareStatement(cResetPhoneFaxCheck)
n = oPrepPhoneFaxCheck.executeUpdate()
  oPrepEmailAddressCheck = oActiveConnection.prepareStatement(cResetEmailAddressCheck)
n = oPrepEmailAddressCheck.executeUpdate()
 oPrepAddressBareCheck = oActiveConnection.prepareStatement(cResetAddressBareCheck)
n = oPrepAddressBareCheck.executeUpdate()
NextRecord 'Call Sub NextRecord
End Sub

Sub NextRecord
Dim oDocument As Object
Dim oDispatcher As Object
oDocument   = ThisComponent.CurrentController.Frame
oDispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
oDispatcher.executeDispatch(oDocument, ".uno:NewRecord", "", 0, Array())
End sub
Please see the attached printscreens as well.

Any suggestions, ideas are much appreciated.

Dream
Error Macro Remove all %22Check%22 when running directly.png
Error Macro Remove all %22Check%22 when running directly.png (7.51 KiB) Viewed 179 times
The error appears to be related to:

Code: Select all

 oActiveConnection = e.Source.Model.Parent.ActiveConnection
Last edited by dreamquartz on Fri Sep 23, 2022 3:25 am, edited 2 times in total.
LO 7.x, HSQLDB 2.6.x & Ubuntu 20.04 LTS.
User avatar
Villeroy
Volunteer
Posts: 30801
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Updating all tables by removing a User set 'Boolean'

Post by Villeroy »

What is e?
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
karolus
Volunteer
Posts: 977
Joined: Sat Jul 02, 2011 9:47 am

Re: Updating all tables by removing a User set 'Boolean'

Post by karolus »

Villeroy wrote: Thu Sep 22, 2022 7:15 pmWhat is e?
@Villeroy: copy and paste from your https://forum.openoffice.org/en/forum/v ... 47#p415847 ??


@dreamquartz: you cannot run a makro directly in the IDE which is designed to triggered from something like click_on _button!
AOO4, Libreoffice 6.1 on Rasbian OS (on ARM)
dreamquartz
Posts: 862
Joined: Mon May 30, 2011 4:02 am

Re: TRIGGER BEFORE UPDATE affected by Macro to reset 'BOOLEAN'

Post by dreamquartz »

karolus wrote: Thu Sep 22, 2022 11:11 pm
Villeroy wrote: Thu Sep 22, 2022 7:15 pmWhat is e?
@Villeroy: copy and paste from your https://forum.openoffice.org/en/forum/v ... 47#p415847 ??


@dreamquartz: you cannot run a makro directly in the IDE which is designed to triggered from something like click_on _button!
@karolus & @Villeroy,

It appears to be related to a TRIGGER we added a while back.
The TRIGGER is helping the User to make sure that the correct Person is updated.

Due to the fact that the User is dealing with a lot of people with the same GivenName-Surname, and/or potential other combinations (see TRIGGER), the User wanted to make sure that the 'correct' Person is updated.
The simplest solution was to add a type of TRIGGER BEFORE UPDATE (see below).
Apparently this TRIGGER 'interferes' with the said Macro.

Removing this TRIGGER and all is 'well' concerning running the Macro.
However, the Macro is used to remove any "Check" which was set by the User in the first place, because the issues, raised by any TRIGGER for "Check" in the tables "tPerson", "tEmailAddress", "tAddressBare", and "tAddressBare".

By setting "Check" to 'TRUE', the User is able to insert and/or update any of the records in either of the aforementioned tables.
In a sense, setting "Check" to 'TRUE' the table related TRIGGER(s) are now 'fooled' and will not fire if an identical record is to be inserted (think PhoneNumber for people living in the same house) or a record is to be updated (think typo in DOB).

However, leaving the "Check" = 'TRUE' in place will potentially cause 'issues' later on, when a record is updated 'again' that has "Check" = 'TRUE' still.
Therefore, after the User has updated a record, the User can push a button to run the Macro, and so remove all "Checks" that were set in the past.
One of the steps we ask the User to take is to push the button for removing the set "Checks" once a while, just like making them aware that 'checkpoint defrag' and 'backups' are important. A lot of the forms the User has access to have macro programmed buttons for the last 2.

The principle works great for the tables "tEmailAddress", "tAddressBare", and "tAddressBare".
There are TRIGGERS 'monitoring' all of the aforementioned tables, but they are of the type TRIGGER BEFORE INSERT.
The only table that is also 'monitored' by a TRIGGER BEFORE UPDATE is "tPerson".

Code: Select all

CREATE TRIGGER PUBLIC.PERSON_BU BEFORE UPDATE ON PUBLIC."tPerson"
	 REFERENCING NEW ROW AS NEWROW FOR EACH ROW
	 BEGIN ATOMIC
		 IF EXISTS
			 (
				 SELECT
					 "tPerson"."Surname",
					 "tPerson"."GivenName",
					 "tPerson"."Nickname",
					 "tPerson"."MiddleInitial(s)",
					 "tPerson"."DateOfBirth"
				 FROM
					 PUBLIC."tPerson"
				 WHERE
					 (
						 (
							 "tPerson"."Surname" = NEWROW."Surname" AND
							 "tPerson"."DateOfBirth" = NEWROW."DateOfBirth"
						 ) OR
						 (
							 "tPerson"."Nickname" = NEWROW."Nickname" AND
							 "tPerson"."DateOfBirth" = NEWROW."DateOfBirth"
						 ) OR
						 (
							 "tPerson"."GivenName" = NEWROW."GivenName" AND
							 "tPerson"."DateOfBirth" = NEWROW."DateOfBirth"
						 )
					 ) AND
					 "tPerson"."Check" = NEWROW."Check"
			 )
			 THEN SIGNAL SQLSTATE'45000'SET MESSAGE_TEXT = 'There appears to be a similar Person already recorded. Please verify the Existing entry!';
		 END IF;
 END
Just for clarification I also added the TRGGER BEFORE INSERT for table "tPerson".

Code: Select all

CREATE TRIGGER PUBLIC.PERSON_BI BEFORE INSERT ON PUBLIC."tPerson"
	 REFERENCING NEW ROW AS NEWROW FOR EACH ROW
	 BEGIN ATOMIC
		 IF EXISTS
			 (
				 SELECT
					 "tPerson"."Surname",
					 "tPerson"."GivenName",
					 "tPerson"."Nickname",
					 "tPerson"."MiddleInitial(s)",
					 "tPerson"."DateOfBirth"
				 FROM
					 PUBLIC."tPerson"
				 WHERE
					 (
						 "tPerson"."Surname"=NEWROW."Surname" AND
						 (
							 "tPerson"."DateOfBirth"=NEWROW."DateOfBirth" OR
							 "tPerson"."GivenName"=NEWROW."GivenName" OR
							 "tPerson"."GivenName"=NEWROW."Nickname" OR
							 "tPerson"."Nickname"=NEWROW."GivenName" OR
							 "tPerson"."Nickname"=NEWROW."Nickname" OR
							 "tPerson"."MiddleInitial(s)"=NEWROW."MiddleInitial(s)"
						 ) AND
						 "tPerson"."Check" = FALSE
					 )
			 )
			 THEN SIGNAL SQLSTATE'45000'SET MESSAGE_TEXT='There appears to be a similar Person already recorded. Please verify the Existing entry!';
		END IF;
END
I do not know how to solve this issue for the table "tPerson", where both new records and updating existing records MUST be monitored.

In summary, this is the real issue that was triggered by a Macro.

Therefore the title is updated.

Dream
LO 7.x, HSQLDB 2.6.x & Ubuntu 20.04 LTS.
dreamquartz
Posts: 862
Joined: Mon May 30, 2011 4:02 am

Re: TRIGGER BEFORE UPDATE affected by Macro to reset 'BOOLEAN'

Post by dreamquartz »

UPDATE

Apparently this statement in DIRECT SQL does work.

Code: Select all

UPDATE "tPerson" SET "Check"=False WHERE "Check" =True
Dream
LO 7.x, HSQLDB 2.6.x & Ubuntu 20.04 LTS.
Post Reply