Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Can anyone please tell me what's going on here:

CREATE TRIGGER afr_alt_trig AFTER ALTER ON SCHEMA 
 cols ora_name_list_t; 
BEGIN 
 IF (ora_dict_obj_type = 'TABLE') THEN 
  select column_name bulk collect into cols from user_tab_cols where table_name =   ora_dict_obj_name; 
  FOR i IN cols.first .. cols.last LOOP 
   dbms_output.put_line(cols(i)); 
  END LOOP; 
 END IF; 
END; 

The above trigger is expected to display the list of all the columns in a table after the ALTER TABLE command is executed.

The following works just fine when I ALTER a table with column(s) without any constraints:

Alter Table tab_xxx ADD(nucol char);

However, if I add a new column(s) with a constraint like

ALTER TALE tab_xxx ADD(n number NOT NULL); 

then this new column(s) is not immediately reflected in the USER_TAB_COLS DD view and the FOR loop above to display the names of all the columns DOESN'T show this NEW column added.

Thanks already.

share|improve this question
    
It looks like the trigger fires while the DDL is not completely finished. This seems to happen if you add any constraint with a column. I have no idea why. –  Vincent Malgrat Nov 26 '13 at 16:04
    
Exactly. And to add to it. If the column has a constraint(any) it seems the DD views(user_tab_cols, user_constraints) is not updated immediately. And, if the constraint is either a Primary Or Unique which results in the creation of an Index, the DD view User_indexes gets updated immediately with the entry of this new index for the PK or Unique key, however, if you go check the user_ind_columns for the columns on which the index has been created... the info is not available there. –  OraNewUser Nov 27 '13 at 9:22

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.