You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
Problem with updatable view when the source field type not exact the field to store the data. (extra trailing spaces are added by firebird) [CORE3597]
#3951
When you create the database and execute the insert command you will get an error message.
INSERT INTO "VW_Benutzer" ("UserName") VALUES ('ABC');
This insert will not work.
Error Message:
----------------------------------------
The insert failed because a column definition includes validation constraints.
validation error for column UserName, value "ABC ". <-- The extra trailing spaces are added by firebird
At trigger 'VW_Benutzer_BI' line: 5, col: 3.
INSERT INTO "VW_Benutzer" ("UserName") VALUES ('ABCDEFGHIJ')
This insert will work.
The number of spaces appended seems to be arbitrary, it is not related to any field length. Firebird should never append spaces like that.
SET SQL DIALECT 3;
SET NAMES UTF8;
CREATE DATABASE '192.168.1.50:/DB/Check.fdb'
USER 'SYSDBA' PASSWORD 'XXXXXX'
PAGE_SIZE 16384
DEFAULT CHARACTER SET UTF8 COLLATION UTF8;
CREATE DOMAIN "SystemBenutzerNamen" AS
VARCHAR(31) CHARACTER SET UTF8
CHECK (value similar to '[A-Z]{1}[0-9|A-Z|_]{1,30}');
CREATE TABLE "Benutzer" (
"UserName" "SystemBenutzerNamen" NOT NULL,
"Bn_ASP_ID" INTEGER,
"Bn_Paswsort" VARCHAR(20) CHARACTER SET UTF8,
"Bn_DefaultMandantGP_ID" INTEGER,
"Bn_DefaultUserLanguage" VARCHAR(20) CHARACTER SET UTF8
);
/* View: "VW_Benutzer" */
CREATE OR ALTER VIEW "VW_Benutzer"(
"UserName",
"Bn_ASP_ID",
"Bn_Paswsort")
AS
select distinct rdb$user , "Bn_ASP_ID" , "Bn_Paswsort" from "RDB$USER_PRIVILEGES" A
left outer join "Benutzer" B ON A.rdb$user = B."UserName"
;
ALTER TABLE "Benutzer" ADD CONSTRAINT "UNQ_Benutzer_ASP" UNIQUE ("Bn_ASP_ID");
ALTER TABLE "Benutzer" ADD CONSTRAINT "PK_Benutzer" PRIMARY KEY ("UserName");
SET TERM ^ ;
/* Trigger: "VW_Benutzer_BD" */
CREATE OR ALTER TRIGGER "VW_Benutzer_BD" FOR "VW_Benutzer"
ACTIVE BEFORE DELETE POSITION 0
as
begin
delete from "Benutzer"
where ("UserName" = old."UserName");
end
^
/* Trigger: "VW_Benutzer_BI" */
CREATE OR ALTER TRIGGER "VW_Benutzer_BI" FOR "VW_Benutzer"
ACTIVE BEFORE INSERT POSITION 0
as
begin
insert into "Benutzer" (
"UserName",
"Bn_ASP_ID",
"Bn_Paswsort")
values (
upper(new."UserName"),
new."Bn_ASP_ID",
new."Bn_Paswsort");
end
^
/* Trigger: "VW_Benutzer_BU" */
CREATE OR ALTER TRIGGER "VW_Benutzer_BU" FOR "VW_Benutzer"
ACTIVE BEFORE UPDATE POSITION 0
as
begin
update "Benutzer"
set "UserName" = new."UserName",
"Bn_ASP_ID" = new."Bn_ASP_ID",
"Bn_Paswsort" = new."Bn_Paswsort"
where ("UserName" = old."UserName");
end
^
SET TERM ; ^
The text was updated successfully, but these errors were encountered:
Submitted by: Sascha Michel (datiscum1)
When you create the database and execute the insert command you will get an error message.
INSERT INTO "VW_Benutzer" ("UserName") VALUES ('ABC');
This insert will not work.
Error Message:
----------------------------------------
The insert failed because a column definition includes validation constraints.
validation error for column UserName, value "ABC ". <-- The extra trailing spaces are added by firebird
At trigger 'VW_Benutzer_BI' line: 5, col: 3.
INSERT INTO "VW_Benutzer" ("UserName") VALUES ('ABCDEFGHIJ')
This insert will work.
The number of spaces appended seems to be arbitrary, it is not related to any field length. Firebird should never append spaces like that.
SET SQL DIALECT 3;
SET NAMES UTF8;
CREATE DATABASE '192.168.1.50:/DB/Check.fdb'
USER 'SYSDBA' PASSWORD 'XXXXXX'
PAGE_SIZE 16384
DEFAULT CHARACTER SET UTF8 COLLATION UTF8;
CREATE DOMAIN "SystemBenutzerNamen" AS
VARCHAR(31) CHARACTER SET UTF8
CHECK (value similar to '[A-Z]{1}[0-9|A-Z|_]{1,30}');
CREATE TABLE "Benutzer" (
"UserName" "SystemBenutzerNamen" NOT NULL,
"Bn_ASP_ID" INTEGER,
"Bn_Paswsort" VARCHAR(20) CHARACTER SET UTF8,
"Bn_DefaultMandantGP_ID" INTEGER,
"Bn_DefaultUserLanguage" VARCHAR(20) CHARACTER SET UTF8
);
/* View: "VW_Benutzer" */
CREATE OR ALTER VIEW "VW_Benutzer"(
"UserName",
"Bn_ASP_ID",
"Bn_Paswsort")
AS
select distinct rdb$user , "Bn_ASP_ID" , "Bn_Paswsort" from "RDB$USER_PRIVILEGES" A
left outer join "Benutzer" B ON A.rdb$user = B."UserName"
;
ALTER TABLE "Benutzer" ADD CONSTRAINT "UNQ_Benutzer_ASP" UNIQUE ("Bn_ASP_ID");
ALTER TABLE "Benutzer" ADD CONSTRAINT "PK_Benutzer" PRIMARY KEY ("UserName");
SET TERM ^ ;
/* Trigger: "VW_Benutzer_BD" */
CREATE OR ALTER TRIGGER "VW_Benutzer_BD" FOR "VW_Benutzer"
ACTIVE BEFORE DELETE POSITION 0
as
begin
delete from "Benutzer"
where ("UserName" = old."UserName");
end
^
/* Trigger: "VW_Benutzer_BI" */
CREATE OR ALTER TRIGGER "VW_Benutzer_BI" FOR "VW_Benutzer"
ACTIVE BEFORE INSERT POSITION 0
as
begin
insert into "Benutzer" (
"UserName",
"Bn_ASP_ID",
"Bn_Paswsort")
values (
upper(new."UserName"),
new."Bn_ASP_ID",
new."Bn_Paswsort");
end
^
/* Trigger: "VW_Benutzer_BU" */
CREATE OR ALTER TRIGGER "VW_Benutzer_BU" FOR "VW_Benutzer"
ACTIVE BEFORE UPDATE POSITION 0
as
begin
update "Benutzer"
set "UserName" = new."UserName",
"Bn_ASP_ID" = new."Bn_ASP_ID",
"Bn_Paswsort" = new."Bn_Paswsort"
where ("UserName" = old."UserName");
end
^
SET TERM ; ^
The text was updated successfully, but these errors were encountered: