Create Audit Tables
Create a table to store audit logs for all changes. The audit table could have fields like:
AuditID (Primary Key)
TableName
OperationType (Insert/Update/Delete)
OldData (for Update/Delete operations)
NewData (for Insert/Update operations)
ChangedBy (User performing the operation)
Timestamp
RowID or PrimaryKeyValue (to reference the affected row in the original table)
Here's an example SQL for the audit table
CREATE TABLE AuditLogs (
AuditID NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
TableName VARCHAR2(100),
OperationType VARCHAR2(10),
OldData CLOB,
NewData CLOB,
ChangedBy VARCHAR2(100),
Timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
RowID VARCHAR2(100) -- This can reference the affected row
);
Create Triggers for Each Table:
You can create AFTER INSERT, UPDATE, DELETE triggers on each table you want to audit. In the trigger, you’ll insert a record into the AuditLogs table whenever there is a CRUD operation on the target table.
Here’s an example of a trigger for the Employees table to log changes for INSERT, UPDATE, and DELETE operations:
After Insert Trigger (Capture inserted data)
CREATE OR REPLACE TRIGGER AuditEmployeesInsert
AFTER INSERT ON Employees
FOR EACH ROW
BEGIN
INSERT INTO AuditLogs (TableName, OperationType, NewData, ChangedBy, RowID)
VALUES (
'Employees',
'INSERT',
TO_CLOB(:NEW), -- Store the entire new record as CLOB
USER,
:NEW.EmployeeID
);
END;
/
After Update Trigger (Capture old and new data)
CREATE OR REPLACE TRIGGER AuditEmployeesUpdate
AFTER UPDATE ON Employees
FOR EACH ROW
BEGIN
INSERT INTO AuditLogs (TableName, OperationType, OldData, NewData, ChangedBy, RowID)
VALUES (
'Employees',
'UPDATE',
TO_CLOB(:OLD), -- Store the old record as CLOB
TO_CLOB(:NEW), -- Store the new record as CLOB
USER,
:NEW.EmployeeID
);
END;
/
After Delete Trigger (Capture deleted data)
CREATE OR REPLACE TRIGGER AuditEmployeesDelete
AFTER DELETE ON Employees
FOR EACH ROW
BEGIN
INSERT INTO AuditLogs (TableName, OperationType, OldData, ChangedBy, RowID)
VALUES (
'Employees',
'DELETE',
TO_CLOB(:OLD), -- Store the deleted record as CLOB
USER,
:OLD.EmployeeID
);
END;
/
These triggers will automatically insert a record into the AuditLogs table every time a record is inserted, updated, or deleted in the Employees table.
Track User Information
The USER function in Oracle will capture the current database user who made the change. If you need to capture more detailed information like the actual ASP.NET user (for instance, if users log in through a web interface), you can modify the trigger or pass the user from your application code.
For example, you can pass the user from your ASP.NET code when performing CRUD operations:
string currentUser = HttpContext.Current.User.Identity.Name; // ASP.NET identity user
// When performing an insert/update, pass this user to the stored procedure or trigger
You can modify the triggers or store this in the ChangedBy column by setting the value from your application logic or even using a session variable.
Store Full Record in Audit Log
In the example above, I used TO_CLOB(:OLD) and TO_CLOB(:NEW) to store the entire row data as CLOB (Character Large Object). This allows you to store the old and new values in a structured format, which you can later parse for analysis. You can also choose to store the individual field values instead of the full row, depending on your requirements.
Managing Large Data:
If the tables you are auditing contain large rows, you might need to implement efficient methods of storing and retrieving large CLOB data.
You can split the audit log into multiple tables (by year, month, etc.) for performance reasons or archive old logs periodically.
Using Stored Procedures (Optional)
If you prefer, you can encapsulate the audit logging in a stored procedure that is called from the trigger instead of directly within the trigger itself. This can be useful for centralized logging logic and to ensure the consistency of your audit trail.
Example of a stored procedure for logging
CREATE OR REPLACE PROCEDURE LogAudit (
p_table_name VARCHAR2,
p_operation_type VARCHAR2,
p_old_data CLOB,
p_new_data CLOB,
p_user VARCHAR2,
p_row_id VARCHAR2
) IS
BEGIN
INSERT INTO AuditLogs (TableName, OperationType, OldData, NewData, ChangedBy, Timestamp, RowID)
VALUES (p_table_name, p_operation_type, p_old_data, p_new_data, p_user, CURRENT_TIMESTAMP, p_row_id);
END;
/
Then in the trigger, you can call this procedure.
CREATE OR REPLACE TRIGGER AuditEmployeesUpdate
AFTER UPDATE ON Employees
FOR EACH ROW
BEGIN
LogAudit(
'Employees',
'UPDATE',
TO_CLOB(:OLD),
TO_CLOB(:NEW),
USER,
:NEW.EmployeeID
);
END;
/
Performance: Triggers add overhead to DML operations, so you should ensure that the performance impact is acceptable for your application, especially if the tables being audited are large or undergo frequent changes.
Handling Bulk Operations: If you are performing bulk inserts, updates, or deletes, consider handling them outside of the trigger scope to prevent performance bottlenecks.
Error Handling: Ensure error handling is in place in the triggers and your application to handle any potential issues with logging operations.
Security: Ensure that only authorized users or roles can modify the audit logs (e.g., don't allow unauthorized users to delete or update records in the AuditLogs table).
Retention Policy: Depending on your compliance requirements, you may need to implement a retention policy for your audit logs (e.g., log deletion after a certain period).
To integrate the audit functionality into your ASP.NET application, you can ensure that all CRUD operations pass the correct user information (either through the database user or application-specific user) and log meaningful details. You can also create a UI for administrators to view the audit logs directly from the application.