Updatable Firebird Views

An important observation

In earlier Firebird versions (1.0.x and 1.5), if triggers are written for naturally updatable views, then the actions of both the view trigger(s) and the underlying table trigger(s) will be executed:

CREATE TABLE NUMBERS(N INT);
CREATE VIEW VN AS SELECT N FROM NUMBERS;

SET TERM ^;
CREATE TRIGGER VN_TR_BI FOR VN BEFORE INSERT AS
BEGIN
   INSERT INTO NUMBERS VALUES(NEW.N);
END^
SET TERM ;^

INSERT INTO VN VALUES(9);
SELECT N FROM NUMBERS;

           N
============
           9
           9

Since this view is naturally updatable, the default action (the insert) is done along with the explicitly defined action, that's also in the trigger. As such, we got two insertions.

Firebird Version 2.x reverts to the original design, whereby the view triggers will automatically override the actions of any corresponding table triggers.

The necessary workaround in pre-Firebird 2 versions is to force naturally updatable views to be the second type, by using a join with a dummy table or RDB$DATABASE. Using RDB$DATABASE is convenient, since it is a system table with only one record:

CREATE VIEW VN2 AS SELECT N
FROM NUMBERS JOIN RDB$DATABASE ON 1 = 1;
INSERT INTO VN2 VALUES(0);

Statement failed, SQLCODE = -150
cannot update read-only view VN2

Getting the desired exception tells us that now, when a trigger is created in our view VN2, only that trigger will fire. From Firebird Version 2, you will no longer need to use such workaround.

Grouped selects

A view defined by a SELECT statement that involves GROUP BY can never be naturally updatable. Some grouped selects appear to meet the criteria because the columns appear to be "natural". For example, the following statement was tried:

CREATE VIEW VN3 AS SELECT N FROM NUMBERS GROUP BY N;
Statement failed, SQLCODE = -607
Dynamic SQL Error
-SQL error code = -607
-Invalid command
-must specify column name for view select expression

In this case, the view's column does not appear to contain any expression. However, GROUP BY generates a column that is an aggregation of the column from the original table and, thus, is considered by the engine to be an expression. Accordingly, the database engine demands that the view's column be given an explicit name.

Grouping as sorting?

You could argue that a GROUP BY may force an implicit sort, but this is an implementation artifact that cannot be considered seriously by application developers. The database engine is free to choose a way to satisfy the aggregation requested in a GROUP BY clause, but changes in the query optimizer could change the execution plan from one engine version to the next.

As an example, let's clean up the view definition from the previous example and try it out using the ISQL command SET PLAN to display the query plan constructed by the optimizer:

SET PLAN;
CREATE VIEW VN3(A) AS SELECT N FROM NUMBERS GROUP BY N;
SELECT A FROM VN3;

PLAN SORT ((VN3 NUMBERS NATURAL))
           A
============
           9

In this instance the engine chose a SORT but, if we put an index on the field and use a newer engine version, we might find that the plan generated is different. The SORT is a decision of the database engine's internal optimizer and therefore, GROUP BY and other clauses should not be depended on as a mechanism to cause a sorting in a view.

The UPDATE trigger

Now, we can write the trigger that will enable UPDATE statements. Firebird Version 1.5 has the concept of "universal triggers"—triggers that can be applied to more than one action (e.g. UPDATE and INSERT) at the same time. However, for these examples, we are going to use the usual approach of having different triggers for different modification statements.

Using ISQL's accepted syntax:

SET TERM ^;
CREATE TRIGGER V_TR_UPD
FOR V
BEFORE UPDATE
AS BEGIN
   UPDATE T
   SET A = NEW.A1
   WHERE A = OLD.A1;
END ^
SET TERM ;^

The code uses the special qualifiers NEW and OLD to refer, respectively, to the new value the UPDATE clause is trying to set and the original value that the column had. The column names used after these qualifiers should not be those of the underlying table's columns but those used by the view definition. In this case, they are the aliases A1 and A2. Since the view definition is a dummy self join, the trigger example uses only the first value and silently discards any value for the second column of the view, since it refers to the same field in the table.

Using the following:

UPDATE V
SET A1 = 10
WHERE A2 < 5;

If you attempt to update the second field, since the view's trigger negates this logic, the value for A2 will automatically be discarded. Consequently, the following statement has exactly the same effect as the previous one:

UPDATE V
SET A1 = 10,
A2 = 20
WHERE A2 < 5;

Changing other tables

A trigger is not limited to making changes only to the table(s) that underlie the view. Just like triggers on tables, a view trigger can perform operations on other tables as well. The kinds of modifications the trigger can perform are not restricted to being the same kind of modification statement that activated the trigger. In the previous example, the update trigger may do an INSERT or a DELETE on T or any other table. It all depends on the needs of the developer, provided that the trigger action makes sense.

Calling stored procedures

Finally, the trigger can call a stored procedure, do a SELECT, or indirectly cause other triggers to run when doing modification statements on other tables. For example, we may decide that our previous trigger should log in a separate table any attempt to change the second field of the view, data that we were effectively discarding until now. We'll assume a table named LOGT with two integer fields named A1 and A2:

SET TERM ^;
ALTER TRIGGER V_TR_UPD
BEFORE UPDATE
AS BEGIN
   UPDATE T
   SET A = NEW.A1
   WHERE A = OLD.A1;
   IF (NEW.A2 <> OLD.A2)
   THEN INSERT INTO LOGT(A1, A2) VALUES(NEW.A1, NEW.A2);
