使用DDL触发器创建视图 [英] View Creation using DDL trigger

问题描述

几年后，我正在为我的团队项目回顾Oracle(11g)的开发，并且需要帮助.我们正在尝试实现一个POC，其中任何添加/删除列都将删除并重新创建对应的视图.视图是指用于生成其别名和列选择的映射表.

I am looking back at Oracle (11g) development after few years for my team project and need help. We are trying to implement a POC where any add/drop column will drop and recreate a corrosponding view. View refers to a Mapping table for producing its alias names and selection of columns.

我的解决方案:

-1. DDL触发器，用于扫描添加列，删除列->标识列名->更新Field_Map表-> Drops View->使用Field_Map表别名创建视图

--1. DDL Trigger that scans for Add Column, Drop Column -> Identifies Column Names -> Updates Field_Map table -> Drops View -> Creates View with Field_Map table alias names

挑战:由于在DDL中创建了视图，因此收到了递归触发错误

Challenge: Received recursive trigger error because of View creation inside DDL

-2. DDL触发器扫描添加列，删除列->->更新字段映射表->将已标识的列名，表写入Audit_DDL表->在Audit_DDL表触发时DML触发器->禁用DDL触发器(以避免递归)->丢弃视图->使用Field_Map表别名创建视图

--2. DDL Trigger scans for Add Column, Drop Column -> -> Updates Field Map table -> Writes identified column names, tables to Audit_DDL table -> DML trigger on Audit_DDL table fires -> Disables DDL trigger (to avoid recursion) -> Drops view -> Creates view with Field_Map table alias names

挑战:收到递归触发错误.我认为，它仍将整个流程视为一项交易.在DML触发器下分离创建视图无济于事.

Challenge: Received recursive trigger error. I think, it is still considering whole flow as one transaction. Separating create view under DML trigger didn't help.

所以，我正在考虑替代方案:

-3.将触发器，表存储在Schema1和View Schema2中.我希望这可以避免递归，因为创建视图现在将发生在schema2上，而触发器是建立在schema1上的.

--3. Store Trigger, Tables in Schema1 and View Schema2. I am expecting, this may avoid recursion since create view will now happen on schema2 and trigger is built on schema1.

-4.创建一个存储过程，该过程将扫描表2的Audit_DDL条目(从#2开始)，以更新表和列.创建视图并标记已检查的已处理Audit_DDL条目.每小时的工作现在运行此过程.

--4. Create a Stored Procedure which scans for Audit_DDL entries (from #2) for tables, columns updated. Creates views and marks checked for processed Audit_DDL entries. Hourly job now runs this procedure.

有什么建议吗?预先感谢您对我的帮助！

Any suggestions? Thanks in advance for helping me out!

推荐答案

如果要从触发器中执行DDL，则它必须是异步的.最简单的解决方案是让DDL触发器使用DBMS_JOB包提交作业，该包将执行您想执行的任何DDL.在提交触发事务(ALTER语句)之前，该作业将不会运行.但是它可能会在几秒钟后运行(取决于正在运行的其他作业数，允许的作业数等).无论是构建要在触发器中执行的DDL语句，然后将其传递给作业，还是将作业所需的信息存储在表中并传递某种键(即对象名称)，然后让作业进行组装DDL语句是一个实现细节.

If you want to do DDL from a trigger, it would need to be asynchronous. The simplest solution would be for the DDL trigger to submit a job using the DBMS_JOB package that would execute whatever DDL you want to do. That job would not run until the triggering transaction (the ALTER statement) committed. But it would probably run a few seconds later (depending on how many other jobs are running, how many jobs are allowed, etc.). Whether you build the DDL statement you want to execute in the trigger and just pass it to the job or whether you store the information the job will need in a table and pass some sort of key (i.e. the object name) and let the job assemble the DDL statement is an implementation detail.

话虽如此，这似乎是一个非常糟糕的架构.如果要添加或删除列，则应该执行适当的变更控制过程.如果更改是通过更改控制进行的，则应该足够容易地将对视图的更改包括在同一脚本中.依赖视图的应用程序应作为变更控制过程的一部分进行测试.如果更改没有通过更改控制，并且随意添加或删除视图中的列，那么您在业务流程中会遇到更大的问题，很可能导致一个或多个应用程序因奇怪而瘫痪以及在看似模糊的时间点上的奇妙方式.

That being said, this seems like a really exceptionally poor architecture. If you are adding or removing a column, that is something that should be going through a proper change control process. If the change is going through change control, it should be easy enough to include the changes to the views in the same script. And applications that depend on the views should be tested as part of the change control process. If the change is not going through change control and columns are being added to or removed from views willy-nilly, you've got much bigger problems in the business process and you're very likely to cause one or more applications to barf in strange and wonderful ways at seemingly obscure points in time.

这篇关于使用DDL触发器创建视图的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！