09. Auditing technique

-

 

Auditing technique



Note on Using Triggers for Auditing

When we handle auditing through triggers in the database, those triggers still run inside the same transaction started by our application. 

That means: Even though the trigger is in the database and our query is from the application, both use the same transaction. So, if something goes wrong and the transaction is rolled back, both the main query and the trigger's audit logic will be rolled back together.

From a performance perspective, it's often better to use an AFTER trigger for auditing (instead of a BEFORE trigger). This way:

  • The database first completes the main query,
  • Then writes to the audit table,
  • All within the same transaction.

This approach keeps our application code clean and can be slightly more efficient since the audit work is handled by the database directly

Comments

Post a Comment

Popular posts from this blog

Database - Topics

-
Image
 Topics  01. Steps in SQL Query Execution 02. How does the database create a query plan 03. How Query Plan Caching Works 04. What is the need for an execution plan? 05. How Do Query/ Query Plan Execute (Inside MySQL Server) 06. Importance Of Undo Log 07. How Row Lock Is Handle When Query On The Table Row 08. Technique to recover the committed changes 09. Auditing technique 10. Query cache 11. DB connection pool 12. What is ACID? 13. Optimizing SQL queries
Read more

02. Spring – Creating spring project clone it with GIT step by step.

-
Image
  Creating spring project clone it with GIT step by step. 01. Create an empty GIT repository and select a repository name. 02.   Check to add README.md and technology to add it inside the gitignire file0- it will include all the generated class when we complete the application. 03.      Copy the github repository URL. 04 . Open eclipse and go to,             Window – Show View – Other - Type git on the folder and select git repositories. 05.   Now clone this repository at your desired location in which we are going to keep the project specific file . a)         a)  Step One.             b)  Step  Two .          c)    Step Three. Both projects are generated as bellows           d) Step Four  Open the gitingnore file and ignore the target folder of the project. All the class files which are ...
Read more

01. Steps in SQL Query Execution (MySQL)

-
Image
Steps in SQL Query Execution (MySQL) 1. Client Sends Query to Server The SQL statement is sent to the MySQL server over a connection (e.g., via JDBC or a query tool like MySQL Workbench). 2. Parser / Syntax Check The parser checks the SQL syntax.If there are any syntax errors, it throws an error immediately. 3. Pre-processor Verifies privileges and permissions for the user executing the query. Resolves object names (like table or column aliases). 4. Query Optimization The optimizer evaluates multiple strategies to execute the query. It considers: Which indexes to use. Join order (for multi-table queries). Whether to use temporary tables. Cost of different execution paths. The optimizer chooses select the most efficient query plan (Cost effective -> memory, CPU etc). 5. Query Execution Plan (Most cost effective query plan) The query plan is a blueprint of how MySQL will fetch the data. You can view this with the EXPLAIN keyword in front of your query. 6. Query Execution The storage e...
Read more