怎样在DDL触发器中获得drop column的类型

Question

CREATE OR REPLACE TRIGGER TRG_ALTER_TABLE BEFORE ALTER ON DATABASE
BEGIN
  ......
END TRG_ALTER_TABLE;怎样在触发器中获得删除列的信息例：
CREATE TABLE TEST
(
  ID   NUMBER,
  NAME VARCHAR2(30)
)执行ALTER TABLE TEST DROP COLUMN NAME;后怎样获得TEST.NAME的类型及长度等信息

Answer 1

--获取列的类型和长度
SELECT COLUMN_NAME,DATA_TYPE FROM USER_TAB_COLUMNS 
WHERE TABLE_NAME='EMP' --表名要大写

Answer 2

1：我是在删除前获得属性信息，便于记录
2：在某些条件不满足情况下不允许删除
3：SELECT COLUMN_NAME,DATA_TYPE FROM USER_TAB_COLUMNS WHERE TABLE_NAME='EMP';
  即便没有drop掉在触发其中照样的没有该列信息

Answer 3

自己体会下.字段的类型存储在data_type字段,长度及精度信息有不同的字段存储.
 
SQL> desc emp
Name     Type         Nullable Default Comments 
-------- ------------ -------- ------- -------- 
EMPNO    NUMBER(20)                             
ENAME    VARCHAR2(10) Y                         
JOB      VARCHAR2(9)  Y                         
MGR      NUMBER(4)    Y                         
HIREDATE DATE         Y                         
SAL      NUMBER(7,2)  Y                         
COMM     NUMBER(7,2)  Y                         
DEPTNO   NUMBER(2)    Y                         
 
SQL> select t.COLUMN_NAME,t.DATA_TYPE,t.DATA_LENGTH,t.DATA_PRECISION,t.DATA_SCALE  from user_tab_columns t where t.TABLE_NAME='EMP';
 
COLUMN_NAME                    DATA_TYPE                                                                        DATA_LENGTH DATA_PRECISION DATA_SCALE
------------------------------ -------------------------------------------------------------------------------- ----------- -------------- ----------
EMPNO                          NUMBER                                                                                    22             20          0
ENAME                          VARCHAR2                                                                                  10                
JOB                            VARCHAR2                                                                                   9                
MGR                            NUMBER                                                                                    22              4          0
HIREDATE                       DATE                                                                                       7                
SAL                            NUMBER                                                                                    22              7          2
COMM                           NUMBER                                                                                    22              7          2
DEPTNO                         NUMBER                                                                                    22              2          0
 
8 rows selected
 
SQL> 

Answer 4

number要查看DATA_PRECISION,DATA_SCALE两个字段.date只要看data_type就可以了.
varchar2只要看data_length就可以了.其他情况参照上面的去研究下就可以了.

Answer 5

虽然是Before触发器，但是执行
  ALTER TABLE TEST DROP COLUMN NAME;
时使用select t.COLUMN_NAME,t.DATA_TYPE,t.DATA_LENGTH,t.DATA_PRECISION,t.DATA_SCALE  from user_tab_columns t where t.TABLE_NAME='EMP';并不能获得所要的信息该列在user_tab_columns（dba_tab_columns）中已经不存在了

Answer 6

没发现你说的情况啊.
SQL> CREATE OR REPLACE TRIGGER T_DROP_AVOID
  2    BEFORE DROP OR ALTER on schema
  3  
  4  declare
  5    drop_column varchar2(30);
  6  BEGIN
  7  
  8    IF (ora_sysevent = 'ALTER' AND ora_dict_obj_type = 'TABLE') THEN
  9  
 10      insert into t_col
 11      select t.COLUMN_NAME,sysdate  from user_tab_columns t where t.TABLE_NAME='EMP1';
 12  
 13    END IF;
 14  
 15  END;
 16  /
 
Trigger created
 
SQL> desc t_col
Name Type          Nullable Default Comments 
---- ------------- -------- ------- -------- 
NAME VARCHAR2(100) Y                         
TIME DATE          Y                         
 
SQL> drop table emp1;
 
Table dropped
 
SQL> create table emp1 as select * from emp;
 
Table created
 
SQL> truncate table t_col;
 
Table truncated
 
SQL> alter table emp1 drop (empno);
 
Table altered
 
SQL> select * from t_col;
 
NAME                                                                             TIME
-------------------------------------------------------------------------------- -----------
EMPNO                                                                            2010-11-12
ENAME                                                                            2010-11-12
JOB                                                                              2010-11-12
MGR                                                                              2010-11-12
HIREDATE                                                                         2010-11-12
SAL                                                                              2010-11-12
COMM                                                                             2010-11-12
DEPTNO                                                                           2010-11-12
 
8 rows selected
 
SQL>