[Solved] How to verify against given Address Information

[dreamquartz]

Hi All,

I am looking for some guidance/thoughts/comments in relation to personal information provided by people.
I am struggling for a long time already with a situation that is plaguing our Client.

I am trying to figure out a solution to test against provided Addresses that are NOT the same in appearance, actually are the same in reality.

This is caused by people sometimes not using the name of the City but the name of the Neighbourhood where they live as the name of the “City” where they reside.
Example:
1. Peter Jannes (P.J.) Jonsen; 234 Albert Strasse Berlin
2. Peter Jannes (P.J.) Jonsen; 234 Albert Strasse Spandau
Now is Spandau a Neighbourhood of Berlin. Therefore the Address is the same.

Our Client came accorss the following as well:
One member of the family filled out the Address as under 1., while an other filled it out as under 2. They were both present at the same time.

It is so that our Client does not need to have any knowledge about Neighbourhoods/Cities.
Secondly our Client is not the one to tell their clients to write their Addresses in a format that is convenient to our Client.

Now is our philosophy that what can be automated, MUST be automated.
The DataBase must be able to verify, and signal the User.

Our quest is now to create a TRIGGER to check both ways, because it depends on which Address was entered first.
Either Check against 1. or against 2. or against both.
The 3rd Check is to determine if there are Duplicates already present in the DataBase.
This will only show up after a TRIGGER has been added to the database.

Problems I can imagine:
1. A Neighbourhood Name present in more than one City.
2. A Neighbourhood Address that is really different than the City Address, even when the Neighbourhood is part of the City. This could indicate a repeat of a StreetNumber-StreetName. I do not want to think about this last one for now, but it should be considdered in the future.

The approach I am thinking of (based on Employee-Supervisor type tables, where the Supervisor is also an Employee) is the use of a table called tCity, with the following fields:
CityID
City */holding Neighbourhood and City Names
Area1ID */holding reference to CityID
Area2ID */holding reference to CityID

The reason behind Area1ID and Area2ID is that a Neighbourhood name can be repeated in multiple Cities. I just limited it to 2. Theoretically this can be endless.
I really only want to use just one AreaID, but the Client came across just this particular situation. I do not know how to deal with that one other than adding a second field, because City is set to Unique.

Example:
tCity
CityID--City------------------------Area1ID---Area2ID
20......Berlin
78......Tempelhof-Schöneberg..20
81......Spandau...................20
83......Reinickendorf............20
84......Neukölln..................20

The table provides the possibility to determine if Spandau is part of Berlin.
It can also provide information about which Neighbourhoods are in a certain City.

Any help is highly appreciated,

Dream

12/08/2017: Opened it again, due to new insight.

[Nocton]

I know that German postcodes are not as 'efficient', i.e. do not get down to the same detail, as UK ones, but is there not an address checker/picker available from Deutsche Post or other provider that would help resolve your problem?

[Villeroy]

German ZIP codes: http://www.fa-technik.adfc.de/code/opengeodb/PLZ.tab (UTF-8 encoded) from opengeodb.org.

The German ZIP code (PLZ for Postleitzahl) together with SOUNDEX("Name") and/or SOUNDEX("Street") may help to detect duplicates.

HSQL2 also includes function DIFFERENCE(name1, name2) which returns a similarity index between 0 and 4.

DIFFERENCE ( <char value expr 1>, <char value expr 2> )
The arguments are character strings. Converts the arguments into SOUNDEX codes, and returns an INTEGER between 0-4 which indicates how similar the two SOUNDEX value are. If the values are the same, it returns 4, if the values have no similarity, it returns 0. In-between values are returned for partial similarity. (JDBC

[dreamquartz]

K,

I added the reference record AreaID, and Check to the table tCity, to confirm a relation between a Neighbourhood and a City. The "weight" of the City and the Neighbourhood is the same. I already dropped Area2ID, and added a TRIGGER for tCity.City, so the User can investigate when there is a Duplicate present. This is to deal with the situation where a Neighbourhood is split up between Cities.

I will investigate PostalCode, because in most cases, StreetNumber, StreetName, TypeOfStraat (i.e. 234 Albert Strasse) combined with a PostalCode should be sufficient/accurate enough.

Dream

[dreamquartz]

To get a TRIGGER working the way I want to, the Reference to the PostalCode (tAddress.FKPostalCode) needs to be move to tAddressBare.FKPostalCode.
This is lot of hard manual labor.

Is there a way to speed up the process of moving the Reference?

Dream

[dreamquartz]

It appears that the PostalCode approach will not work because of the design structure of the DataBase.

The storage of the Full "tAddress"."Address" is spread over 2 tables.
Table "tAddressBare" holds the information for the reason to add Notes to the location.
Our Client has a lot of corporate clients working in office towers in larger cities. The also have individual clients living in apartment complexes.
To provide our Client with the opportunity to store information, in the form of Notes, about for example where to park (this is information related to the building), the decision was made to split up the storage of the address.
The tAddressBare information, is the information about the location itself.
This information is complemented with information stored in table tAddress.

The PostalCodes in a office tower is not the same for our Client. It varies from unit to unit and floor to floor.

To be able to check on the StreetNumber, StreetName, TypeOfStreet, and PostalCode, means that information is to be stored in tAddressBare, before it can be made available to tAddress.
If there is a Duplication found, tAddressBare will hold information of no use. This will "poison" the tAddressBare table.
This is not a desired situation.

So therefore back to my initial request (See OP).
Dream.

[dreamquartz]

Hi All,

I have added our solution to the problems with the so called Bare Address.
The table tAddressBare can hold different types of addresses.
1. 1234 Apple Street, Pear City
2. 2345 Banana Avenue E., Peach Town
3. POBox 34567, Orange
4. 2345 Banana Avenue E. Pitt (where "Pitt" is a Neighbourhood of "Peach Town". This is considered an acceptable address, because people might write their address like that).

The code consists of 1 TRIGGER (instead of 4 in the previous version), to cover the above mentioned situations.

Hope it can be if use to someone.

Dream.

NOTE to UPDATE:

Code: Select all

"tCity"."CityID" IN(5,67,92,174,175,188,198,283)AND

is a hard coding for the different Neighbourhoods in a City. The User has to physically check and enter the Neighbourhoods and create a reference to the "tCity"."CityID" in the table itself.
It turned out to be the easiest and low risk solution.

10/15/2017: UPDATED Code

Code: Select all

CREATE TRIGGER PUBLIC.ADDRESS_CHECK_NEIGHBOURHOOD BEFORE INSERT ON PUBLIC."tAddressBare"
	 REFERENCING NEW ROW AS NEWROW FOR EACH ROW
	 BEGIN ATOMIC
		 IF EXISTS
			(
				SELECT
					1
				 FROM
					(
						SELECT
							NEWROW."StreetNumber" "StreetNumber",
							NEWROW."StreetName" "StreetName",
							"tTypeOfStreet"."TypeOfStreet" "TypeOfStreet",
							"Neighbourhood"."City" "Neighbourhood"
						 FROM
							PUBLIC."tAddressBare",
							PUBLIC."tTypeOfStreet",
							PUBLIC."tCity",
							PUBLIC."tCity" "Neighbourhood"
						 WHERE
							"tAddressBare"."FKTypeOfStreetID"="tTypeOfStreet"."TypeOfStreetID" AND
							"tAddressBare"."FKCityID"="Neighbourhood"."CityID" AND
							"Neighbourhood"."AreaID"="tCity"."CityID"
					)"NewNeighbourhood"
			 	INNER JOIN
					(
						SELECT
							"tAddressBare"."StreetNumber" "StreetNumber",
							"tAddressBare"."StreetName" "StreetName",
							"tTypeOfStreet"."TypeOfStreet" "TypeOfStreet",
							"Neighbourhood"."City" "Neighbourhood"
						 FROM
							PUBLIC."tAddressBare" "tAddressBare",
							PUBLIC."tTypeOfStreet" "tTypeOfStreet",
							PUBLIC."tCity",
							PUBLIC."tCity" "Neighbourhood"
						 WHERE
							"tAddressBare"."FKTypeOfStreetID"="tTypeOfStreet"."TypeOfStreetID" AND
							"tAddressBare"."FKCityID"="Neighbourhood"."CityID" AND
							"Neighbourhood"."AreaID"="tCity"."CityID" AND
							"tCity"."CityID" IN(5,67,92,174,175,188,198,283)AND
							"tAddressBare"."Check"=FALSE
					)"ExistingNeighbourhood" ON
					"NewNeighbourhood"."StreetNumber"="ExistingNeighbourhood"."StreetNumber" AND
					"NewNeighbourhood"."StreetName"="ExistingNeighbourhood"."StreetName" AND
					"NewNeighbourhood"."TypeOfStreet"="ExistingNeighbourhood"."TypeOfStreet" AND
					"NewNeighbourhood"."Neighbourhood"="ExistingNeighbourhood"."Neighbourhood"
			)
			THEN SIGNAL SQLSTATE'45000'SET MESSAGE_TEXT='The same Address already exists. Please verify the Exisitng entry!';
		END IF;
END

[eremmel]

Note on the above trigger code:
- You can learn how a trigger looks like in HSQLDB 2.4. This is not for the embedded database that is shipped with Openoffice.
- You can learn how you should not solve a check on existing during insert.

[dreamquartz]

10/15/2017; REMOVED comment

Dream

[dreamquartz]

Note on the above trigger code:
- You can learn how a trigger looks like in HSQLDB 2.4. This is not for the embedded database that is shipped with Openoffice.
- You can learn how you should not solve a check on existing during insert.

Please be advised that the solutions we provide here in the forum are based on your suggestions (See: viewtopic.php?f=13&t=86906#p409009).

We do appreciate your "constructive" feedback very much.
It helps us and others to re-address and re-focus on the actual issues, even if we think we have described the issues properly.

Dream

[eremmel]

With this progress, here some thoughts:
- tCity plays two roles: Neighbourhood (NH) and City. I guess that when 'AreaID IS NULL' or alike that the record is a City else a NH. So you do not need a hard coded lists of NHs, you can query this.
- In both inner-queries, you join with tCity in the role of City, but you do not use that information in the inner-join condition when you join both inner-queries.
- You join on "NewNeighbourhood"."Neighbourhood"="ExistingNeighbourhood"."Neighbourhood" which on the NH-name. This might give a false positive when two NHs exists in two different cities with the same name.
- If you have only one level of NH (a tCity record is only or a NH-of-a-city or a city) you miss in this query a street that is first registered in a NH and the next entry is at City-level: join on city level iso NH-level.
- Test the result of the first inner query by hand (just replace NEWROW.* with literals), and observe what you see. You copied the pattern, but you missed here something, making it expensive.

[dreamquartz]

With this progress, here some thoughts:
- tCity plays two roles: Neighbourhood (NH) and City. I guess that when 'AreaID IS NULL' or alike that the record is a City else a NH. So you do not need a hard coded lists of NHs, you can query this.
- In both inner-queries, you join with tCity in the role of City, but you do not use that information in the inner-join condition when you join both inner-queries.
- You join on "NewNeighbourhood"."Neighbourhood"="ExistingNeighbourhood"."Neighbourhood" which on the NH-name. This might give a false positive when two NHs exists in two different cities with the same name.
- If you have only one level of NH (a tCity record is only or a NH-of-a-city or a city) you miss in this query a street that is first registered in a NH and the next entry is at City-level: join on city level iso NH-level.
- Test the result of the first inner query by hand (just replace NEWROW.* with literals), and observe what you see. You copied the pattern, but you missed here something, making it expensive.

I am following the principle of Manager-Employee tables.
(See attached tCity and City.odb).

As far as my understanding goes, I need to one way or an other link a Neighbourhood to a City.
Looking at the table tCity, what I do is find the City, as a name, record the CityID in AreaID.

Referring to the OP;
An Address can be written with either the Neighbourhood, or the City, because that is what people want to do.
The reason behind this is highly likely annexation of the "old" City into the "new" City as a Neighbourhood.
You can say: "Old habits die hard".

As a result of this, the value in the DataBase for the Address information provided can be either Neighbourhood, or City.
Situations are known where one member of a family uses the Neighbourhood as City, while an other uses City as City when providing Address information.

EDIT: 10/16/2017;
After checking it turns out to be that the TRIGGER does what it needs to do.
Please realize that an Address Entry must always be checked against either, when there is a Neighbourhood identified, to determine if there is a Duplicate, created by either City or Neighbourhood.
It only has to find one instance at the time.
The data is checked manually. There is no import of data.

If there is, however, a way to speed up things, I'm all ears.....
So, I am a bit lost in regards to what you mean.
Can you provide an example?
Dream

[eremmel]

If you are satisfied with your trigger in respect to correctness, I've nothing to add. As a programmer you are able to test each combination of possibilities / events and validate the correctness.
In respect to performance: take a note of my last remark

Test the result of the first inner query by hand (just replace NEWROW.* with literals), and observe what you see. You copied the pattern, but you missed here something, making it expensive.

You will need to find the clue yourself.

[dreamquartz]

If you are satisfied with your trigger in respect to correctness, I've nothing to add. As a programmer you are able to test each combination of possibilities / events and validate the correctness.
In respect to performance: take a note of my last remark

Test the result of the first inner query by hand (just replace NEWROW.* with literals), and observe what you see. You copied the pattern, but you missed here something, making it expensive.

You will need to find the clue yourself.

Hi Eremmel,

Have been working with your comments for a while, and I think this works.
The TRIGGER checks against 4 conditions:
1. City-Neighbourhood
2. City-City
3. Neighbourhood-City
4. Neighbourhood-Neighbourhood

Testing shows that it works for me.

Code: Select all

CREATE TRIGGER PUBLIC.ADDRESS_CHECK_NEIGHBOURHOOD BEFORE INSERT ON PUBLIC."tAddressBare"
	 REFERENCING NEW ROW AS NEWROW FOR EACH ROW
	 BEGIN ATOMIC
		 IF EXISTS
			(
				 SELECT
					 1
				 FROM
					 (
						 SELECT
							 "tCity"."CityID" "CityID",
							 "tCity"."AreaID" "AreaID",
							 NEWROW."StreetNumber" "StreetNumber",
							 NEWROW."StreetName" "StreetName",
							 "tTypeOfStreet"."TypeOfStreet" "TypeOfStreet"
						 FROM
							 PUBLIC."tAddressBare",
							 PUBLIC."tTypeOfStreet",
							 PUBLIC."tCity"
						 WHERE
							 "tAddressBare"."FKTypeOfStreetID" = "tTypeOfStreet"."TypeOfStreetID" AND
							 "tAddressBare"."FKCityID" = "tCity"."CityID"
					 ) "NewNeighbourhood"
					 INNER JOIN
					 (
						 SELECT
							 "tCity"."CityID" "CityID",
							 "tCity"."AreaID" "AreaID",
							 "tAddressBare"."StreetNumber" "StreetNumber",
							 "tAddressBare"."StreetName" "StreetName",
							 "tTypeOfStreet"."TypeOfStreet" "TypeOfStreet"
						 FROM
							 PUBLIC."tAddressBare" "tAddressBare",
							 PUBLIC."tTypeOfStreet" "tTypeOfStreet",
							 PUBLIC."tCity"
						 WHERE							 
							 "tAddressBare"."FKTypeOfStreetID" = "tTypeOfStreet"."TypeOfStreetID" AND
							 "tAddressBare"."FKCityID" = "tCity"."CityID" AND
							 "tAddressBare"."Check" = FALSE
					 ) "ExistingNeighbourhood" ON
					 "NewNeighbourhood"."StreetNumber"="ExistingNeighbourhood"."StreetNumber" AND
					 "NewNeighbourhood"."StreetName"="ExistingNeighbourhood"."StreetName" AND
					 "NewNeighbourhood"."TypeOfStreet"="ExistingNeighbourhood"."TypeOfStreet" AND
					 (
						 "NewNeighbourhood"."CityID" = "ExistingNeighbourhood"."AreaID" OR
						 "NewNeighbourhood"."AreaID" = "ExistingNeighbourhood"."CityID" OR
						 "NewNeighbourhood"."CityID" = "ExistingNeighbourhood"."CityID" OR
						 "NewNeighbourhood"."AreaID" = "ExistingNeighbourhood"."AreaID"

					 )
			)
			 THEN SIGNAL SQLSTATE'45000'SET MESSAGE_TEXT='The same Address already exists. Please verify the Exisitng entry!';
		 END IF;
END

Dream

[eremmel]

Testing involves not only the positive events: trigger should blocks insert of 'duplicate' {streetnumber,streetname} ), but also negative events: trigger should not block same {streetnumber,streetname} in other city (or same/other neighborhood in different cities)

Good to see that you identified the unnecessity to join tCity as neighborhood for you can use AreaID for that!

[dreamquartz]

Testing involves not only the positive events: trigger should blocks insert of 'duplicate' {streetnumber,streetname} ), but also negative events: trigger should not block same {streetnumber,streetname} in other city (or same/other neighborhood in different cities)

