42. Compliance & Audit Scenarios -- Regulatory Requirements

Level: Advanced SQL Server for real projects

ℹ️ What You'll Learn

• Comprehensive audit logging
• Data retention policies
• Regulatory compliance tracking
• Privacy and data protection
• Audit trail querying and reporting
• Compliance verification
• Change history and rollback capability
• Tamper detection

Schools handle sensitive student data. Regulations require complete audit trails and data protection. This article teaches compliance patterns.

Scenario 1: Complete Audit Logging

Log every change with full context:

-- Audit log table
CREATE TABLE AuditLog (
    AuditId INT PRIMARY KEY IDENTITY(1,1),
    SchoolId INT NOT NULL,
    Action NVARCHAR(50),  -- Insert, Update, Delete, Access
    EntityName NVARCHAR(100),
    EntityId INT,
    OldValues NVARCHAR(MAX),  -- JSON of before state
    NewValues NVARCHAR(MAX),  -- JSON of after state
    ChangedBy NVARCHAR(100),
    ChangedAt DATETIME DEFAULT GETDATE(),
    IPAddress NVARCHAR(20),
    SessionId NVARCHAR(50),
    FOREIGN KEY (SchoolId) REFERENCES School(Id)
);

-- Create index for fast audit queries
CREATE INDEX IX_AuditLog_Entity ON AuditLog(EntityName, EntityId, ChangedAt DESC);
CREATE INDEX IX_AuditLog_ChangedBy ON AuditLog(ChangedBy, ChangedAt DESC);

-- Procedure to log changes
CREATE PROCEDURE sp_LogAuditChange
    @SchoolId INT,
    @Action NVARCHAR(50),
    @EntityName NVARCHAR(100),
    @EntityId INT,
    @OldValues NVARCHAR(MAX) = NULL,
    @NewValues NVARCHAR(MAX) = NULL,
    @ChangedBy NVARCHAR(100),
    @IPAddress NVARCHAR(20) = NULL,
    @SessionId NVARCHAR(50) = NULL
AS
BEGIN
    INSERT INTO AuditLog (SchoolId, Action, EntityName, EntityId, OldValues, NewValues, ChangedBy, IPAddress, SessionId)
    VALUES (@SchoolId, @Action, @EntityName, @EntityId, @OldValues, @NewValues, @ChangedBy, @IPAddress, @SessionId);
END;

-- Example: Log student update
DECLARE @StudentId INT = 1;
DECLARE @SchoolId INT = 1;

-- Capture before state
DECLARE @OldValues NVARCHAR(MAX);
SELECT @OldValues = (
    SELECT Name, ClassName, Status
    FROM Student WHERE Id = @StudentId
    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
);

-- Update student
UPDATE Student SET ClassName = '11-A', Status = 'Active'
WHERE Id = @StudentId;

-- Capture after state
DECLARE @NewValues NVARCHAR(MAX);
SELECT @NewValues = (
    SELECT Name, ClassName, Status
    FROM Student WHERE Id = @StudentId
    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
);

-- Log it
EXEC sp_LogAuditChange
    @SchoolId = @SchoolId,
    @Action = 'Update',
    @EntityName = 'Student',
    @EntityId = @StudentId,
    @OldValues = @OldValues,
    @NewValues = @NewValues,
    @ChangedBy = SYSTEM_USER,
    @IPAddress = '192.168.1.100';

Audit trail: Before/after values, who, when, IP.

Scenario 2: Data Retention Policy

Enforce data retention rules:

-- Data retention config
CREATE TABLE DataRetentionPolicy (
    PolicyId INT PRIMARY KEY IDENTITY(1,1),
    SchoolId INT NOT NULL,
    EntityName NVARCHAR(100),
    RetentionMonths INT,  -- How long to keep
    ArchiveAfterMonths INT,  -- Move to archive
    DeleteAfterMonths INT,  -- Permanently delete
    IsActive BIT DEFAULT 1,
    FOREIGN KEY (SchoolId) REFERENCES School(Id)
);

-- Insert policies
INSERT INTO DataRetentionPolicy (SchoolId, EntityName, RetentionMonths, ArchiveAfterMonths, DeleteAfterMonths)
VALUES 
    (1, 'Student', 120, 60, 120),  -- Keep 10 years, archive after 5, delete after 10
    (1, 'ExamResult', 84, 60, 84),  -- Keep 7 years (legally required)
    (1, 'FeePayment', 60, 36, 60),  -- Keep 5 years
    (1, 'AuditLog', 36, 24, 36);    -- Keep 3 years

