Got information from your archives, BUT solution is not provided or there's no solution???


Archive : "Why the trigger disappears... May 28, 2003
Reviewer: Kamal Kishore from New Jersey, USA "

Hi Tom,
After you re-create the view definition using CREATE OR REPLACE (maybe to change its condition), the trigger on the view disappears. Is this expected behaviour?

SQL> create or replace view emp_view
2 as
3 select * from emp
4
SQL> /

View created.

SQL> create or replace trigger trig_emp_view
2 instead of insert or update on emp_view
3 for each row
4 begin
5 Null ;
6 end ;
7 /

Trigger created.

SQL> show errors
No errors.
SQL> select ut.trigger_name, ut.table_owner, ut.table_name
2 from user_triggers ut where trigger_name = 'TRIG_EMP_VIEW'
3 /

TRIGGER_NAME TABLE_OWNER TABLE_NAME
------------------------------ ------------------------------
------------------------------
TRIG_EMP_VIEW KKISHORE EMP_VIEW

1 row selected.

SQL> create or replace view emp_view
2 as
3 select * from emp
4 /

View created.

SQL> select ut.trigger_name, ut.table_owner, ut.table_name
2 from user_triggers ut where trigger_name = 'TRIG_EMP_VIEW'
3 /

no rows selected

Followup:
the "or replace" is replacing the view and all related things. the create or
replace preserves grants -- not the triggers. it is a "new view"

====>> so what should I do if i have view's with instead of triggers became invalid? what syntax can I use to alter the view without my trigger disappearing?
if "create or replace" cannot be used, what syntax can i used?