Link to home
Start Free TrialLog in
Avatar of David VanZandt
David VanZandtFlag for United States of America

asked on

Need to create DML / CDC triggers dynamically

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.
SOLUTION
Avatar of flow01
flow01
Flag of Netherlands image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of David VanZandt

ASKER

Thank you, I'll follow up with you later this week.
slight, pretty slick :)  I had to replace PCTFREE with SEGMENT, and figure out how to append the DDL string with the execute delimiter, but that was wonderful -- and a challange to figure out.

flow, I need to take you up on that additional coaching for the trigger.  I've attached my WIP, and it comes down to parsing the :old row, plus WHO columns, into the AUD table together.  What I didn't make clear, perhaps, was that this "agile" process needs to be reusable for say, 10 schemas, each having 50 tables.  I can handle doing the schemas one after the other, but the trigger with types eludes me.  TIA.
dvz-audit-20120703-draft.sql
TR-MYTABLE1.sql
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
As slightwv mentioned already the kind of audit but it also depends on your application:
if there are more then 1 changes of a row within one commit (update-update or update-delete)  you will get twice the same data (except for the type) in your audit-record.
Courtesy update, I'll post my code and split points in a day or two.
I'll award points and close the question, but you're invited to look over my attached solution.  Thanks again for the help!
bmi-ora-gen-aud-table.txt
bmi-ora-gen-aud-trigger.txt
Thank you both!
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Glad to help.  This one was fun!