7ges ERP Client/Server

Pattern Code:

CREATE OR REPLACE FUNCTION llx_{tabla}_before_insert() 
RETURNS trigger 
LANGUAGE plpgsql
AS $$
BEGIN
    -- 1. Required fields validation
    IF NEW.required_field IS NULL OR trim(NEW.required_field) = '' THEN
        RAISE EXCEPTION 'ErrorFieldRequired: required_field';
    END IF;
    
    -- 2. Automatic data cleaning
    NEW.text_field = trim(NEW.text_field);
    
    -- 3. Email validation with regex
    IF NEW.email IS NOT NULL AND NEW.email != '' THEN
        NEW.email = trim(lower(NEW.email));
        IF NOT (NEW.email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$') THEN
            RAISE EXCEPTION 'ErrorBadEMail: %', NEW.email;
        END IF;
    END IF;
    
    -- 4. Phone formatting
    IF NEW.phone IS NOT NULL THEN
        NEW.phone = regexp_replace(NEW.phone, '[^0-9+\-\.\s()]', '', 'g');
        NEW.phone = trim(NEW.phone);
    END IF;
    
    -- 5. Default values
    NEW.entity := COALESCE(NEW.entity, 1);
    NEW.status := COALESCE(NEW.status, 1);
    NEW.datec := COALESCE(NEW.datec, NOW());
    
    -- 6. Numeric values validation
    IF NEW.numeric_field IS NOT NULL AND NEW.numeric_field < 0 THEN
        RAISE EXCEPTION 'Field cannot be negative';
    END IF;
    
    RETURN NEW;
END;
$$;

CREATE OR REPLACE FUNCTION llx_documento_validate_status_change()
RETURNS trigger AS $$
BEGIN
    -- Only process if state changed
    IF OLD.fk_statut != NEW.fk_statut THEN
        
        -- Validate allowed transitions
        CASE OLD.fk_statut
            WHEN 0 THEN -- Draft
                IF NEW.fk_statut NOT IN (1, -1) THEN
                    RAISE EXCEPTION 'Invalid transition from draft to state %', NEW.fk_statut;
                END IF;
                
            WHEN 1 THEN -- Validated
                IF NEW.fk_statut NOT IN (2, 3, -1) THEN
                    RAISE EXCEPTION 'Invalid transition from validated to state %', NEW.fk_statut;
                END IF;
                
            WHEN 2 THEN -- In process
                IF NEW.fk_statut NOT IN (3, -1) THEN
                    RAISE EXCEPTION 'Invalid transition from in process to state %', NEW.fk_statut;
                END IF;
                
            WHEN 3 THEN -- Closed
                RAISE EXCEPTION 'Cannot modify a closed document';
                
            WHEN -1 THEN -- Cancelled
                IF NEW.fk_statut != 0 THEN
                    RAISE EXCEPTION 'Can only reopen a cancelled document';
                END IF;
        END CASE;
        
        -- Specific actions per transition
        IF NEW.fk_statut = 1 AND OLD.fk_statut = 0 THEN
            -- When validating: set date and generate reference
            NEW.date_valid := COALESCE(NEW.date_valid, NOW());
            IF NEW.ref LIKE '(PROV%' THEN
                NEW.ref := llx_documento_get_next_ref(NEW.entity);
            END IF;
        END IF;
        
        IF NEW.fk_statut = -1 THEN
            -- When cancelling: clear process dates
            NEW.date_valid := NULL;
            NEW.date_close := NULL;
        END IF;
        
        IF NEW.fk_statut = 3 THEN
            -- When closing: set close date
            NEW.date_close := COALESCE(NEW.date_close, NOW());
        END IF;
    END IF;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Trigger for automatic audit
CREATE OR REPLACE FUNCTION llx_audit_changes()
RETURNS trigger AS $$
DECLARE
    v_changes jsonb := '{}';
    v_campo text;
    v_old_value text;
    v_new_value text;
BEGIN
    -- For UPDATE: record changes
    IF TG_OP = 'UPDATE' THEN
        -- Update timestamp automatically
        NEW.tms := NOW();
        
        -- Detect significant changes for history
        FOR v_campo IN SELECT column_name 
                       FROM information_schema.columns 
                       WHERE table_name = TG_TABLE_NAME 
                       AND column_name NOT IN ('tms', 'rowid', 'datec')
        LOOP
            EXECUTE format('SELECT ($1).%I::text, ($2).%I::text', v_campo, v_campo)
            INTO v_old_value, v_new_value
            USING OLD, NEW;
            
            IF v_old_value IS DISTINCT FROM v_new_value THEN
                v_changes := v_changes || jsonb_build_object(
                    v_campo, jsonb_build_object(
                        'old', v_old_value,
                        'new', v_new_value
                    )
                );
            END IF;
        END LOOP;
        
        -- Record in audit table if there are significant changes
        IF v_changes != '{}' THEN
            INSERT INTO llx_audit_log (
                table_name, record_id, operation, changes, 
                user_id, change_date
            ) VALUES (
                TG_TABLE_NAME, NEW.rowid, 'UPDATE', v_changes,
                COALESCE(NEW.fk_user_modif, NEW.fk_user_author, 1), NOW()
            );
        END IF;
    END IF;
    
    -- For INSERT: record creation
    IF TG_OP = 'INSERT' THEN
        NEW.datec := COALESCE(NEW.datec, NOW());
        NEW.tms := COALESCE(NEW.tms, NOW());
        
        INSERT INTO llx_audit_log (
            table_name, record_id, operation, 
            user_id, change_date
        ) VALUES (
            TG_TABLE_NAME, NEW.rowid, 'INSERT',
            COALESCE(NEW.fk_user_author, 1), NOW()
        );
    END IF;
    
    RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql;