scheduling ddl in a trigger
726603Mar 22 2011 — edited Mar 25 2011Hi,
I'm trying to find a way to schedule DDL statements from Oracle triggers.
As one example, we keep INDEX metadata in our own IndexMetadata table.
I'm trying to create a row delete trigger on this table that will schedule a (dynamic) DROP INDEX statement when the metadata row is deleted.
An old post by Tom Kyte suggests using the dbms_job package to this end, but it seems to have no effect for me (on 11g), and the documentation says this package has been superseded by dbms_scheduler.
However, if I try to create a DDL job using dbms_scheduler in my trigger, it fails at runtime.
I DO NOT want to use an autonomous transaction for this purpose, as that could result in the physical index being dropped even if the metadata delete gets rolled back.
Suggestions for the best way(s) to handle these issues appreciated,
Ben