When a DML operation is performed on a view, the changes can be passed through to the underlying tables from which the view was created only if certain conditions are met. If a view meets these conditions, it is updatable. If it does not meet these conditions, it is read-only, and writes to the view cannot be passed through to the underlying tables.
Values can only be inserted through a view for those columns named in the view. Firebird stores NULL into any unreferenced columns. A view will not be updatable if there are any columns not in the view that are non-nullable.
A read-only view can be made updatable by means of triggers.
A view will be read-only if its SELECT statement has any of the following characteristics:
Making Read-Only Views Updatable
You can write triggers that will perform the correct writes to the base tables when a DELETE, UPDATE, or INSERT operation is requested for a view. This Firebird feature can turn many non-updatable views into updatable views.
The following script 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 base tables:
When defining triggers for views, take care to ensure that any triggers on the view do not create a conflict or an unexpected condition with regard to triggers defined for the base tables. The trigger event for the view precedes that for the table, respective of phase (BEFORE/AFTER).
For example, suppose you have a Before Insert trigger on the base table that fetches a fresh generator value for the primary key if its new.value is null. If the view trigger includes an INSERT statement for the base table, omit the primary key column from the view’s statement. This causes NULL to be passed to the new.value of the primary key column, allowing the table trigger to do its work.
Not all views can be made updatable by defining triggers for them. For example, this handy little read-only view reads a context variable from the server, but regardless of the triggers you define for it, all operations except SELECT will fail:CREATE VIEW SYSTRANS (CURR_TRANSACTION) AS SELECT CURRENT_TRANSACTION FROM RDB$DATABASE;
Naturally Updatable Views
A view is naturally updatable if both of the following conditions are met:
The following statement creates a naturally updatable view:CREATE VIEW EMP_MNGRS (FIRST, LAST, SALARY) AS SELECT FIRST_NAME, LAST_NAME, SALARY FROM EMPLOYEE WHERE JOB_CODE = 'Mngr' WITH CHECK OPTION;
Because the WITH CHECK OPTION clause was included in this specification, applications will be prevented from changing the JOB_CODE, even if there is no violation of the foreign key constraint on this column in the base table.
Changing the Behavior of Updatable Views
Alternative behavior for naturally updatable views can be specified using triggers. For a particular phase (BEFORE/AFTER) of an operation, the view triggers fire before the base table’s triggers. With care and skill, it is thus possible to use views to pre-empt the normal trigger behavior of the base table under planned circumstances.
It is also possible to create havoc with badly planned view triggers. Test, test, test!
Firebird Related Interview Questions
|RDBMS Interview Questions||MySQL Interview Questions|
|Linux Interview Questions||Mac OS X Deployment Interview Questions|
|Windows Administration Interview Questions||Windows Server 2003 Interview Questions|
|SQL Interview Questions||NoSQL Interview Questions|
|Advanced C++ Interview Questions|
All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.