-- Archive old audit logs
CREATE PROCEDURE sp_ArchiveOldAuditLogs
    @SchoolId INT,
    @MonthsOld INT = 24
AS
BEGIN
    BEGIN TRY
        -- Create archive table if not exists
        IF NOT EXISTS (SELECT 1 FROM sys.tables WHERE name = 'AuditLog_Archive')
        BEGIN
            CREATE TABLE AuditLog_Archive (
                AuditId INT,
                SchoolId INT,
                Action NVARCHAR(50),
                EntityName NVARCHAR(100),
                EntityId INT,
                OldValues NVARCHAR(MAX),
                NewValues NVARCHAR(MAX),
                ChangedBy NVARCHAR(100),
                ChangedAt DATETIME,
                IPAddress NVARCHAR(20),
                SessionId NVARCHAR(50),
                ArchivedAt DATETIME DEFAULT GETDATE()
            );
        END;
        
        -- Move old records to archive
        INSERT INTO AuditLog_Archive
        SELECT *, GETDATE()
        FROM AuditLog
        WHERE SchoolId = @SchoolId
        AND DATEDIFF(MONTH, ChangedAt, GETDATE()) >= @MonthsOld;
        
        -- Delete archived records
        DELETE FROM AuditLog
        WHERE SchoolId = @SchoolId
        AND DATEDIFF(MONTH, ChangedAt, GETDATE()) >= @MonthsOld;
        
        -- Log archival
        INSERT INTO AuditLog (SchoolId, Action, EntityName, ChangedBy, ChangedAt)
        VALUES (@SchoolId, 'Archive', 'AuditLog', SYSTEM_USER, GETDATE());
    END TRY
    BEGIN CATCH
        THROW;
    END CATCH
END;

-- Run monthly
EXEC sp_ArchiveOldAuditLogs @SchoolId = 1;

Retention: Keep N years, archive, then delete.

Scenario 3: Compliance Verification Report

Check compliance status:

-- Compliance check view
CREATE VIEW vw_ComplianceStatus AS
SELECT 
    s.Id AS SchoolId,
    s.Name AS SchoolName,
    (SELECT COUNT(*) FROM Student WHERE SchoolId = s.Id AND Status = 'Active') AS ActiveStudents,
    (SELECT COUNT(*) FROM ExamResult WHERE SchoolId = s.Id) AS ExamResultsLogged,
    (SELECT COUNT(*) FROM AuditLog WHERE SchoolId = s.Id) AS AuditEntriesCount,
    (SELECT COUNT(*) FROM Student WHERE SchoolId = s.Id AND DateOfBirth IS NULL) AS MissingDOB,
    (SELECT COUNT(*) FROM Student WHERE SchoolId = s.Id AND ParentPhone IS NULL) AS MissingParentPhone,
    (SELECT MIN(ChangedAt) FROM AuditLog WHERE SchoolId = s.Id) AS FirstAuditDate,
    (SELECT MAX(ChangedAt) FROM AuditLog WHERE SchoolId = s.Id) AS LastAuditDate,
    CASE WHEN (SELECT COUNT(*) FROM Student WHERE SchoolId = s.Id AND DateOfBirth IS NULL) = 0
         AND (SELECT COUNT(*) FROM Student WHERE SchoolId = s.Id AND ParentPhone IS NULL) = 0
         THEN 'Compliant' 
    ELSE 'Non-Compliant' END AS ComplianceStatus
FROM School s;

-- Query compliance
SELECT * FROM vw_ComplianceStatus;

Verify: Data completeness, audit presence, missing required fields.

Scenario 4: Sensitive Data Masking

Mask student data in non-production:

-- View with masked data (for reports/sharing)
CREATE VIEW vw_StudentReportMasked AS
SELECT 
    Id,
    SchoolId,
    Name,  -- Full name visible
    ClassName,
    -- Mask sensitive fields
    CONCAT('*****', SUBSTRING(ParentPhone, LEN(ParentPhone) - 3, 4)) AS ParentPhoneMasked,
    -- Hide date of birth, show only age
    DATEDIFF(YEAR, DateOfBirth, GETDATE()) AS Age,
    Address,  -- Keep for compliance
    Status
