0

I am searching for a way, to store only the tables and columns that are added in the database with the one who created it (nachine_name) in a log table.

I tried to add a trigger on sys table user_tab_cols but I cannot do that Why cannot I create triggers on objects owned by SYS?

The system table user_objects will give me the date when a table created, but I want also to know which machine created it. and I also want to track the column creation and modification and log them in a table.

Is that possible ? is there a way for that ?

Community
  • 1
  • 1
Moudiz
  • 7,211
  • 22
  • 78
  • 156

1 Answers1

1

you can create a database event trigger:

CREATE OR REPLACE TRIGGER log_ddl_event_trg
  AFTER DDL
  ON DATABASE
DECLARE
  v_sql_list  ora_name_list_t;
  v_sql_txt   VARCHAR2(2500);
BEGIN
  FOR i in 1..ORA_SQL_TXT(v_sql_list) LOOP
    v_sql_txt := v_sql_txt || v_sql_list(i);
    EXIT WHEN length(v_sql_txt ) >= 2000;
  END LOOP;

 ...
END;
/

in the Trigger, you can get the executed ddl-statement using the ORA_SQL_TXT() Funktion, and then log it in the table together with the other data (log_date, user etc.).

schurik
  • 7,798
  • 2
  • 23
  • 29
  • can i do it on dml also ? – Moudiz Sep 27 '16 at 08:37
  • dml is insert / update/delete. you can certainly create a trigger on insert or update or delete, but you have asked how to log "the tables and columns that are added in the database" – schurik Sep 27 '16 at 08:51
  • can i add create a tirgger on insert or update on all the database ?. btw while checking I noticed It doenst log the drop statements only create and alter – Moudiz Sep 27 '16 at 08:53
  • as far as i know, it's not possible to create an insert or update Trigger on database, only on table or view – schurik Sep 27 '16 at 09:02