Problem with updatable view when the source field type not exact the field to
store the data. (extra trailing spaces are added by firebird)
-------------------------------------------------------------------------------------------------------------------------------------------
Key: CORE-3597
URL: http://tracker.firebirdsql.org/browse/CORE-3597
Project: Firebird Core
Issue Type: Bug
Components: Charsets/Collation
Affects Versions: 2.5.0, 2.5.1
Environment: LI-V6.3.1.26349 Firebird 2.5
WI-V6.3.0.26074 Firebird 2.5
Reporter: Sascha Michel
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 ; ^
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
------------------------------------------------------------------------------
BlackBerry® DevCon Americas, Oct. 18-20, San Francisco, CA
Learn about the latest advances in developing for the
BlackBerry® mobile platform with sessions, labs & more.
See new tools and technologies. Register for BlackBerry® DevCon today!
http://p.sf.net/sfu/rim-devcon-copy1
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