Good to see that you identified the unnecessity to join tCity as neighborhood for you can use AreaID for that!

Thanks for your response eremmel, you have been very helpful.

It took me a while to figure out what was needed to test the following situations:
City vs City
City vs Neighbourhood
Neighbourhood vs City
Neighbourhood vs Neighbourhood

It allows to enter a new Address, even with existing StreetNumber, StreetName, TypeofStreet (i.e. Existing: 123 45 Strasse, Berlin and New: 123 45 Strasse, Köln).

I think I tested as much as I could, and it appears to be the right solution.

Code: Select all

CREATE TRIGGER PUBLIC.ADDRESS_CHECK_NEWADDRESS_EXISTINGADDRESS BEFORE INSERT ON PUBLIC."tAddressBare"
	 REFERENCING NEW ROW AS NEWROW FOR EACH ROW
	 BEGIN ATOMIC
		 IF EXISTS
			 (
				 SELECT
					 1
				 FROM
					 (
						 SELECT
							 NEWROW."StreetNumber" "StreetNumber",
							 NEWROW."StreetName" "StreetName",
							 NEWROW."FKTypeOfStreetID" "FKTypeOfStreetID",
							 NEWROW."FKCityID" "FKCityID"
						 FROM
							 "tAddressBare"
					 ) "NewAddress"
					 INNER JOIN
					(
						 SELECT
							 "tAddressBare"."StreetNumber" "StreetNumber",
							 "tAddressBare"."StreetName" "StreetName",
							 "tAddressBare"."FKTypeOfStreetID" "FKTypeOfStreetID",
							 "tAddressBare"."FKCityID" "FKCityID",
							 "tCity"."AreaID" "AreaID",
							 "tCity"."CityID" "CityID"
						 FROM
							 "tAddressBare",
							 "tCity"
						 WHERE
							 "tAddressBare"."FKCityID" = "tCity"."CityID" OR
							 "tAddressBare"."FKCityID" = "tCity"."AreaID"

	 				) "ExistingAddress" ON
					 "NewAddress"."StreetNumber" = "ExistingAddress"."StreetNumber" AND
					 "NewAddress"."StreetName" = "ExistingAddress"."StreetName" AND
					 "NewAddress"."FKTypeOfStreetID" = "ExistingAddress"."FKTypeOfStreetID" AND
					 (
						 "NewAddress"."FKCityID" = "ExistingAddress"."FKCityID" OR
						 (
							 "NewAddress"."FKCityID" = "ExistingAddress"."AreaID" AND
							 "ExistingAddress"."CityID" = "ExistingAddress"."FKCityID"
						 ) OR
						 (
							 "NewAddress"."FKCityID" = "ExistingAddress"."CityID" AND
							 "ExistingAddress"."AreaID" = "ExistingAddress"."FKCityID"
						 ) OR
						 (
							 "NewAddress"."FKCityID" = "ExistingAddress"."CityID" AND
							 "ExistingAddress"."AreaID" = "ExistingAddress"."FKCityID"
						 )
					 )
			)
			 THEN SIGNAL SQLSTATE'45000'SET MESSAGE_TEXT='The same Address already exists. Please verify the Existing entry!';
		 END IF;
