1

Dropping a table invalidates dependent objects and removes object privileges on the table. If you want to re-create the table, then you must regrant object privileges on the table, re-create the indexes, integrity constraints, and triggers for the table, and respecify its storage parameters.

I got little confused from this document. It says dropping a table invalidates dependent objects (lets say trigger). So on re-creating the table just re-compiling trigger should be enough to make the trigger valid right? But from doc it says we need to re-create trigger.

Also I tried to a little analysis and found out on dropping a table its dependent trigger is getting dropped (Shouldn't it just get invalidated as per doc)

 create table myteste1 (num int not null primary key);

Table MYTESTE1 created.

 CREATE trigger mytrigger1
 after update on myteste1
 begin
 dbms_output.put_line('mytrigger1 called' );
 end;

Trigger MYTRIGGER1 compiled

SELECT * FROM user_objects where object_name=upper('mytrigger1');

Query Result 

Object_name |.....
-------------------
MYTRIGGER1  |......

drop table myteste1;

Table MYTESTE1 dropped.

SELECT * FROM user_objects where object_name=upper('mytrigger1');

Query Result 

Object_name |.....
-------------------
Ben
  • 51,770
  • 36
  • 127
  • 149
sql_dummy
  • 715
  • 8
  • 23
  • Yup, you need to recreate the trigger per the documentation and your tests. Thank you for testing! – Ben Jan 05 '18 at 16:55
  • 5
    You (or I ?) may be confused by the meaning of "dependent". Usually a trigger is on a table, and it may read info from or perform DML on **other** tables. If the table on which the trigger is on is deleted, then the trigger is deleted too (the same as indexes on the table, etc.) On the other hand, the trigger is "dependent on" the tables it references - **not** the table it is a trigger on. So if you had an auditing table, into which the trigger on **your** table inserted rows when you perform actions on **your** table, the trigger would be invalidated if you deleted the **audit** table. –  Jan 05 '18 at 17:04
  • The trigger is dependent on the table's existence @mathguym. "Dependent" is being used as the English word rather than a description of Oracle dependencies. – Ben Jan 05 '18 at 17:42
  • 1
    @Ben - I don't get that impression from the quoted fragment from the Documentation. Imagine a production table and an audit table. The production table has a trigger to insert into the audit table. The trigger is **for** the production table, and it is **dependent on** the audit table. If the production table is deleted, the second sentence from the fragment applies (about "triggers for the table"), while the first sentence does not apply to that trigger. If the audit table is deleted, only the first sentence applies to the trigger on the prod table - the trigger is not "for" the audit table. –  Jan 05 '18 at 17:50
  • 2
    If you keep reading the rest of the documentation page past the first few paragraphs, they explain in great detail exactly what Oracle does when you drop a table. "All table indexes and domain indexes are dropped, as well as any triggers defined on the table, regardless of who created them or whose schema contains them." https://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9003.htm#SQLRF01806 – kfinity Jan 05 '18 at 19:02
  • I was slightly inexact @mathguy, my apologies. I was describing what the OP meant by dependent rather than the fragment of the Oracle documentation. You're (obviously) completely correct in your understanding of the quoted bit of text. – Ben Jan 05 '18 at 23:34

0 Answers0