[CLOSED] Input form using a view of a table

[dreamquartz]

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

 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

Update: 05/04/2019. Started new thread, based on new insights

[eremmel]

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

[dreamquartz]

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

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

CREATE VIEW "viewMail" AS SELECT * FROM "tMail"

Example via DIRECT SQL

Code: Select all

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

[eremmel]

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

[dreamquartz]

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

Dream