Updating Views with Triggers
Go Up to Using 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-update-able views into update-able views.
If you define BEFORE triggers for a view that the InterBase engine considers to be directly update-able, on an UPDATE, DELETE, or INSERT operation the BEFORE trigger will fire; also, the default action attempted by the UPDATE/DELETE/INSERT statement will be executed, generating two actions and hence unexpected results.
Not all views can be made update-able by defining triggers for them. For example, this read-only view attempts to count records from the client; but regardless of the triggers you define for it, all operations except SELECT always fail:
CREATE VIEW AS SELECT 1 FROM MyTable;
You can specify non-default behavior for update-able views, as well. InterBase does not perform write-throughs 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 Update-able.
Example: 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.
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;