I am still working on a problem that is related to the large amount of data being entered in tables, and searching/viewing the content.
As a result, I am looking for a method to single out one record, in an 'ocean' of records, that already exists.
The main reason is: prevent duplication.
For ease of use, Views have created via Queries of certain tables.
An example:
Table tAddress has the following records:
AddressID
FKAddressBareID
FKPersonID
FKCompanyID
FKPostalCodeID
FKTypeOfAddressID
UnitNumber
Floor
This information is difficult to read, so a Query creates a View of tAddress to show a readable Address, like:
"Dreamquartz [# A 1234 Brenner Strasse 2nd Floor, Munich Germany 67E45T (Residence)]".
The Query we use, is always recreating the view of the table tAddress, every time a new Address is entered.
This is taking more and more time.
What we want to create is a situation where:
1. the information is presented to the DataBase, checked, based on a TRIGGER, and if exists, identified by AddressID, or added, if not present
2. only create a view of that information, which is either present, or new
for selection purposes in a form called fAddress.
For testing purposes I have created the following:
1.
Code: Select all
CREATE TABLE "tMail"
(
"MailID" BIGINT PRIMARY KEY,
"FKTypeOfEmailPhoneFaxID" INT,
"FKPersonID" BIGINT,
"FKEmployeeID" BIGINT,
"Mail" VARCHAR(100),
"Check" BOOLEAN
)
Code: Select all
CREATE VIEW "vMail" AS SELECT * FROM "tMail"
Code: Select all
CREATE TRIGGER "triggerMail"
INSTEAD OF INSERT ON "vMail"
REFERENCING NEW ROW AS NEWROW FOR EACH ROW
MERGE INTO "tMail"
USING
(
VALUES
(
DEFAULT,
NEWROW."FKTypeOfEmailPhoneFaxID",
NEWROW."FKPersonID",
NEWROW."FKEmployeeID",
NEWROW."Mail",
FALSE
)
) AS vals
(
"MailID",
"FKTypeOfEmailPhoneFaxID",
"FKPersonID",
"FKEmployeeID",
"Mail",
"Check"
) ON "tMail"."Mail" = vals."Mail"
WHEN MATCHED
THEN UPDATE SET "tMail"."Check" = TRUE
WHEN NOT MATCHED
THEN INSERT VALUES
vals."MailID",
vals."FKTypeOfEmailPhoneFaxID",
vals."FKPersonID",
vals."FKEmployeeID",
vals."Mail",
vals."Check"
I am stuck at the point that I can only seem to use 'DIRECT SQL' for data entry to make use of this TRIGGER.
I need to make a function like what i described above available in a form format for ease of use for our Client.
Can anyone give me some directions in how to make it possible to enter the data via a form and still utilize the 'MERGE INTO' TRIGGER?
I think it has to do with the syntax of the TRIGGER itself, because it uses 'VALUES' and not really the information presented for 'NEWROW'
Thanks in advance,
Dream