Audit Trail Logic

Creating an audit trail in SQL involves capturing and recording changes made to the data in a database, along with metadata like who made the change, when it was made, and what the original and new values were. Here are some common methods to create an audit trail in SQL:

### 1. *Using Triggers*
Triggers are a common way to create an audit trail in SQL. A trigger can be set up to fire before or after an INSERT, UPDATE, or DELETE operation. The trigger then logs the changes into an audit table.

#### Example:
Suppose you have a table named employees and you want to audit changes.

1. *Create an Audit Table:*
    sql
    CREATE TABLE employees_audit (
        audit_id INT AUTO_INCREMENT PRIMARY KEY,
        employee_id INT,
        action VARCHAR(10),
        old_value VARCHAR(255),
        new_value VARCHAR(255),
        changed_by VARCHAR(50),
        changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    );
    

2. **Create a Trigger for UPDATE:**
    sql
    CREATE TRIGGER employees_update_audit
    AFTER UPDATE ON employees
    FOR EACH ROW
    BEGIN
        INSERT INTO employees_audit (employee_id, action, old_value, new_value, changed_by)
        VALUES (
            OLD.employee_id, 
            'UPDATE', 
            OLD.salary, 
            NEW.salary, 
            USER()
        );
    END;
    

This trigger captures changes made to the salary column and stores the old and new values, the action performed, who performed the action, and when.

3. **Create Triggers for INSERT and DELETE:**
    Similar triggers can be created for INSERT and DELETE operations.

### 2. *Change Data Capture (CDC)*
If your database supports Change Data Capture (CDC), you can enable this feature to automatically track changes without creating manual triggers.

- *SQL Server Example:*
    sql
    EXEC sys.sp_cdc_enable_table 
        @source_schema = 'dbo', 
        @source_name = 'employees', 
        @role_name = NULL;
    

- *Oracle Example:*
    Oracle has its own built-in auditing features that can be configured via DBMS packages or directly using the AUDIT command.

### 3. *Manual Logging*
If you want to avoid using triggers, you can manually log changes by writing data into an audit table within your SQL operations.

#### Example:
sql
UPDATE employees 
SET salary = 60000 
WHERE employee_id = 101;

INSERT INTO employees_audit (employee_id, action, old_value, new_value, changed_by)
VALUES (101, 'UPDATE', '50000', '60000', 'admin');


### 4. *Temporal Tables*
Some databases like SQL Server 2016+ support temporal tables, which automatically keep track of historical data. You can enable a table to be temporal, and SQL Server will automatically manage the history.

#### Example:
sql
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    salary INT,
    SysStartTime DATETIME2 GENERATED ALWAYS AS ROW START,
    SysEndTime DATETIME2 GENERATED ALWAYS AS ROW END,
    PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime)
) WITH (SYSTEM_VERSIONING = ON);


### Best Practices:
- *Minimal Performance Impact:* Triggers can introduce performance overhead, so ensure they're optimized and only track necessary data.
- *Data Integrity:* Ensure that the audit trail can't be easily tampered with by using appropriate permissions.
- *Compliance:* Ensure that your audit trail complies with legal and regulatory requirements for data retention and privacy.

By implementing one or more of these methods, you can effectively create an audit trail in SQL to track changes in your database.