CODEWITHSUNDEEP
sqloracle-databaseoracle11gdatabase-triggerdatabase-view
Vicky
Vicky

Reputation: 1

How to create the views using trigger?

Ca you please suggest if I can create the db view using triggers in Oracle? E.g. I have a trigger trig_cust and I want to create a view: Create or replace view vw_cust as select * from trig_cust;

P.S. need to use this view in loop

Upvotes: 0

Views: 1365

Answers (2)

Jim Macaulay
Jim Macaulay

Reputation: 5165

Triggers are an even of action. You cannot create a view out of trigger. Triggers are used to perform an action insert/ update / delete based on particular event where as views are used to select set of columns with the combination of multiple tables.

View is used to display a combined set of required columns from various tables in order to reduce the querying effort. View is majorly used for reporting purpose. You can have a trigger on the view, but not view out of trigger.

Upvotes: 2

Littlefoot
Littlefoot

Reputation: 143033

suggest if I can create the db view using triggers in Oracle?

Can you? Yes, you can. Should you? No, you shouldn't.

Anyway, just for amusement, here you go: table and its trigger which is supposed to create a view once a new row is inserted into a table. Pay attention to pragma; without it, it wouldn't work as you can't commit in a trigger. True, there's no explicit commit there, but create view is a DDL and it implicitly commits.

SQL> create table test (empno number, ename varchar2(20));

Table created.

SQL> create or replace trigger trg_ai_test
  2    after insert on test
  3    for each row
  4  declare
  5    pragma autonomous_transaction;
  6  begin
  7    execute immediate 'create or replace view v_test_' || to_char(:new.empno) ||
  8      ' as select * from test where empno = ' || :new.empno;
  9  end;
 10  /

Trigger created.

Testing:

SQL> insert into test (empno, ename) values (1, 'Little');

1 row created.

SQL> insert into test (empno, ename) values (2, 'Foot');

1 row created.

SQL> select * from v_test_1;

     EMPNO ENAME
---------- --------------------
         1 Little

SQL> select * from v_test_2;

     EMPNO ENAME
---------- --------------------
         2 Foot

SQL>

Upvotes: 0

Related Questions