[SQL] updateable View

[Hansa]

Hi,

kann mich jemand hier aufklären, was hier genau geschieht ? Das ist aus dem Interbase 6 Data Definition Guide (S.190) :

SQL-Code:

CREATE TABLE Table1 (
  ColA INTEGER NOT NULL,
  ColB VARCHAR(20),
  CONSTRAINT pk_table PRIMARY KEY(ColA)
););

CREATE TABLE Table2 (
  ColA INTEGER NOT NULL,
  ColC VARCHAR(20)
  CONSTRAINT fk_table2 FOREIGN KEY REFERENCES Table1(ColA));

CREATE VIEW TableView AS SELECT Table1.ColA, Table1.ColB, Table2.ColC FROM Table1, Table2
  WHERE Table1.ColA = Table2.ColA;

CREATE TRIGGER TableView_Delete FOR TableView BEFORE DELETE AS BEGIN
  DELETE FROM Table1 WHERE ColA = OLD.ColA;
  DELETE FROM Table2 WHERE ColA = OLD.ColA;
END;


CREATE TRIGGER TableView_Update FOR TableView BEFORE UPDATE AS BEGIN
  UPDATE Table1 SET ColB = NEW.ColB WHERE ColA = OLD.ColA;
  UPDATE Table2 SET ColC = NEW.ColC WHERE ColA = OLD.ColA;
END;

CREATE TRIGGER TableView_Insert FOR TableView BEFORE INSERT AS BEGIN
  INSERT INTO Table1 values (NEW.ColA,NEW.ColB);
  INSERT INTO Table2 values (NEW.ColA,NEW.ColC);
END;

Schreibe den Text lieber auch noch dabei:

Zitat:

Updating views with triggers

Views that are based on joins—including reflexive joins—and on aggregates cannot be updated directly. You can, however, write triggers that will perform the correct writes to the base tables when a DELETE, UPDATE, or INSERT is performed on the view. This InterBase feature turns non-updatable views into updatable views. Tip You can specify nondefault behavior for updatable views, as well. InterBase does not
perform writethroughs on any view that has one or more triggers defined on it. This means that you can have complete control of what happens to any base table when users modify a view based on it.

For more information about updating and read-only views, see “Types of views: read-only and updatable” on page 131.

The following example creates two tables, creates a view that is a join of the two tables, and then creates three triggers—one each for DELETE, UPDATE, and INSERT—that will pass all updates on the view through to the underlying base tables.

Mehr steht da nicht. Schön und gut, aber folgendes verstehe ich nicht : die beiden Constraints verursachen einen Fehler. Sind sie auskommentiert läuft alles sauber ab. ColA läßt sich nicht verändern.