You Asked

Hi Tom,

I have a requirement where I have to create a table level trigger dynamically.

Here are more details :

A user will be provided an option to choose the columns in a table XYZ which will be tracked for any changes - inserting, updating and deleting. The code behind the scene will use these columns that the user has chosen and create a database trigger on that table. A log table will start logging any deletions, insert, updates on these columns in table XYZ.

I appreciate your insight and suggestions on the above.

Regards & Thanks

and Connor said...

It's easy enough to dynamically create a trigger using 'execute immediate', but I suppose the question is:

- what if user A says "Audit columns X, Y"
- what if user B says "Audit columns Y, Z"
- what if user C says "Audit no columns"

Are you going to change/drop/recreate the trigger every time ? It seems to me that this should only ever be a one time operation, that is, once the trigger is on, it must stay on, otherwise you will be jeopardising audit requests made in the past.

In which case, you might want to consider logging *all* columns in a trigger, no matter what someone asks for ?

Rating

(1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

SAK

SAK, August 10, 2016 - 1:56 pm UTC

Thank you very much for your reply.

I agree as you mentioned that the issue of different users asking for different columns to be audited is a concern. We are working on how best to address this concern.

Appreciate your help,

Regards

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

SAK

More to Explore

PL/SQL demos

PL/SQL docs

Create a database trigger (Dynamic) on a table

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

SAK

More to Explore

PL/SQL demos

PL/SQL docs