Triggers - Database Event Automation Support

[mathiasrw]

Priority: 3-4 (Medium)
Impact: SQL-99 Compliance
Test Files: test/test1119.js
Test Count: 1 test

Problem Description

The test file contains skipped tests for SQL trigger functionality including BEFORE, AFTER, and INSTEAD OF triggers for INSERT, UPDATE, and DELETE operations. Triggers are essential SQL-99 features for database automation and business logic enforcement.

Specific Test Cases

test1119.js - Trigger Callback Parameters (1 test suite)

• Lines 6-156: Comprehensive trigger testing framework
• Tests all trigger types: BEFORE/AFTER/INSTEAD OF
• Tests all DML operations: INSERT/UPDATE/DELETE
• Tests callback parameter passing and data reception

Expected Behavior

AlaSQL should support SQL-99 standard trigger functionality:

1. BEFORE Triggers: Execute before DML operations
2. AFTER Triggers: Execute after DML operations
3. INSTEAD OF Triggers: Replace DML operations
4. Trigger Events: INSERT, UPDATE, DELETE operations
5. Callback Parameters: Pass row data to trigger functions
6. Business Logic: Data validation and automation

Current Status

• Test Status: Entire test suite skipped (describe.skip)
• Error: Unknown (tests not executed)
• Root Cause: Trigger system not implemented in database engine

Implementation Requirements

1. Parser Support

Add trigger syntax to src/alasqlparser.jison:

-- Basic trigger creation
CREATE TRIGGER trigger_name 
BEFORE INSERT ON table_name 
CALL trigger_function()

-- AFTER trigger
CREATE TRIGGER trigger_name 
AFTER UPDATE ON table_name 
CALL trigger_function()

-- INSTEAD OF trigger
CREATE TRIGGER trigger_name 
INSTEAD OF DELETE ON table_name 
CALL trigger_function()

-- Drop trigger
DROP TRIGGER trigger_name

2. Trigger Engine

• Trigger Registration: Store trigger definitions in table metadata
• Event Detection: Intercept DML operations (INSERT/UPDATE/DELETE)
• Execution Control: BEFORE/AFTER/INSTEAD OF timing
• Parameter Passing: Pass row data to trigger functions
• Error Handling: Handle trigger failures appropriately

3. Trigger Types Support

• BEFORE INSERT: Validate/modify data before insertion
• AFTER INSERT: Post-insertion processing
• BEFORE UPDATE: Validate/modify data before update
• AFTER UPDATE: Post-update processing
• BEFORE DELETE: Validate before deletion
• AFTER DELETE: Post-deletion cleanup
• INSTEAD OF: Custom DML operation handling

4. Data Parameter Support

• INSERT Triggers: Pass inserted row data
• UPDATE Triggers: Pass old and new row data
• DELETE Triggers: Pass deleted row data
• Function Integration: Integrate with AlaSQL function system

5. Advanced Features

• Multiple Triggers: Support multiple triggers per table/event
• Trigger Ordering: Control execution order for multiple triggers
• Nested Triggers: Handle triggers that fire other triggers
• Performance: Efficient trigger execution without major overhead

SQL-99 Features Involved

• CREATE TRIGGER statement
• BEFORE/AFTER/INSTEAD OF trigger timing
• INSERT/UPDATE/DELETE trigger events
• Trigger function calling mechanism
• Row-level trigger execution
• Database automation and business logic

Dependencies

• AlaSQL parser (src/alasqlparser.jison)
• Database engine DML operations
• Function system (alasql.fn)
• Table metadata management
• Transaction system (for rollback support)

Acceptance Criteria

Basic Trigger Functionality (test1119.js)

• All 5 trigger tests enabled and passing
• BEFORE INSERT trigger with row data
• AFTER INSERT trigger with row data
• BEFORE UPDATE trigger with old/new row data
• BEFORE DELETE trigger with row data
• INSTEAD OF INSERT trigger with row data
• Proper callback parameter passing
• Trigger function integration

Implementation Strategy

Phase 1: Basic Trigger Framework

1. Parser Support for CREATE/DROP TRIGGER
2. Trigger Registration in table metadata
3. Basic Event Detection for DML operations
4. Simple Trigger Execution with function calling

Phase 2: Trigger Types and Parameters

1. BEFORE/AFTER Timing implementation
2. INSTEAD OF Trigger support
3. Parameter Passing for different DML operations
4. Error Handling and rollback support

Phase 3: Advanced Features

1. Multiple Triggers per table/event
2. Trigger Ordering and execution control
3. Performance Optimization for trigger overhead
4. Edge Cases and error scenarios

Test Implementation Examples

-- BEFORE INSERT trigger
CREATE TRIGGER validate_email
BEFORE INSERT ON users
CALL validateEmailFunction()

-- AFTER UPDATE trigger
CREATE TRIGGER audit_changes
AFTER UPDATE ON products
CALL auditUpdateFunction()

-- INSTEAD OF trigger
CREATE TRIGGER custom_insert
INSTEAD OF INSERT ON orders
CALL customOrderInsert()

-- Trigger function example
alasql.fn.validateEmailFunction = function(row) {
    if (!row.email.includes('@')) {
        throw new Error('Invalid email address');
    }
};

Performance Considerations

• Minimal Overhead: Triggers should not significantly impact DML performance
• Efficient Execution: Optimize trigger function calling
• Memory Usage: Proper cleanup of trigger contexts
• Batch Operations: Handle bulk DML operations efficiently

Edge Cases to Handle

• Trigger Exceptions: Handle errors in trigger functions
• Recursive Triggers: Prevent infinite trigger loops
• Transaction Conflicts: Handle trigger failures with rollbacks
• Multiple Triggers: Order execution correctly
• Schema Changes: Handle triggers during table modifications

Integration Points

• Function System: Integrate with alasql.fn for trigger functions
• Transaction System: Support trigger rollback on failures
• Error Handling: Proper error propagation and handling
• Table Metadata: Store trigger definitions with table schemas

Security Considerations

• Function Validation: Validate trigger function existence
• Permission System: Control trigger creation/execution
• Resource Limits: Prevent trigger resource exhaustion
• Code Injection: Secure trigger function execution

Notes

Triggers are essential for database automation and business logic enforcement. The implementation should:

• Follow SQL-99 standards for trigger syntax and behavior
• Provide good performance for typical trigger usage
• Handle edge cases and errors gracefully
• Integrate well with existing AlaSQL features