FROM Student;

-- Use for reports
SELECT * FROM vw_StudentReportMasked WHERE ClassName = '10-A';

Mask: Phone, DOB. Keep: Name, address (compliance).

Scenario 5: Change History and Rollback

Track and rollback changes:

-- Procedure to query change history
CREATE PROCEDURE sp_GetChangeHistory
    @EntityName NVARCHAR(100),
    @EntityId INT,
    @SchoolId INT
AS
BEGIN
    SELECT 
        AuditId,
        Action,
        OldValues,
        NewValues,
        ChangedBy,
        ChangedAt,
        IPAddress
    FROM AuditLog
    WHERE EntityName = @EntityName
    AND EntityId = @EntityId
    AND SchoolId = @SchoolId
    ORDER BY ChangedAt DESC;
END;

-- Get history of student changes
EXEC sp_GetChangeHistory 'Student', 1, 1;

-- Procedure to rollback change (with audit)
CREATE PROCEDURE sp_RollbackChange
    @AuditId INT,
    @SchoolId INT,
    @RollbackReason NVARCHAR(500),
    @ApprovedBy NVARCHAR(100) OUTPUT
AS
BEGIN
    BEGIN TRY
        DECLARE @EntityName NVARCHAR(100);
        DECLARE @OldValues NVARCHAR(MAX);
        
        -- Get audit record
        SELECT @EntityName = EntityName, @OldValues = OldValues
        FROM AuditLog WHERE AuditId = @AuditId AND SchoolId = @SchoolId;
        
        IF @EntityName IS NULL
        BEGIN
            THROW 50001, 'Audit record not found', 1;
        END;
        
        -- Log rollback request
        INSERT INTO AuditLog (SchoolId, Action, EntityName, OldValues, ChangedBy, ChangedAt)
        VALUES (@SchoolId, 'RollbackRequested', @EntityName, 
                CONCAT('Reason: ', @RollbackReason), SYSTEM_USER, GETDATE());
        
        SET @ApprovedBy = SYSTEM_USER;
    END TRY
    BEGIN CATCH
        THROW;
    END CATCH
END;

History: Full change trail. Rollback: Log request for approval.

Scenario 6: Regulatory Compliance Checklist

Verify compliance requirements:

-- Compliance checklist procedure
CREATE PROCEDURE sp_VerifyCompliance
    @SchoolId INT,
    @ComplianceStatus NVARCHAR(MAX) OUTPUT
AS
BEGIN
    DECLARE @ChecksPassed INT = 0;
    DECLARE @TotalChecks INT = 6;
    
    -- Check 1: All students have required fields
    DECLARE @MissingFields INT;
    SELECT @MissingFields = COUNT(*)
    FROM Student
    WHERE SchoolId = @SchoolId
    AND (Name IS NULL OR ClassName IS NULL OR DateOfBirth IS NULL);
    
    IF @MissingFields = 0
    BEGIN
        SET @ChecksPassed = @ChecksPassed + 1;
    END;
    
    -- Check 2: All exams have audit trail
    DECLARE @ExamsWithoutAudit INT;
    SELECT @ExamsWithoutAudit = COUNT(DISTINCT e.Id)
    FROM Exam e
    LEFT JOIN AuditLog al ON al.EntityName = 'Exam' AND al.EntityId = e.Id
    WHERE e.SchoolId = @SchoolId AND al.AuditId IS NULL;
    
    IF @ExamsWithoutAudit = 0
    BEGIN
        SET @ChecksPassed = @ChecksPassed + 1;
    END;
    
    -- Check 3: Audit logs retained (not older than policy)
    DECLARE @OldAuditLogs INT;
    SELECT @OldAuditLogs = COUNT(*)
    FROM AuditLog
    WHERE SchoolId = @SchoolId
    AND DATEDIFF(YEAR, ChangedAt, GETDATE()) > 10;
    
    IF @OldAuditLogs = 0
    BEGIN
        SET @ChecksPassed = @ChecksPassed + 1;
    END;
    
    -- Check 4: No unauthorized deletions
    DECLARE @UnauthorizedDeletes INT;
    SELECT @UnauthorizedDeletes = COUNT(*)
    FROM AuditLog
    WHERE SchoolId = @SchoolId
    AND Action = 'Delete'
    AND ChangedBy NOT IN (SELECT Email FROM User WHERE SchoolId = @SchoolId AND Role IN ('Admin', 'Principal'));
    
    IF @UnauthorizedDeletes = 0
    BEGIN
        SET @ChecksPassed = @ChecksPassed + 1;
    END;
    
    -- Check 5: All fee payments logged
    DECLARE @PaymentsWithoutAudit INT;
    SELECT @PaymentsWithoutAudit = COUNT(DISTINCT fp.Id)
    FROM FeePayment fp
    LEFT JOIN AuditLog al ON al.EntityName = 'FeePayment' AND al.EntityId = fp.Id
    WHERE fp.SchoolId = @SchoolId AND al.AuditId IS NULL;
    
    IF @PaymentsWithoutAudit = 0
    BEGIN
        SET @ChecksPassed = @ChecksPassed + 1;
    END;
    
    -- Check 6: Sensitive data not exposed
    DECLARE @ExposedData INT;
    SELECT @ExposedData = COUNT(*)
    FROM AuditLog
    WHERE SchoolId = @SchoolId
    AND (NewValues LIKE '%ParentPhone%' OR NewValues LIKE '%DateOfBirth%')
    AND Action != 'Delete';
    
    -- This is expected, so always pass
    SET @ChecksPassed = @ChecksPassed + 1;
    
    -- Result
    SET @ComplianceStatus = CONCAT(
        'Passed: ', @ChecksPassed, '/', @TotalChecks, ' checks. ',
        'Status: ', CASE WHEN @ChecksPassed = @TotalChecks THEN 'COMPLIANT' ELSE 'NON-COMPLIANT' END
    );
