在触发器主体内创建视图

本文介绍了在触发器主体内创建视图-Oracle的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

是否可以在Oracle中的触发器内创建或替换视图?

Is possible to create or replace a view inside a trigger in Oracle?

该视图是通过联接2个表创建的，其中一个是由触发器更新的表

The view is created by joining 2 tables and one of them is the one updated by the trigger

推荐答案

只需提供所有选项(但是在触发器中创建视图的想法可能很奇怪...)，您可以可以创建触发器中的视图.是的，将跟随隐式COMMIT，但是如果我们使触发器在自主事务中工作，那么动态DDL将不会失败.以卢克·伍德沃德(Luke Woodward)为例:

Just to provide all options (however weird the idea of creating a view inside a trigger might be...) you can create a view in a trigger. Yes, an implicit COMMIT will follow, but if we make the trigger work in autonomous transaction, then the dynamic DDL will not fail. Using Luke Woodward's example:

CREATE TABLE test (a integer); INSERT INTO test (a) VALUES (5); CREATE OR REPLACE TRIGGER test_trig AFTER UPDATE ON test FOR EACH ROW DECLARE -- making the trigger work outside of the main transaction PRAGMA autonomous_transaction; BEGIN EXECUTE IMMEDIATE 'CREATE OR REPLACE VIEW test_view AS SELECT * FROM test'; END; / UPDATE test SET a = 6; SELECT * FROM test_view;

A ---------- 6

在SQLFiddle中检查