Input form using a view of a table

Discuss the database features

Input form using a view of a table

Postby dreamquartz » Sun Sep 30, 2018 4:11 am

Hi All,

Looking for information on how to create and use a form where the source is a view of a table.
The view is created via: 'CREATE VIEW "vTable" AS SELECT * FROM "tTable"'.
According to the Guide for 2.4.1, pg. 52, Views, a view of a table can be updatable, if created via i.e. the above statement.
A view that is not updatable according to the above paragraph can be made updatable or insertable-into by adding INSTEAD OF triggers to the view.

Using a TRIGGER
Code: Select all   Expand viewCollapse view
CREATE TRIGGER "triggerMail"
    INSTEAD OF INSERT ON "viewMail"
       REFERENCING NEW AS newrow FOR EACH ROW
          MERGE INTO "tMail"
          USING
             (
                VALUES
                   (
                      IDENTITY(),
                      newrow."FKTypeOfEmailPhoneFaxID",
                      newrow."FKPersonID",
                      newrow."FKEmployeeID",
                      newrow."Mail",
                      FALSE
                   )
             ) AS vals
                (
                   u,
                   v,
                   w,
                   x,
                   y,
                   z
                ) ON "tMail"."Mail" = vals.y
    WHEN MATCHED
       THEN UPDATE SET "tMail"."Check" = TRUE
    WHEN NOT MATCHED
       THEN INSERT VALUES
             vals.u,
             vals.v,
             vals.w,
             vals.x,
             vals.y,
             vals.z

Should therefore give me the option to create a form, using "viewMail" as the source.
However, the form created from "viewMail", via the standard route, is not updatable.

I know I am missing something, but I do not know what.

Any suggestions,

Dream
LO 5.x and OO 4.x on Windows 7 PRO & Ubuntu 16.04 LTS.
dreamquartz
 
Posts: 642
Joined: Mon May 30, 2011 4:02 am

Re: Input form using a view of a table

Postby eremmel » Mon Oct 01, 2018 11:45 pm

You miss the primary key. No PK on a table/view no insert/delete/update possible via Base.
Is your question answered: edit your initial post add "[Solved]" to the subject and check V
LO4.2.8.2 for real life with ORB; AOO4.1.3,LO5.2.5.1 on W7E SP1 64bit
It's Microsoft marketing that tells you computers are qualified for non-technicians
User avatar
eremmel
Volunteer
 
Posts: 1006
Joined: Tue Dec 30, 2008 1:15 am
Location: Barneveld, Netherlands

Re: Input form using a view of a table

Postby dreamquartz » Fri Oct 05, 2018 4:03 am

eremmel wrote:You miss the primary key. No PK on a table/view no insert/delete/update possible via Base.

The underlying table"tMail" is created as follows:
Code: Select all   Expand viewCollapse view
CREATE TABLE  "tMail"(
"MailID" BIGINT PRIMARY KEY,
"FKTypeOfEmailPhoneFaxID" INT,
"FKPersonID" BIGINT,
"FKEmployeeID" BIGINT,
"Mail" VARCHAR(100),
"Check" BOOLEAN)


For the TRIGGER to fire a view must be created of the table "tMail"
Code: Select all   Expand viewCollapse view
CREATE VIEW "viewMail" AS SELECT * FROM "tMail"

Example via DIRECT SQL
Code: Select all   Expand viewCollapse view
NSERT INTO "viewMail" VALUES IDENTITY(), 101, 2002 , 30003, 'jon@email.org', FALSE


When entered the 1st time, "Check" is set to FALSE.
The moment the entry is repeated, the TRIGGER changes "Check" to TRUE.
This is the acceptable outcome, but indeed the problem appears to be with the PK.

I have tried different ways for the PK, but if it is e.g. GENERATED, a new record is added.
This is only acceptable if entry for "tMail"."Mail" is unique. The TRIGGER should not fire.

If however there are multiple entries in tMail (via manual entry) and "Mail" is identical while all other entries (Except "Check") are varied, and then using the DIRECT SQL command (see above) all the "Check" will be set to TRUE. This is the acceptable outcome.

Our Client, however, uses data entry forms, and this type of simplified entry must also be done that way.
DIRECT SQL is therefore impossible.

My Quest therefore:
1. Form based entry for "tMail".
a. Where "tMail"."Mail" is identical, set "tMail"."Check" to TRUE
b. Where "tMail"."Mail" is different, add record

Again, I need to be able to identify the PK of the record already in the table "tMail" when an identical entry is attempted.

Dream
LO 5.x and OO 4.x on Windows 7 PRO & Ubuntu 16.04 LTS.
dreamquartz
 
Posts: 642
Joined: Mon May 30, 2011 4:02 am

Re: Input form using a view of a table

Postby eremmel » Fri Oct 05, 2018 12:20 pm

This is the same pattern as we discussed before about AddressBare and 'duplicate' addresses.
 Edit: This is the second time you started a discussion that is a cross post of continuation of a discussion on the HSQLDB forum without presenting a link to that forum. I think that is not a good habit. See here for start discussion Help 
Is your question answered: edit your initial post add "[Solved]" to the subject and check V
LO4.2.8.2 for real life with ORB; AOO4.1.3,LO5.2.5.1 on W7E SP1 64bit
It's Microsoft marketing that tells you computers are qualified for non-technicians
User avatar
eremmel
Volunteer
 
Posts: 1006
Joined: Tue Dec 30, 2008 1:15 am
Location: Barneveld, Netherlands

Re: Input form using a view of a table

Postby dreamquartz » Thu Oct 11, 2018 1:41 am

Thanks for letting me know.
This thread thread is a renewed attempt from below link
https://forum.openoffice.org/en/forum/viewtopic.php?f=13&t=93249
based on new insights.

Dream
LO 5.x and OO 4.x on Windows 7 PRO & Ubuntu 16.04 LTS.
dreamquartz
 
Posts: 642
Joined: Mon May 30, 2011 4:02 am


Return to Base

Who is online

Users browsing this forum: No registered users and 1 guest