Page 1 of 1

[CLOSED] TRIGGER BEFORE UPDATE affected by Macro to reset 'BOOLEAN'

Posted: Thu Sep 22, 2022 5:54 pm
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 1097 times
The error appears to be related to:

Code: Select all

 oActiveConnection = e.Source.Model.Parent.ActiveConnection
UPDATE 11/05/2022; the issue is not related to the afore mentioned macro, but to the TRIGGER BEFORE UPDATE PERSON_BU.
Therefore this topic is closed and a new topic is opened https://forum.openoffice.org/en/forum/v ... p?t=108812

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

Posted: Thu Sep 22, 2022 7:15 pm
by Villeroy
What is e?

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

Posted: Thu Sep 22, 2022 11:11 pm
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!

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

Posted: Fri Sep 23, 2022 3:14 am
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 "tAddress".

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

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

Posted: Fri Sep 23, 2022 4:54 am
by dreamquartz
UPDATE

Apparently this statement in DIRECT SQL does work.

Code: Select all

UPDATE "tPerson" SET "Check"=False WHERE "Check" =True
Dream