END ^
SET TERM ;^

SQL Privileges

It's important to remember that, when a view is created, the creator's privileges are checked against the involved tables. Once the view has been created successfully, the user is replace d by the view's creator at run-time to check the access rights for execution. The user only needs to be granted rights over the view itself. There are always some anomalous cases, but these are usually bugs. Due to the added complexity, views are not allowed to call procedures. However it is possible to work around this restriction by calling a procedure in the WHERE clause, but this due to the lack of a proper syntax checking, and was never intended to work.

Ordering trigger execution

If you need the triggers to fire in a particular order, include the POSITION clause in the CREATE TRIGGER statement. Being able to do everything in a single trigger should be compared with doing different logical actions in different triggers. The second option may be slightly more work for the database engine, but allows triggers to be more maintainable.

Unintended effects

Since triggers can affect other tables and even views, care should be taken not to produce an infinite loop. Imagine V has an update trigger that inserts a row in table T2, but this table has an AFTER INSERT trigger that updates V again. In this case, doing an UPDATE of V will generate an endless loop, until the engine gives up due to detection of a maximum recursion value or (most probably) stack exhaustion.

Typically, recursion is only checked when one procedure calls itself, so the responsibility of avoiding indirect recursion lies with the database developer. In the example given, a possibility (if it makes sense) is to update T directly (instead of going through V) from T2's trigger.

Notice statements like:

INSERT INTO V(A1, A2) VALUES(0, 0);

and

DELETE FROM V WHERE A1 = A2;

will still fail. The reason has been explained previously: Firebird enables the UPDATE statement on the view V because there's a BEFORE UPDATE trigger (it would be the same if there's an AFTER UPDATE trigger), but there's no trigger defined for INSERT or DELETE and since this view is not naturally updatable, the statements will fail.

More complex checking

When more power is needed, the user writes the triggers explicitly. Let's try an example with two tables:

CREATE TABLE T1(
ID INT NOT NULL PRIMARY KEY,
NAME VARCHAR(30));

CREATE TABLE T2(
ID INT NOT NULL REFERENCES T1(ID),
QUANTITY INT NOT NULL,
DATETIME TIMESTAMP DEFAULT CURRENT_TIMESTAMP);

CREATE VIEW STOCK(ID, NAME, QUANTITY)
AS SELECT T1.ID, T1.NAME, SUM(T2.QUANTITY)
FROM T1 LEFT JOIN T2
ON T1.ID = T2.ID
GROUP BY T1.ID, T1.NAME;

We have a GROUP BY and a SUM, so naming the view's fields is mandatory. We know this view can't be updated directly. Changing the quantity through the view doesn't make sense, but we will allow updating the name of the product and we will forbid making any insertion to the base tables through the view.

Granted, the view is not updatable, so it's protected naturally against any change until we write triggers, but we would like the user to see a custom message:

SET TERM ^;
CREATE TRIGGER TR_STOCK_BU
FOR STOCK BEFORE UPDATE
AS BEGIN
   UPDATE T1 SET NAME = NEW.NAME WHERE T1.ID = NEW.ID;
END^

CREATE EXCEPTION STOCK_VIEW_CANNOT_UPDATE
'Cannot update stock, use table t2.'^

CREATE TRIGGER TR_STOCK_BI
FOR STOCK BEFORE INSERT
AS BEGIN
   EXCEPTION STOCK_VIEW_CANNOT_UPDATE;
END^

We'll try the insertion case before going on the update:

INSERT INTO STOCK VALUES(0, 0, 0);
INSERT INTO STOCK VALUES(0, 0);
INSERT INTO STOCK VALUES(0);

Unfortunately for our test, whether we use one, two or three fields in the insertion, the checking logic detects early that the columns are not updatable (because there's a GROUP BY) and therefore generates the following error:

Statement failed, SQLCODE = -804
Dynamic SQL Error
-SQL error code = -804
-Count of read-write columns does not equal count of values

We have to name the columns explicitly to get to the point where the insertion is possible, our trigger then fires, stops the action, and gives a custom message:

INSERT INTO STOCK(ID, NAME, QUANTITY) VALUES(0, 0, 0);
Statement failed, SQLCODE = -836
exception 1
-Cannot update stock, use table t2.

SET PLAN ON;
INSERT INTO T1(ID, NAME) VALUES(1, 'apples');
INSERT INTO T2(ID, QUANTITY) VALUES(1, 50);

Now, we have to test our view and try to update the name:

SELECT * FROM STOCK;
PLAN SORT (JOIN (STOCK T1 NATURAL, STOCK T2 INDEX (RDB$FOREIGN2)))

          ID   NAME                                        QUANTITY
============ ==============================
           1 apples                                            50

UPDATE STOCK SET NAME = 'pears' WHERE ID = 1;
PLAN SORT (JOIN (STOCK T1 NATURAL, STOCK T2 INDEX (RDB$FOREIGN2)))

SELECT * FROM STOCK;
PLAN SORT (JOIN (STOCK T1 NATURAL, STOCK T2 INDEX (RDB$FOREIGN2)))

          ID NAME                                        QUANTITY
============ ==============================
           1 pears                                             50
COMMIT;

It works—an updatable join view using triggers.