troubleshooting Question

Need to create DML / CDC triggers dynamically

Avatar of David VanZandt
David VanZandtFlag for United States of America asked on
Oracle Database
11 Comments4 Solutions1024 ViewsLast Modified:
Here's a head-scratcher:  For all tables in a given schema, I need to duplicate each table (DDL)  for a trigger-based change capture.  For example, DEPT_AUD is created from DEPT using the dbms_metadata function.  Standard WHO columns are appended, and a column holding the DDL type (I, U, D).

I can manually map a row-based trigger on DEPT. When updating or inserting or deleting, the table's OLD columns are inserted into DEPT_AUD with the appropriate key.  If the DEPT table has n number of columns, the CREATE TRIGGER statement will also require n number of inserted columns (plus the WHO, lest we forget).

For a few tables, the manual approach may be simplest.  I've thought of dynamic SQL using user_tab_cols, but I can't figure out how to loop through the variable number of columns for each table.  My guess is that row_types would somehow eliminate the kludge mapping,  but that's an area where I'm ignorant.

For the record, Golden Gate, Logminer, and similar log-based CDC mining are off the table.  Don't bother suggesting them.  New 11g functions are okay.  Secondly, I'm only interested in capturing the :old data.  Thirdly, functionality takes priority over performance.  Lastly, this audit requirement touches several thousand DW tables.

Thanks in advance, and I'm looking forward to a lively discussion.
Join our community to see this answer!
Unlock 4 Answers and 11 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 4 Answers and 11 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros