DDL Triggers Oracle

DML triggers explained in another post DML Triggers

DDL Triggers are triggers which associated with DDL (Data Definition Language) such as Dropping a table, Altering a column  etc. DDL triggers execute every time a DDL statement is executed. Generally DBA’s create DDL triggers for auditing and enforcement purposes.

General Syntax

create or replace trigger
DDLTrigger_name
AFTER DDL/LOGON/LOGOFF ON DATABASE/SCHEMA
BEGIN

— code here —

END;
/

Example :-

1) Connect to system

2)

create or replace trigger
ddl_trigger1
after DDL on DATABASE
begin
insert into ddl_table values (
ora_dict_obj_name || ‘-‘||
ora_login_user || ‘-‘||
sysdate || ‘-‘||
ora_sysevent || ‘-‘||
ora_dict_obj_type || ‘-‘||
ora_dict_obj_owner || ‘-‘||
ora_dict_obj_name
);
end;
/

Note :- ddl_table is a user defined table which is in DBA schema.

Within the database any DDL issued, details will be saved into ddl_table table.

similarly DDL trigger can be created within schema also.

1) Connect to system

2)

create or replace trigger
ddl_trigger1
after DDL on SCHEMA
begin
insert into ddl_table values (
ora_dict_obj_name || ‘-‘||
ora_login_user || ‘-‘||
sysdate || ‘-‘||
ora_sysevent || ‘-‘||
ora_dict_obj_type || ‘-‘||
ora_dict_obj_owner || ‘-‘||
ora_dict_obj_name
);
end;
/

DDL Triggers for LOGON/LOGOFF

LOGON

create or replace trigger
ddl_trigger3
after LOGON on database
begin
insert into ddl_table values (
ora_dict_obj_name || ‘-‘||
ora_login_user || ‘-‘||
to_char(sysdate,’dd-mon-yyyy hh:mi am’) || ‘-‘||
ora_sysevent || ‘-‘||
ora_dict_obj_type || ‘-‘||
ora_dict_obj_owner || ‘-‘||
ora_dict_obj_name
);
end;
/

LOGOFF

create or replace trigger
ddl_trigger3
after LOGOFF on database
begin
insert into ddl_table values (
ora_dict_obj_name || ‘-‘||
ora_login_user || ‘-‘||
to_char(sysdate,’dd-mon-yyyy hh:mi am’) || ‘-‘||
ora_sysevent || ‘-‘||
ora_dict_obj_type || ‘-‘||
ora_dict_obj_owner || ‘-‘||
ora_dict_obj_name
);
end;
/

6 responses to “DDL Triggers Oracle”

  1. This website very useful to me.definations are very simple and easily understand anybody

      1. Keep it up . You r doing great work.

      2. Thank you for this notes….

    1. Keep it up . You r doing great work.

    2. Thank you for this notes….

  2. Thank you for this notes….

  3. This web site very useful for me.

  4. I want collections and collections Sample query

  5. Keep it up . You r doing great work.