CODEWITHSUNDEEP
oracle-databaseviewtriggers
user2999664
user2999664

Reputation: 15

Creation of a view inside trigger body - Oracle

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

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

Upvotes: 0

Views: 1846

Answers (2)

Przemyslaw Kruglej
Przemyslaw Kruglej

Reputation: 8123

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

Check at SQLFiddle

Upvotes: 3

Luke Woodward
Luke Woodward

Reputation: 65034

No, you can't. Creating a view forces a commit, and you cannot commit in a trigger.

Here's what happens when you try to do this:

SQL> CREATE TABLE test (a integer);

Table created.

SQL> INSERT INTO test (a) VALUES (5);

1 row created.

SQL> COMMIT;

Commit complete.

SQL> CREATE OR REPLACE TRIGGER test_trig
  2    AFTER UPDATE ON test
  3    FOR EACH ROW
  4  BEGIN
  5    EXECUTE IMMEDIATE 'CREATE OR REPLACE VIEW test_view AS SELECT * FROM test';
  6  END;
  7  /

Trigger created.

SQL> UPDATE test SET a = 6;
UPDATE test SET a = 6
       *
ERROR at line 1:
ORA-04092: cannot COMMIT in a trigger
ORA-06512: at "LUKE.TEST_TRIG", line 2
ORA-04088: error during execution of trigger 'LUKE.TEST_TRIG'

Upvotes: 2

Related Questions