[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

CAPTURE TRIGGER COUNTS ON DML OPERATIONS

Posted on 2007-04-03
6
Medium Priority
?
1,379 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 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

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

650 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