END

Dream

[eremmel]

This looks much better. Still too complex, but the logic is there. There is one theoretical situation that you miss: You have an address in Neighborhood A of City C and you like to enter the same address in Neighborhood B of City C. This might be possible when there are (accidental) aliases or accidental (wrong address/Neighborhood) in the system.
Another thing is when you want to correct a address spelling, you need an update trigger as well.

The complexity comes from the fact that you are mentally bound to the trigger code you copied the structure from. That trigger needed the complexity, but not needed here. It results also in the performance penalty you observe. How many records do you think that the first inner select will return (the one with SELECT NEWROW... FROM AddressBare) ?

[dreamquartz]

This looks much better. Still too complex, but the logic is there. There is one theoretical situation that you miss: You have an address in Neighborhood A of City C and you like to enter the same address in Neighborhood B of City C. This might be possible when there are (accidental) aliases or accidental (wrong address/Neighborhood) in the system.
Another thing is when you want to correct a address spelling, you need an update trigger as well.

The complexity comes from the fact that you are mentally bound to the trigger code you copied the structure from. That trigger needed the complexity, but not needed here. It results also in the performance penalty you observe. How many records do you think that the first inner select will return (the one with SELECT NEWROW... FROM AddressBare) ?

Question:
Can a TRIGGER cover more than one table?
In my experiments, I only dealt with "tAddressBare" so far in the NEWROW situation, but have not been able to find a way to add "tCity".

