?
Solved

CAPTURE TRIGGER COUNTS ON DML OPERATIONS

Posted on 2007-04-03
6
Medium Priority
?
1,383 Views
Last Modified: 2013-12-18
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 ...
0
Comment
Question by:RND2006
  • 2
  • 2
4 Comments
 
LVL 28

Accepted Solution

by:
Naveen Kumar earned 2000 total points
ID: 18843444
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
 

Author Comment

by:RND2006
ID: 18843818
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
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 18843854
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
 

Author Comment

by:RND2006
ID: 18843945
I appreciate your answer and thank you very much for your immediate response.
0

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question