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;
/
This website very useful to me.definations are very simple and easily understand anybody
Thanks
Thank you for this notes….
This web site very useful for me.
I want collections and collections Sample query