Code: Select all

CREATE TRIGGER PUBLIC.ADDRESS_CHECK_NEWADDRESS_EXISTINGADDRESS BEFORE INSERT ON PUBLIC."tAddressBare"

.

Dream

[eremmel]

Triggers are table oriented, so your NEWROW record will only contain fields that are related to the corresponding table. In the trigger you can update/insert data in other tables as well, but you have no access to tCity data. In a Base form based on tAddressBase you connect to tCity as a ListBox or a sub-form.
When you access tAddressBare/tCity in one shot as the result of a view, you might use 'INSTEAD OF INSERT' trigger, but are you sure that you want to tenfold complexity of your triggers.

[dreamquartz]

Thanks eremmel.

Because of the design for the Address in the DataBase;
tAddressBare
AddressBareID
FKTypeOfStreetID
FKCityID
StreetNumber
StreetName

tAddress
AddressID
FKAddressBareID
FKPersonID
FKFKPostalCodeID
FKTypeOfAddressID
FKUnitNumberID

it is necessary to re-select the AddressBare for creating a complete Address, so it can be linked to a Person, PostalCode and UnitNumber.
The AddressBare is therefore checked by the User again, because it needs to be selected.
We do not expect any issues with existing addresses.

Decided to split up the TRIGGER into 2.
1. TRIGGER for City-City, Neighbourhood-City, and City-Neighbourhood

