RE: Data auditing: triggers vs application code

And how will the application code guarentee that they will audit changes made to the table via some other mechanism? (eg sqlplus) Answer: They can't. The trigger getting disabled is a security thing. Don't allow that many peoiple the ability to disable the trigger. Someone did. Someone could change the Java code or write Java code in the application that went around the trigger. How is that any more secure than in the database next to where the change is? (it isn't, it is less secure.) With thousands of line of Java code how will you know what sections aren't audited and should be vs which are? The trigger is the most performant since you have to have the information. Jim

From: oracle-l-bounce_at_freelists.org on behalf of Sandeep Dubey Sent: Wed 9/21/2005 1:36 PM
To: Igor Neyman
Cc: oracle-l
Subject: Re: Data auditing: triggers vs application code

Igor- Application will be based on J2EE. Delete to a live table and insert to audit table will be part of one single transaction one thread. I don't think it will be an extra trip. Moreover the point being putforward is we will deploy multiple application servers (10s or more) to do scaling.

Here back in past the trigger got somehow disabled - packaged called from trigger was invalidated, later fixed the packge but could not check the triggers. Application ran fine without complaining only later to found no audit records. I think that mindset of application architect is pushing against going with triggers. It's not though easy to disable the part og Java code that does the audit -you need to actually go and remove the code portion.  

Stephane - Thanks for your comments  

Thanks, Sandeep  

On 9/21/05, Igor Neyman <ineyman_at_perceptron.com> wrote:

	Regarding your 2nd point:
	In high transaction environment making extra trip from application to database to log data in adit table will cause more overhead than using trigger to populate audit table. 
	 
	Now, if you are afraid that someone could disable auditing triggers, aren't you afraid that someone could disable "auditing part" of application? 
	 
	Igor Neyman

	 


________________________________


	From: oracle-l-bounce_at_freelists.org [mailto: oracle-l-bounce_at_freelists.org <mailto:oracle-l-bounce_at_freelists.org> ] On Behalf Of Sandeep Dubey
	Sent: Wednesday, September 21, 2005 3:52 PM
	To: oracle-l
	Subject: Data auditing: triggers vs application code
	
	 
	
	Hi,
	 
	I need to keep track of history of data change in the live tables. Two methods are being compared.
	 
	1. Create triggers on the live table. For each insert/update/delete insert a row in the audit table. 
	2. Let the application take care of inserting the data in the audit table itself.
	 
	The cons against using triggers in the order of severity are:
	 
	1. If somehow the triggers are disabled in production, the application goes through without noticing it and no audit data will be captured.
	2. In a high transaction environment triggers have overhead.
	 
	As a database person, I am inclined to use triggers. But I fail to guarantee that trigger will never get disabled. If it is disabled somehow application SHOULD stop. (It is impractical to check the status of all underlying triggers before each transaction). 
	 
	I would like to hear how you guys handle data auditing  in your system.
	 
	Thanks
	 
	Sandeep
	 

--
http://www.freelists.org/webpage/oracle-l