Avatar of David VanZandt
David VanZandt
Flag 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.
Oracle Database

Avatar of undefined
Last Comment
slightwv (䄆 Netminder)

8/22/2022 - Mon
SOLUTION
flow01

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
SOLUTION
slightwv (䄆 Netminder)

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
David VanZandt

ASKER
Thank you, I'll follow up with you later this week.
David VanZandt

ASKER
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
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER CERTIFIED SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
flow01

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.
Your help has saved me hundreds of hours of internet surfing.
fblack61
David VanZandt

ASKER
Courtesy update, I'll post my code and split points in a day or two.
David VanZandt

ASKER
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
David VanZandt

ASKER
Thank you both!
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
slightwv (䄆 Netminder)

Glad to help.  This one was fun!