CODEWITHSUNDEEP
oracle-databasesecurityplsql
k200228 Ismail Ahmed Ansari
k200228 Ismail Ahmed Ansari

Reputation: 31

How to get the table name in trigger on which ddl operation will be performed

I want to do something like

create or replace trigger t1 before ddl on database
begin
insert into table1 values(//the table name on which the ddl will be performed);
end;

so if i create a table named "Hello" than "Hello"(table name) will be inserted in table1

so i don't know how to fetch the table name actually

Upvotes: 2

Views: 175

Answers (1)

thatjeffsmith
thatjeffsmith

Reputation: 22427

That's show here in the Database Docs

So you could do something like...

CREATE OR REPLACE TRIGGER ddl_trig
AFTER DDL
ON DATABASE
BEGIN
  INSERT INTO loguser.ddl_log
  (user_name, ddl_date, ddl_type,
   object_type, owner,
   object_name)
  VALUES
  (ora_login_user, SYSDATE, ora_sysevent,
   ora_dict_obj_type, ora_dict_obj_owner,
   ora_dict_obj_name);
END ddl_trig;
/

BUT, you shouldn't build your own auditing software/code. You should instead use the database's built in auditing system.

See this.

Disclaimer: I work for Oracle and am a product manager on the database team.

Upvotes: 5

Related Questions