[Firebird-devel] [ firebird-Bugs-1492064 ] Updatable Views not working

[SourceForge.net <no...@so...>]

Bugs item #1492064, was opened at 2006-05-20 08:45
Message generated for change (Comment added) made by robocop
You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=109028&aid=1492064&group_id=9028

Please note that this message will contain a full copy of the comment thread,
including the initial issue submission, for this request,
not just the latest update.
Category: Core Engine
Group: As Designed/Pitfall
Status: Deleted
Resolution: Rejected
Priority: 5
Submitted By: jvp (jvpgr)
Assigned to: Nobody/Anonymous (nobody)
Summary: Updatable Views not working

Initial Comment:
After upgrating to fb 2.0.1 and / or fb 2.0.2 I have 
lots of updatable views, working in fb 1.5 that stoped 
working.

I include one of the smaller ones bellow

/* View: VW_PROFESSIONS */
CREATE VIEW VW_PROFESSIONS(
    PRM_ID,
    PRM_TYPE,
    PRMTR,
    LANGUAGE,
    GLYPH,
    GLYPH_IDX,
    STATE_GLYPH_IDX)
AS
select PRM_ID, PRM_TYPE, PRMTR, LANGUAGE, GLYPH, 
GLYPH_IDX, STATE_GLYPH_IDX
from parameters
where prm_type = 'ΕΠΑΓΓΕΛΜΑΤΑ';


/* Trigger: VW_PROFESSIONS_BI */
CREATE TRIGGER VW_PROFESSIONS_BI FOR VW_PROFESSIONS
ACTIVE BEFORE INSERT POSITION 0
AS
begin
  new.prm_type = 'ΕΠΑΓΓΕΛΜΑΤΑ';
end
^

the table that should be updated through the view 
follows

CREATE GENERATOR PRM_RELATION_ID_GEN;

CREATE TABLE PARAMETERS (
    PRM_ID           INTEGER,
    PRM_TYPE         VARCHAR(25) NOT NULL,
    LANGUAGE         VARCHAR(25) DEFAULT 'GREEK' NOT 
NULL,
    PRMTR            VARCHAR(50) NOT NULL,
    SYSTEM           CHAR(1) DEFAULT 'F' NOT NULL,
    GLYPH            BLOB SUB_TYPE 0 SEGMENT SIZE 128,
    GLYPH_IDX        SMALLINT,
    STATE_GLYPH_IDX  SMALLINT
);

ALTER TABLE PARAMETERS ADD CONSTRAINT 
CHK_PARAMETERS_SYSTEM check (SYSTEM IN ('T','F'));

ALTER TABLE PARAMETERS ADD CONSTRAINT PK_PARAMETERS 
PRIMARY KEY (PRM_TYPE, PRMTR);

ALTER TABLE PARAMETERS ADD CONSTRAINT 
FK_PARAMETERS_TYPE FOREIGN KEY (PRM_TYPE, LANGUAGE) 
REFERENCES PARAMETER_TYPES (PRM_TYPE, LANGUAGE) ON 
DELETE CASCADE ON UPDATE CASCADE;

CREATE UNIQUE INDEX PARAMETERS_IDX ON PARAMETERS 
(PRM_ID);
CREATE INDEX PARAMETERS_IDX1 ON PARAMETERS (PRMTR);

/* Trigger: PARAMETERS_CHCK_SYSTEM_BD */
CREATE TRIGGER PARAMETERS_CHCK_SYSTEM_BD FOR PARAMETERS
ACTIVE BEFORE DELETE POSITION 0
AS
begin
    IF(OLD.SYSTEM = 'T')
    THEN EXCEPTION SYSTEM_PRM;
end
^

/* Trigger: PARAMETERS_CHCK_SYSTEM_BU */
CREATE TRIGGER PARAMETERS_CHCK_SYSTEM_BU FOR PARAMETERS
ACTIVE BEFORE UPDATE POSITION 0
AS
begin
    IF(OLD.PRMTR <> NEW.PRMTR)
    THEN IF(OLD.SYSTEM = 'T')
         THEN EXCEPTION SYSTEM_PRM;
end
^

/* Trigger: PARAMETERS_SET_ID_BI */
CREATE TRIGGER PARAMETERS_SET_ID_BI FOR PARAMETERS
ACTIVE BEFORE INSERT POSITION 0
AS
begin
  IF(NEW.PRM_ID IS NULL)
  THEN NEW.PRM_ID = GEN_ID(PRM_RELATION_ID_GEN,1);
end
^

/* Trigger: PARAMETERS_SET_SYSTEM_BI */
CREATE TRIGGER PARAMETERS_SET_SYSTEM_BI FOR PARAMETERS
ACTIVE BEFORE INSERT POSITION 0
AS
begin
  SELECT SYSTEM
    FROM PARAMETER_TYPES
   WHERE PRM_TYPE = NEW.PRM_TYPE
    INTO NEW.SYSTEM;
end
^
/* Privileges of triggers */
GRANT UPDATE, REFERENCES ON PARAMETERS TO TRIGGER 
PARAMETERS_SET_ID_BI;
GRANT INSERT ON PARAMETERS TO TRIGGER 
PARAMETER_RELATIONS_BI;
GRANT SELECT, UPDATE ON PARAMETERS TO TRIGGER 
PARAMETER_RELATIONS_BU;

when I insert into the view everything seems ok, no 
error message is generated but there is no record in 
the source table, with update everything works ok.

the same database/tables/views are working in an fb 1.5 
production system for three months now

I have tested the above in winXP SP1 & SP2, win2003 
server, SP1 & SP2, results are always the same


----------------------------------------------------------------------

>Comment By: Claudio Valderrama C. (robocop)
Date: 2006-05-28 05:09

Message:
Logged In: YES 
user_id=62823

We restored the old behavior. When someone has an updatable
view and wants another action than the default, the only
trick is to create useless joins to make it non-updatable.
With the old and restored functionality, updatable views
without triggers work automatically, but triggers override
the action. The Borland behavior causes either duplicate
insertions or two updates if the user trigger happens to
insert/update the base table, because the engine will do it
implicitly, too. Worse with views based on views and so on.
We want predictable behavior.

----------------------------------------------------------------------

Comment By: jvp (jvpgr)
Date: 2006-05-20 21:13

Message:
Logged In: YES 
user_id=1260355

yes, you are right, I missed that note, w/o this to mean 
that I don't value the documentation team's work as you 
imply, why the update still works though ?

Does it really had to be changed ? Oracle works the same 
way fb 1.5 does. My app, works both on fb 1.5 and Oracle 8-
10 just fine (till now)

just curious

----------------------------------------------------------------------

Comment By: Claudio Valderrama C. (robocop)
Date: 2006-05-20 20:03

Message:
Logged In: YES 
user_id=62823

Obviously you didn't read the Release Notes. Our
documentation people waste long hours for that work to not
be ignored by users.
Chapter 15, "Known Compatibility Issues"
Item "SQL Migration Issues"
Subitem "DDL"
Title "Views made updatable via triggers no longer perform
direct table operations"


----------------------------------------------------------------------

You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=109028&aid=1492064&group_id=9028