Code: Select all

CREATE TRIGGER PUBLIC.ADDRESS_CHECK_CITY_AREA_CITY BEFORE INSERT ON PUBLIC."tAddressBare" 
REFERENCING NEW ROW AS NEWROW FOR EACH ROW BEGIN ATOMIC IF EXISTS
	 (
		 SELECT 1
		 FROM
			 (
				 SELECT
					 NEWROW."StreetNumber" "StreetNumber",
					 NEWROW."StreetName" "StreetName",
					 NEWROW."FKTypeOfStreetID" "FKTypeOfStreetID",
					 NEWROW."FKCityID" "FKCityID"
				 FROM
					 PUBLIC."tAddressBare"
			 ) "NewAddress"
				 INNER JOIN
			 (
				 SELECT
					 "tAddressBare"."StreetNumber" "StreetNumber",
					 "tAddressBare"."StreetName" "StreetName",
					 "tAddressBare"."FKTypeOfStreetID" "FKTypeOfStreetID",
					 "tAddressBare"."FKCityID" "FKCityID",
					 "tCity"."AreaID" "AreaID",
					 "tCity"."CityID" "CityID"
				 FROM
					 PUBLIC."tAddressBare",
					 PUBLIC."tCity"
				 WHERE
					 "tAddressBare"."FKCityID"="tCity"."CityID" OR
					 "tAddressBare"."FKCityID"="tCity"."AreaID"
			 ) "ExistingAddress"
				 ON
					 "NewAddress"."StreetNumber"="ExistingAddress"."StreetNumber" AND
					 "NewAddress"."StreetName"="ExistingAddress"."StreetName" AND
					 "NewAddress"."FKTypeOfStreetID"="ExistingAddress"."FKTypeOfStreetID" AND
					 (
						 "NewAddress"."FKCityID"="ExistingAddress"."FKCityID" OR
						 (
							 "NewAddress"."FKCityID"="ExistingAddress"."AreaID" AND
							 "ExistingAddress"."CityID"="ExistingAddress"."FKCityID"
						 ) OR
						 (
							 "NewAddress"."FKCityID"="ExistingAddress"."CityID" AND
							 "ExistingAddress"."AreaID"="ExistingAddress"."FKCityID"
						 )
					 )
	 )
		 THEN SIGNAL SQLSTATE'45000'SET MESSAGE_TEXT='The same Address already exists. Please verify the Existing entry!';
	 END IF;
END

2. Neighbourhood-Neighbourhood

Code: Select all

CREATE TRIGGER PUBLIC.ADDRESS_CHECK_AREA_AREA BEFORE INSERT ON PUBLIC."tAddressBare"
REFERENCING NEW ROW AS NEWROW FOR EACH ROW BEGIN ATOMIC IF EXISTS
	 (
		 SELECT 1
		 FROM
			(
				 SELECT
					 NEWROW."StreetNumber" "StreetNumber",
					 NEWROW."StreetName" "StreetName",
					 NEWROW."FKTypeOfStreetID" "FKTypeOfStreetID",
					 NEWROW."FKCityID" "FKCityID"
				 FROM
					 PUBLIC."tAddressBare"
			 ) "NewAddress"
				 INNER JOIN
			 (
				SELECT
					 "tAddressBare"."StreetNumber" "StreetNumber",
					 "tAddressBare"."StreetName" "StreetName",
					 "tAddressBare"."FKTypeOfStreetID" "FKTypeOfStreetID",
					 "tAddressBare"."FKCityID" "FKCityID",
					 "tCity"."AreaID" "AreaID",
					 "tCityB"."AreaID" "AreaBID",
					 "tCity"."CityID" "CityID",
					 "tCityB"."CityID" "CityBID"
				 FROM
					 PUBLIC."tAddressBare",
					 PUBLIC."tCity",
					 PUBLIC."tCity" "tCityB"
				 WHERE
					 "tCityB"."AreaID"="tCity"."AreaID" AND
					 "tCity"."CityID"="tAddressBare"."FKCityID"
			 ) "ExistingAddress"
				 ON
					 "NewAddress"."StreetNumber"="ExistingAddress"."StreetNumber" AND
					 "NewAddress"."StreetName"="ExistingAddress"."StreetName" AND
					 "NewAddress"."FKTypeOfStreetID"="ExistingAddress"."FKTypeOfStreetID" AND
					 "NewAddress"."FKCityID"="ExistingAddress"."CityBID"
	 )
		 THEN SIGNAL SQLSTATE'45000'SET MESSAGE_TEXT='The same Address already exists. Please verify the Existing entry!';
	 END IF;
