Solved

CAPTURE TRIGGER COUNTS ON DML OPERATIONS

Posted on 2007-04-03
6
1,348 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
6 Comments
 
LVL 28

Accepted Solution

by:
Naveen Kumar earned 500 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
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.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

920 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now