在触发器主体内创建视图-Oracle [英] Creation of a view inside trigger body - 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中检查

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