Updatable Firebird Views
Views are like virtual tables: they store a SQL statement that generates a result set. Columns in the result set can come from expressions, aggregate functions, joins and even other views.
There are three different types of views:
- Naturally updatable views are simple SELECT statements and they can be updated automatically without having to write any triggers.
- Updatable views are more complex SELECT statements that have underlying triggers defined for them to allow them to accept UPDATE, INSERT or DELETE statements.
- Non-updatable views are complex SELECT statements containing expressions that are not accepted by the database engine as updatable, even if triggers are written for the view. This may change over time as the database engine is enhanced.
Naturally updatable views
To be naturally updatable, a view has to fulfil the following conditions:
- no joins
- no grouping
- only one table is used
- no view column can be derived from an expression or a constant
- the view should include all of a table's fields that are defined as NOT NULL
In other words, only the simplest view definitions can be naturally updated without having to do extra work (i.e. write triggers).
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.
Sorting in Views
The ORDER BY clause is not allowed in any view definition. It should not be considered a limitation, since a view is queried as though it were a table and sorting is part of the normal SQL SELECT statement syntax. This is coherent with the idea of a view as a virtual table: tables have no explicit order:
CREATE VIEW VN3 AS SELECT N FROM NUMBERS ORDER BY N; Statement failed, SQLCODE = -104 Dynamic SQL Error -SQL error code = -104 -Token unknown - line 1, char 42 -order
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.
Now, we are going to focus on the second view type—the kind that is not naturally updatable but can be made so by means of triggers.
We are going to use a simple table, T, with a single field, A for our examples. Assume an integer type for the column. You could use any data type: it does not affect any of the overall concepts discussed in this paper.
Let's begin with a simple view that cannot be updated naturally: a self inner join based on this statement:
SELECT T1.A, T2.A FROM T T1 JOIN T T2 ON T1.A = T2.A;
The result is not very interesting, but it should produce two columns with the same value in each row. However, it won't accept any modification statement (UPDATE, INSERT or DELETE) until one or more triggers have been written.
If you try a modification statement, then the database engine generates the following error message:
Statement failed, SQLCODE = -150 cannot update read-only view V
Firebird (and InterBase) allow triggers to be defined before and after any modification statement. The database engine actually doesn't care whether the action is BEFORE or AFTER. In either case it will permit the UPDATE, INSERT or DELETE statement if there is an applicable trigger. What the trigger actually does cannot be checked semantically by the database engine, so it will not actually validate whether the trigger does something sensible or useful. That's up to the developer.
For example, a trigger written to fire before or after an UPDATE statement will allow the UPDATE clause to be applied to the view.
We'll use now use the previous example to create the following simple view:
CREATE VIEW V AS SELECT T1.A, T2.A FROM T T1 JOIN T T2 ON T1.A = T2.A;
This statement will fail because we have two columns with the same name. However the actual error message is somewhat cryptic:
Statement failed, SQLCODE = -607 unsuccessful metadata update -STORE RDB$RELATION_FIELDS failed -attempt to store duplicate value (visible to active transactions) in unique index "RDB$INDEX_15"
This is because the cited system index is making sure that that there are no duplicates in the system table that maintains the details about the columns that are held in a table. The combination table_name, field_name has to be unique. Therefore, the view definition should provide column aliases for those columns:
CREATE VIEW V(A1, A2) AS SELECT T1.A, T2.A FROM T T1 JOIN T T2 ON T1.A = T2.A;
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;
Multiple activities in triggers
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 ;^
View triggers in action
Triggers in Firebird work on a row-by-row basis, that is, they are called for every row affected. To have an UPDATE statement firing the trigger, we need at least one row in the base table
INSERT INTO T VALUES(1);
Now we can try a full update. We have one row, so we won't bother to write a WHERE clause to filter records:
UPDATE V SET A2 = 0;
We initially stored the value 1 in T's A but we only updated V's A2. Therefore, NEW.A1 takes the current value of the field A1 and nothing visible happens (the column is updated with its same current value), but NEW.A2 is set to zero, so the INSERT part is executed:
SELECT * FROM LOGT; A1 A2 ============ ============ 1 0
We didn't need to modify the original trigger: we could have written instead a second trigger for the same table and for the same action, but only for doing the INSERT part.
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 replaced 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.
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);
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.
WITH CHECK OPTION
There's a way to have the engine generate triggers for simple cases, when a view has a WHERE clause and we want that clause to be enforced for insertions and updates:
CREATE VIEW V2 AS SELECT A FROM T WHERE A > 5 WITH CHECK OPTION;
In this case, the CHECK OPTION tells the database engine to automatically generate triggers that will avoid the filter (A > 5) being bypassed when inserting or updating records. The idea is that the user of the view can change only the items (rows) that are visible through the view. Therefore, inserting any values that are less than or equal to five will result in an error message. The same will happen if you try to update the column. Let's try:
SELECT * FROM T; A ============ 1
SELECT * FROM V2;
The command is executed but there are no records to display. There isn't an error. The only record in T (that's the table V2 is based on) doesn't meet the WHERE clause and therefore, no record is returned. Inserting a record that doesn't meet the same WHERE clause will generate an error:
INSERT INTO V2 VALUES(0); Statement failed, SQLCODE = -297 Operation violates CHECK constraint on view or table V2
Now, we'll attempt to update all v2's records to a value that doesn't meet the CHECK and afterwards, do a full delete.
UPDATE V2 SET A = 0; SELECT * FROM T; A ============ 1 DELETE FROM V2; SELECT * FROM T; A ============ 1
We then discover that the table T wasn't affected, it still has the same record with a field having a value of one. Why? Because the database engine is safeguarding the view against insertions that don't meet the WHERE clause as defined by the CHECK OPTION by raising an exception, but in the case of updates and deletions, because they operate over filtered records, an exception isn't raised. The view's records include only values greater than five. Because T's only record is not visible through V2 it won't be touched. This is consistent behaviour.
The CHECK option has limited value, in that it only is accepted with WHERE clauses and on single tables, not joins. To do more complex validation logic for generating view sets, the analysis of the original statement in the engine would have to be enhanced.
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.