compile view
Tarun Babu, January 28, 2004 - 2:57 am UTC
Hi tom,
SQL> alter view emp_view compile;
View altered.
SQL> select ut.trigger_name, ut.table_owner, ut.table_name
2 from user_triggers ut where trigger_name = 'TRIG_EMP_VIEW';
no rows selected
After recompiling view, the same result.
Why it is so? Is the trigger dropped after create or replace?
Your comments please.
January 28, 2004 - 8:36 am UTC
doesn't happen to me. for all I know, your trigger never existed. See how after the alter view -- the trigger is still there (we see the output from it!)
ops$tkyte@ORA817DEV> create or replace view my_view
2 as
3 select x from t;
View created.
ops$tkyte@ORA817DEV> insert into my_view values ( 'world' );
1 row created.
ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> create or replace trigger v_trigger
2 instead of insert on my_view
3 begin
4 dbms_output.put_line( 'hello ' || :new.x );
5 end;
6 /
Trigger created.
ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> insert into my_view values ( 'world' );<b>
hello world</b>
1 row created.
ops$tkyte@ORA817DEV> alter view my_view compile;
View altered.
ops$tkyte@ORA817DEV> insert into my_view values ( 'there' );<b>
hello there</b>
1 row created.
Re-create of views should always Re-create it's trigger (instead of) ?
Juliet Co, January 28, 2004 - 4:17 am UTC
Additional Question:
Does it mean if i need to re-create of views should always re-create it's trigger (instead of) ?
January 28, 2004 - 8:45 am UTC
if you want the instead of triggers -- yes, you had better.
Viewing trigger body present in a different schema
A reader, May 09, 2005 - 11:33 am UTC
Tom,
I have a table and trigger in schema1, schema 2 would like to view the trigger body in schema 1.
Owner/Schema: User1
Table: TestTable
Trigger on table 'TestTable': tr_TestTable
Owner/Schema: CommonUser
CommonUser has select, insert, update and delete permissions on User1.TestTable.
Question
If user 'CommonUser' needs to view the trigger body 'tr_TestTable' , what permissions are required?
or what system table would have this information
Thank you
May 09, 2005 - 11:41 am UTC
dba_triggers and dba_source have open access to this for anyone that can query them.
otherwise, the ALL_ views require powerful "any" privs to see the bodies (don't go there)
common user would need select on the DBA_ views, or common user could be granted execute on a stored procedure owned by the owner of the trigger that returns the information needed.
Viewing trigger body present in a different schema
A reader, May 09, 2005 - 12:35 pm UTC
Thank you
I learned this lesson the hard way
neil, May 09, 2005 - 1:27 pm UTC
During our app's development, I went through a lot of 'where did my trigger go'! We eventually figured out that a CREATE OR REPLACE causes the trigger to disappear.
Moral of the story? Never use the database as a sole code respository! All of my CREATE VIEW scripts include the trigger sql now :)
I opened a tar and Oracle said that this is expected behavior. Not necessairly something they're wild about but is expected nonetheless.
May 09, 2005 - 2:50 pm UTC
SQL is code.
Code is Code.
CODE needs CM (configuration management)
things that start with create, alter, drop, grant, etc are *code*
create or replace should not cause a trigger to disappear?????
you cannot create or replace a table.
you can however create or replace trigger, and in doing so you end up with the new trigger -- but you have the trigger.
MARCIEL, April 26, 2024 - 7:41 pm UTC
I've had some situations like this, where when recreating a view, the triggers disappear. Nor does the audit record what caused the deletion. I believe it is an Oracle bug, as it happens occasionally.
April 30, 2024 - 4:59 am UTC
I think we'd need to see a test case there, because my understanding is that the trigger is
always dropped.
SQL> create or replace view emp_v
2 as select * from emp;
View created.
SQL>
SQL> create or replace trigger trg_emp
2 instead of insert on emp_v
3 begin
4 null;
5 end;
6 /
Trigger created.
SQL> create or replace view emp_v
2 as select * from emp
3 where deptno = 20;
View created.
SQL> select trigger_name
2 from user_triggers
3 where trigger_name = 'TRG_EMP';
no rows selected