1

I wrote the following trigger in Oracle:

create or replace trigger foreign_drop before ddl on database
begin
  insert into ddl_log
  select ora_sysevent, ora_dict_obj_owner, ora_dict_obj_name, null, user, 
sysdate
  from dual;
end foreign_drop;

It fires whenever a DDL event happens. But when I drop a foreign key constraint, nothing is recorded. Is dropping a foreign key constraints considered an event? How could I record the action of dropping a foreign key constraint?

2

The following trigger captures the drop constraint operation.

create or replace trigger foreign_drop after create or alter or drop on database
begin
  dbms_output.put_line('DDL Fired!');
end foreign_drop;
/
SQL> set serveroutput on
SQL> CREATE TABLE t2
( id number,
  CONSTRAINT fk_id
    FOREIGN KEY (id)
    REFERENCES t1(id)
);  2    3    4    5    6  
DDL Fired!

Table created.

SQL> alter table t2 drop constraint fk_id;
DDL Fired!

Table altered.
2

How could I record the action of dropping a foreign key constraint?

Instead of reinventing the wheel, use the built-in support for auditing.

Dropping a constraint is an ALTER TABLE statement. So enable auditing and audit ALTER TABLE statement as:

audit alter table;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.