Oracle“Instead of”触发器示例
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1370606/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Example of Oracle "Instead of" Trigger
提问by berlebutch
Does anyone have a simple example that explains why one would want to use an Oracle instead of trigger?
有没有人有一个简单的例子来解释为什么要使用 Oracle而不是触发器？
回答by Justin Cave
There are a few different scenarios I've seen
我见过几种不同的场景
1) You build a system with an initial data model and provide a set of views for the applications to query that are just 1:1 mappings with the tables. In the future, if you decide that you need to alter the data model and break one table up into two different tables, you can do so without affecting any existing applications by keeping the view layer unchanged. But in order to keep things transparent to the applications, you have to continue to allow inserts on the view that cause inserts into two (or more) different tables, you need an INSTEAD OF trigger.
1) 您构建一个具有初始数据模型的系统，并为应用程序提供一组视图以进行查询，这些视图只是与表的 1:1 映射。将来，如果您决定需要更改数据模型并将一个表分解为两个不同的表，您可以通过保持视图层不变来这样做，而不会影响任何现有应用程序。但是为了使事情对应用程序透明，您必须继续允许在视图上插入导致插入到两个（或更多）不同表中的内容，您需要一个 INSTEAD OF 触发器。
2) A variation on #1 where your applications start out just hitting tables directly. When you want to refactor a table definition without affecting existing applications, you rename the table (T becomes T_OLD), new tables are created, and a view called T is created with an INSTEAD OF trigger. This has the same effect-- it allows you to change the table declarations without requiring application changes.
2) #1 的一种变体，您的应用程序开始时只是直接点击表格。当您想重构表定义而不影响现有应用程序时，您可以重命名表（T 变为 T_OLD），创建新表，并使用 INSTEAD OF 触发器创建名为 T 的视图。这具有相同的效果——它允许您更改表声明而无需更改应用程序。
回答by APC
Justin has nailed the two main casesbut there a couple of other uses.
贾斯汀已经确定了两个主要案例，但还有其他一些用途。
One is where the table contains a historical trail of its data as well as the current version. Here we would want to build a view over the table (for instance to control which version is displayed) with an INSTEAD OF UPDATE trigger which updates the current record to be old and then applies the changes as an insert creating a new current record. There are variations on this theme; for instance, we can implement a policy of logical deletion by using an INSTEAD OF DELETE trigger to execute an update (setting a DELETED_FLAG column).
一种是表格包含其数据的历史轨迹以及当前版本。在这里，我们希望使用 INSTEAD OF UPDATE 触发器在表上构建一个视图（例如控制显示哪个版本），该触发器将当前记录更新为旧记录，然后将更改应用为创建新当前记录的插入。这个主题有很多变化；例如，我们可以通过使用 INSTEAD OF DELETE 触发器执行更新（设置 DELETED_FLAG 列）来实现逻辑删除策略。
Another scenario is when the application requires us to maintain two copies of some data - a master table and a local table. We would use an updateable view to select from the appropriate table and an INSTEAD OF trigger to decide which table to apply DML to.
另一种情况是应用程序需要我们维护一些数据的两个副本——一个主表和一个本地表。我们将使用可更新视图从适当的表中进行选择，并使用 INSTEAD OF 触发器来决定将 DML 应用到哪个表。
These are not glorious examples of how to design a good, sound data model. But they are the sorts of kludges we occasionally find ourselves having to implement.
这些并不是如何设计一个好的、健全的数据模型的光荣例子。但它们是我们偶尔发现自己必须实施的那种杂乱无章的东西。
回答by Jim Hudson
Another model, which I've seen used very successfully, is when the application architecture wants to insulate the database design from the front end. And they want to do that on the database side, rather than through an object-relational framework.
我已经看到使用非常成功的另一个模型是当应用程序架构想要将数据库设计与前端隔离时。他们希望在数据库端做到这一点，而不是通过对象关系框架。
For example, if you want to make the job of the front-end developer very easy, you can build a view for each front end page (or set of pages). Then use instead-of triggers to deal with the mapping back to the underlying tables.
例如，如果您想让前端开发人员的工作变得非常简单，您可以为每个前端页面（或一组页面）构建一个视图。然后使用替代触发器来处理回底层表的映射。
Of course, it helps to have solid database developers who understand how Oracle works.
当然，拥有了解 Oracle 工作原理的可靠数据库开发人员会有所帮助。
回答by duffymo
The docssay they're for modifying views.
该文件说，他们修改意见。
So if you create a view that's a JOIN of two tables, and you try to execute an INSERT statement on the VIEW, you can write an INSTEAD OF trigger that modifies the two underlying tables appropriately instead.
因此，如果您创建的视图是两个表的 JOIN，并且您尝试在 VIEW 上执行 INSERT 语句，您可以编写一个 INSTEAD OF 触发器来适当地修改两个基础表。