END

It appears all is taken care of and consider it to be solved.

Dream

[eremmel]

It is amost Christmas, time for a gift

Code: Select all

select 1
from PUBLIC."tAddressBare"
inner join  PUBLIC."tCity" "CityOrNeighbourhood"
	on "tAddressBare"."FKCityID" = "CityOrNeighbourhood"."CityID"
left join PUBLIC."tCity" "Neighbourhoods"
	on ( "Neighbourhoods"."AreaID" = "CityOrNeighbourhood"."CityID"
		or "Neighbourhoods"."AreaID" = "CityOrNeighbourhood"."AreaID" )
where
	"tAddressBare"."StreetNumber" =  NEW."StreetNumber"
	and "tAddressBare"."StreetName" =  NEW."StreetName"
	and "tAddressBare"."FKTypeOfStreetID" =  NEW."FKTypeOfStreetID"
	and ( "CityOrNeighbourhood"."CityID" = NEW."FKCityID"
		or "Neighbourhoods"."CityID" =  NEW."FKCityID"
		or "Neighbourhoods"."AreaID" =  NEW."FKCityID"
	)	

[dreamquartz]

It is amost Christmas, time for a gift

Code: Select all

select 1
from PUBLIC."tAddressBare"
inner join  PUBLIC."tCity" "CityOrNeighbourhood"
	on "tAddressBare"."FKCityID" = "CityOrNeighbourhood"."CityID"
left join PUBLIC."tCity" "Neighbourhoods"
	on ( "Neighbourhoods"."AreaID" = "CityOrNeighbourhood"."CityID"
		or "Neighbourhoods"."AreaID" = "CityOrNeighbourhood"."AreaID" )
where
	"tAddressBare"."StreetNumber" =  NEW."StreetNumber"
	and "tAddressBare"."StreetName" =  NEW."StreetName"
	and "tAddressBare"."FKTypeOfStreetID" =  NEW."FKTypeOfStreetID"
	and ( "CityOrNeighbourhood"."CityID" = NEW."FKCityID"
		or "Neighbourhoods"."CityID" =  NEW."FKCityID"
		or "Neighbourhoods"."AreaID" =  NEW."FKCityID"
	)	

Wow,
That is a VERY BIG present.
Thank you very much.

Your solution is very elegant and very simple.

Your approach is completely different. It makes perfect sense.

I was always under the impression that NEWROW has to be selected, and that is why my TRIGGER designs are that way.
Apparently that is not the case.
This solution will speed up the response time significantly for the TRIGGER if the DataBase is growing. At this point in time there are around 10,000 Address records, and my solutions are still quite acceptable.

I am still in the testing phase, but it appears to be working great.

MERRY CHRISTMAS and HAPPY NEW YEAR to you too.

Dream

[eremmel]

Impressions ends with lock-in understanding concepts brings you the world.

[dreamquartz]

Impressions ends with lock-in understanding concepts brings you the world.

I concur.

We always conceptualize potential solutions, but that does not always work.

We have been searching a long time for good information about TRIGGERS, and to be honest, we still haven't found it .
During our discussions in the group, we are not trying to invent the wheel at all, and many so called 'solutions' for issues are presented and discussed.
Sometimes we think we have it, but then presenting it to the QA department (this forum), will sometimes hit us in the face big time.
We all do appreciate that very much, because this is how we all learn.

The group has a ton of knowledge, but this area is very under represented.

Again thanks for this this solution.

Dream

[eremmel]

Just study triggers of another database e.g. like MS SQL Server or Postgress. Writing triggers is like writing stored procedures so a lot of angles to start learning and experimenting.