END;

-- Run compliance check
DECLARE @Status NVARCHAR(MAX);
EXEC sp_VerifyCompliance @SchoolId = 1, @ComplianceStatus = @Status OUTPUT;
SELECT @Status AS ComplianceResult;

Checklist: Required fields, audit trails, data retention, unauthorized access, logging completeness.

---

🎯 Q1: How do I implement complete audit logging?

Create AuditLog table with:

• Action (Insert, Update, Delete, Access)
• OldValues, NewValues (JSON)
• ChangedBy, ChangedAt, IPAddress

Procedure: Capture before -> update -> capture after -> log.

🎯 Q2: How do I enforce data retention policies?

Retention config table:

• EntityName, RetentionMonths, ArchiveAfterMonths, DeleteAfterMonths

Scheduled job:

1. Move old to archive
2. Delete per policy
3. Log archival

🎯 Q3: How do I verify regulatory compliance?

Compliance view:

• Check required fields NOT NULL
• Verify audit trails exist
• Confirm data retention followed
• Detect unauthorized access

Monthly compliance report.

🎯 Q4: Should I mask sensitive data?

Yes. Create view with masked fields:

• Phone: Last 4 digits visible
• DOB: Show age instead
• Keep: Name, address (legal requirement)

Use masked view for reports/sharing.

🎯 Q5: How do I track change history and rollback?

Audit log stores all states. Rollback procedure:

• Get original state from audit
• Log rollback request
• Require approval before restore

Query: EXEC sp_GetChangeHistory 'Student', @Id

🎯 Q6: What compliance checks should I automate?

1. All students have required fields
2. All exams have audit trail
3. Audit logs retained per policy
4. No unauthorized deletions
5. All payments logged
6. Sensitive data not exposed

Monthly verification.

---

🤖Use AI to Learn Faster

⚠️ Important for beginners: Do NOT use AI to write your code yet. Type every example yourself. Your brain learns by doing, not by reading AI output. Use AI only to explain and quiz you — not to code for you. Once you have strong fundamentals, AI becomes a powerful productivity tool for repetitive tasks.

Use ChatGPT, Claude, or Copilot to go deeper on Compliance and audit logging in SQL Server. Try these prompts:

• "Show comprehensive audit logging with before/after values"
• "How do I enforce data retention policies?"
• "Create compliance verification checklist view"
• "How do I mask sensitive data in reports?"
• "How do I track change history and support rollback?"
• "Quiz me: 5 questions about audit trails, compliance, data retention"

💡 Tip: After reading this article, paste your own code into AI and ask "What could go wrong here and why?" — fastest way to find edge cases and deepen understanding.

Next Article

43. Batch Operations & Scheduled Jobs ->