CAPTURE TRIGGER COUNTS ON DML OPERATIONS

A trigger is fired for an INSERT/UPDATE/DELETE statement and an Audit Table gets inserted with a row for each of the changes that occurred.
I will need to validate the auditing process is executing correctly or not by writing simple SQL statements to compare.
Other than that can you please let me know if I can write a SQL query to find out how many times the trigger actually fired on a given table and a time period? OR is there a better way to say that
the trigger has executed correctly and the audit table has been populated with the data correctly.
Are there any Oracle functions to track the trigger fire counts on DML operations?
Something related to these areas for validating ...
RND2006Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Naveen KumarProduction Manager / Application Support ManagerCommented:
A) let us say for an example, we want to validate insert's first.

1) count the number of records in the actual table first between whatever days you want by using date fields in the where clause.

2) find the count of records in the audit table for that peiod. It should match. This is a blind method.

If you want to check even the values which are in the audit table with the actual values in the table, then writing pl/sql procedure/code will be good for this.

B) for updates you have write pl/sql code to verify the field values with some key value being present in the audit table as well so that you can use this key to query the main table for the field values.

C) for deletes, if you have some backup table for the original table then find the count of deleted records from the audit table and then try to find those records in the current table and they should not be there but should be present in the backup table and the count should match.

Thanks
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RND2006Author Commented:
nav_kum_v:
Yes, the approach is something that I already have in my mind. But, I wanted to know something about the trigger(s) being used and how to trap the hits. Is there way I can find the number of times the trigger has actually fired?
I would appreciate if you could give me more insight into this area.
Thank you.
0
Naveen KumarProduction Manager / Application Support ManagerCommented:
again this will be like auditing only, you should have some table with one record for insert and one record for update and one record for delete.

Whenever the trigger fires, we can update the record which belongs to insert or update or delete. I mean it is again user defined logic to trap the number of hits also.

I don't think there is some way/procedure/function given by oracle to find out how many times a particular trigger has fired.

Thanks
0
RND2006Author Commented:
I appreciate your answer and thank you very much for your immediate response.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.