Solved

CAPTURE TRIGGER COUNTS ON DML OPERATIONS

Posted on 2007-04-03
6
1,370 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to recover a database from a user managed backup

696 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