Скачать или смотреть How to Create a SQL View That Accepts Inserted Values

Learn how to create a SQL view with the ability to accept inserted values by understanding the limitations of views and using triggers effectively.
---
This video is based on the question https://stackoverflow.com/q/67144630/ asked by the user 'Hard_Whey' ( https://stackoverflow.com/u/15028747/ ) and on the answer https://stackoverflow.com/a/67144990/ provided by the user 'Aman Singh Rajpoot' ( https://stackoverflow.com/u/12937828/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: Creating a SQL view that will accept inserted values

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
How to Create a SQL View That Accepts Inserted Values

Creating a SQL view can be a powerful way to simplify data retrieval and enhance the way you access information in your database. However, many users encounter challenges when they try to insert new values into a view. If you are facing the error ORA-01733: virtual column not allowed here when attempting to insert values into your view, you are not alone! In this guide, we'll explore how to circumvent these limitations to allow data insertion into a SQL view.

Understanding the Problem

When creating your SQL view, you might have encountered a few limitations, particularly regarding insertions. In SQL, a view is essentially a virtual table representing the result of a stored query. While views can be incredibly useful, they also come with rules that must be strictly followed, particularly when it comes to inserting, updating, or deleting data.

The specific error you received (ORA-01733) indicates that the current view structure does not support direct insert operations due to the following reasons:

Missing keys from the underlying tables.

Use of certain SQL clauses such as DISTINCT or GROUP BY that alter the basic relationship of the data being retrieved.

Solutions to Enable Insertions into a SQL View

There are a couple of strategies you can implement to allow insertions into your view:

1. Structure the View Correctly

To allow for direct insertions into the view, you need to ensure that the view adheres to the following criteria:

Include all keys from the source tables: Ensure that any keys present in the underlying table(s) are included in the view.

Avoid using DISTINCT or GROUP BY clauses: These clauses can disrupt the key relationships necessary for insert operations.

Create a key preserving table: Make sure that the table you are working with has a 1:1 relationship with the rows in the view.

Revised View Example

Given your current view, you may need to re-architect it without the need for complex joins or filtering conditions that would obstruct direct insertions.

[[See Video to Reveal this Text or Code Snippet]]

Note: The above query is simplified and may not represent your actual data structure requirements based on other relationships in your database.

2. Using INSTEAD OF Triggers

If redesigning your view structure is not viable, another option is to use an INSTEAD OF trigger. Triggers allow you to define custom behaviors when data manipulation commands (like INSERT, UPDATE, or DELETE) are executed against the view.

Creating an INSTEAD OF Trigger

Here’s how you can define an INSTEAD OF trigger for your NATIONAL_ITEMS view:

[[See Video to Reveal this Text or Code Snippet]]

In this code:

The trigger intercepts the INSERT operation on the NATIONAL_ITEMS view.

Instead of inserting into the view, it directly inserts into the ITEM table, effectively allowing you to populate the underlying data structure.

Conclusion

Inserting values into a SQL view can be complex, but it is entirely possible with the right approach. By restructuring your view to include necessary keys and avoiding complex clauses, or by employing an INSTEAD OF trigger, you can successfully perform insert operations on your views. With these strategies, you will be able to maintain your database integrity while also enjoying the simplified access that views provide.

Feel free to reach out or leave a comment if you have any questions about SQL views or database management. Happy SQL querying!